Question description:
This user has given permission to use the problem statement for this
blog.
Help please.
I have two sheets, one contains a column that has a list of common errors, the other has a column from a log file that has all the errors.
I need something that will check through all the errors and return a count of lines that do NOT contain any text from the common errors. In other words lines that have unknown errors. Additionally it would be useful to highlight the lines in all the errors to show unrecognized errors in red.
Any ideas?
Solved by T. L. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
08/10/2018 - 02:43
Welcome to Excel chat, I see that your question is about to Match or count the two columns which contain unrecognized error from a list.
Excelchat Expert
08/10/2018 - 02:43
How are you doing today?
User
08/10/2018 - 02:43
ok
Excelchat Expert
08/10/2018 - 02:43
I can help you with that problem, I'll be able to provide you a solution and explanation.
Excelchat Expert
08/10/2018 - 02:44
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
User
08/10/2018 - 02:45
Great.
Excelchat Expert
08/10/2018 - 02:45
Can you please attach the Sheet here so that i can better understand your problem and give you a perfect solution.
Excelchat Expert
08/10/2018 - 02:47
So these are Common Error or Log file errors ?
Excelchat Expert
08/10/2018 - 02:49
Sheet 1 Contains Common Error?
User
08/10/2018 - 02:49
Yes. In sheet 2 I need to find errors that are not in sheet 1
Excelchat Expert
08/10/2018 - 02:50
So you need count from sheet 1
User
08/10/2018 - 02:50
or has the text present in any of the errors
Excelchat Expert
08/10/2018 - 02:50
that how many time error has occured ?
Excelchat Expert
08/10/2018 - 02:51
You want to match the whole text of a part of text from the sheet 2?
User
08/10/2018 - 02:53
The idea is that if I was doing this manually I would cross of any error lines that contain the text in any part of sheet1 so in sheet 2 I can see what is left or count what is left.
Excelchat Expert
08/10/2018 - 02:53
Okay.
Excelchat Expert
08/10/2018 - 02:54
So for Example in Sheet 2 for the highlighted cells it says Export Failed.
Excelchat Expert
08/10/2018 - 02:54
So in Sheet 1 it has there as highlighted .
Excelchat Expert
08/10/2018 - 02:54
am i right ?
User
08/10/2018 - 02:56
I think so.E.g. there may be a line in Sheet 2 that says "uwbddb;qwd Operation not yet implemented jhsjhsd8" which i can then discount. There may be another error in sheet 2 that has "8348 njdnjjd CoPilot Error jshdjshdjjs" which is not in the list. that is one I want or to count.
Excelchat Expert
08/10/2018 - 02:59
Okay so you want to highlight in sheet 2 which does not in sheet 1?
User
08/10/2018 - 03:00
Yes that sounds right.
Excelchat Expert
08/10/2018 - 03:00
Okay
Excelchat Expert
08/10/2018 - 03:00
work on that and found you a solution for that
Excelchat Expert
08/10/2018 - 03:00
I’m working on your solution. But don’t worry, I’ll be updating you throughout!
Excelchat Expert
08/10/2018 - 03:00
Please continue to ask me any questions you have while I’m working.
Excelchat Expert
08/10/2018 - 03:06
I’m still working, I should have your solution soon.
User
08/10/2018 - 03:07
I used a COUNTIF to find the numbers of each of these errors which included wild cards to search for the text within the line. But is is important to look for any lines that contain text not in the list. I only gave a small sample to you of the really large log file in Sheet2.
Excelchat Expert
08/10/2018 - 03:07
Okay
Excelchat Expert
08/10/2018 - 03:08
i Give you Count in sheet 1 ?
Excelchat Expert
08/10/2018 - 03:08
will that work
User
08/10/2018 - 03:08
Yes, that will be fine
Excelchat Expert
08/10/2018 - 03:09
For example how many time " Operation not yet implemented" Occurs in sheet 2.
Excelchat Expert
08/10/2018 - 03:10
Do you believe that will Solve your problem?
User
08/10/2018 - 03:12
Actually I already have that in the Total column. I need to know how many lines in sheet 2 that has NOT got any text matching the Error list in sheet1.
Excelchat Expert
08/10/2018 - 03:12
Okay
Excelchat Expert
08/10/2018 - 03:13
o you need count or match in Sheet2
Excelchat Expert
08/10/2018 - 03:13
I have inserted a formula in Sheet 1 for count see if it works otherwise will do something for sheet 2 as well
User
08/10/2018 - 03:14
Want to exclude lines in Sheet2 that have any of the text in the Error column on sheet1 to leave me a count of "Unknown Errors Found".
Excelchat Expert
08/10/2018 - 03:16
if Count is 0 in sheet 1 that says unknown error
Excelchat Expert
08/10/2018 - 03:16
Highlighted in red is an unknown error.
Excelchat Expert
08/10/2018 - 03:17
which is not in sheet 2
Excelchat Expert
08/10/2018 - 03:17
that says same
User
08/10/2018 - 03:17
So lines that do NOT match anything in the Error column should be conted to give me a single figure.
User
08/10/2018 - 03:17
*counted
Excelchat Expert
08/10/2018 - 03:17
Lines of sheet 1?
Excelchat Expert
08/10/2018 - 03:18
That do not match?
User
08/10/2018 - 03:18
like what I have just added.
User
08/10/2018 - 03:18
Yes
Excelchat Expert
08/10/2018 - 03:19
So see in sheet 1
Excelchat Expert
08/10/2018 - 03:19
errors do not match
User
08/10/2018 - 03:19
yes
Excelchat Expert
08/10/2018 - 03:19
or 18 lines you can say that
User
08/10/2018 - 03:19
yes
Excelchat Expert
08/10/2018 - 03:19
will that work for you now.
Excelchat Expert
08/10/2018 - 03:20
Does this solution solve your problem?
User
08/10/2018 - 03:21
yes I think it does.
Excelchat Expert
08/10/2018 - 03:21
You can try this solution to your original data.
Excelchat Expert
08/10/2018 - 03:21
i am here until you get satisfied.
User
08/10/2018 - 03:22
Oh hang on
Excelchat Expert
08/10/2018 - 03:22
i will be very happy to give you explanation as well.
User
08/10/2018 - 03:22
Is that actually searching through Sheet2
Excelchat Expert
08/10/2018 - 03:23
Yes
Excelchat Expert
08/10/2018 - 03:26
Does that worked ?
User
08/10/2018 - 03:28
Mmm... Just trying to understand the logic. So you added the formula in column C to search for the specific error in column B, then counted the zeros in column C.
Excelchat Expert
08/10/2018 - 03:29
Yes
Excelchat Expert
08/10/2018 - 03:29
You got it Right
Excelchat Expert
08/10/2018 - 03:30
I have added * in Count if formula as well to search the text in column B if this text find in any part of column A in Sheet 2.
User
08/10/2018 - 03:31
That seems to count how many times an error message is not found, not sure this is right. Let me think...
Excelchat Expert
08/10/2018 - 03:31
But you need count at the end which is right.
Excelchat Expert
08/10/2018 - 03:32
As per your query.
Excelchat Expert
08/10/2018 - 03:33
18 errors are unrecognized or not in Sheet 2.
Excelchat Expert
08/10/2018 - 03:35
As you trying to match two text which are not exactly same, so this is the best solution for your problem.
User
08/10/2018 - 03:36
let me think of it in another way. Imagine I wanted every line in Sheet 2 highlighted where it could not see any text in the error list and count them.
Excelchat Expert
08/10/2018 - 03:37
That cant be done by single formula as the text are not exactly same but can be done by using multiple formulas
Excelchat Expert
08/10/2018 - 03:40
Now which are found in sheet 1 have two values in sheet2
Excelchat Expert
08/10/2018 - 03:40
and you can highlight them
User
08/10/2018 - 03:40
So it would go is "Operation not yet implemented" in any part of the lines in sheet 2? If yes we don't want it, Then check for each error in turn in the error list. if it matches we don't want it. if after going through each error message in turn, it finds one that it has not seen. Add it to the total.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
