I am going to have text data in column A, a date in column B and a count in column C that represent 'before' values. In columns E, F anf G, I am going to have 'after' values. There might be more after rows (won't be less), and the counts might not be the same as the before values.
Ideally, I'd like to code a function that I can replicate down column H. I want to match columns E and F against columns A and B. If I get a match, then I want to compare the values in C and G. If they don't match, then I want to display something in column H (e.g. "!="). If I cannot match E and F against A and B, then I want to display some other character in column H (e.g. "**").
Use the IF function.
=IF(((A2=E2) * (B2=F2) * (C2=G2)),"**","NE")
Here's how it works.
If A2 and E2 are the same, the result of (A2=E2) is 1; if not, the result is 0.
So if all three tests are true, the result is 1, which is true and the IF function returns **.
If any of the tests are false, the result is 0 and the IF function returns NE (Not Equal).
This is NOT intended to be directly usable but may help you with "logic"...
IF ((A & B) = (D & F), IF C = G (H = "match", H = "!*"), H = "**")
Basically, concatenate A and B as well as D and F, watching your types (and ensuring you are not mixing date formats) and then compare them. Would be best to TRIM as needed as well to ensure some stray blanks don't mess you up. Then something like above general format should work I think. Did not have time to test it for you.
That would work if there are always the same number of rows. In fact, if that was the case, I'd only need to compare C and G, as A and E and B and F would always be equal. But, an extra day could be placed in their somewhere, where now the data in row 28 of columns A and B match row 30 of E and F, or row 29 of E and F have no matching row in A and B. I think VLOOKUP is going to somehow be involved, maybe.
0
Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)
VLOOKUP doesn't require data to be sorted when the 4th argument is FALSE or zero for an exact match.....but I think you can do this without VLOOKUP anyway.
You really have 3 possible outcomes, either you have "No initial Match", i.e. E2 and F2 don't match anywhere in A and B.......or there is a match there but no match in the same row with C and G2 ("Initial but no secondary").......or there is a match with all 3 columns ("Both") - you could use COUNTIFS to get those results, e.g.
=IF(COUNTIFS(A:A,E2,B:B,F2,C:C,G2),"Both",IF(COUNTIFS(A:A,E2,B:B,F2),"Initial but no secondary","No initial Match"))
Of course you can change those text values to be whatever you want
Hey Barry,
True but two points...
1) His problem requires an exact match by his definition.
2) I don't think there'll be unreliable results without a sorted table "data table"; does not matter about the search term tho.
Actually, it doesn't. If the values in E,F and G are in A, B and C, but in a different row, does not return the proper results. I want the indication to be based on what is in E, F and G, with the flag being based on the relative occurrence (or lack) of matching data in A, B and C for exact match or A and B for a match of data with different counts.
Didn't think it would. :))
Did you sort the after data?
Try that but be sure you maintain data integrity for the respective columns.
Without seeing some of the data we're guessing at things. Perhaps it would help for you to sort both the concatenated before values and the after values? This may keep the comparisons in the same rows considering you are dealing with dates as one key value - this may help if the "missing" data is due to "later" dates.
Data is sorted. Column A is a list of table names, Column B is a list of dates and column C is a count of rows in the tables, so sample data would be:
table1 2015/02/01 100
table1 2015/02/02 110
table1 2015/02/03 110
table1 2015/02/05 110
table2 2015/02/01 201
table2 2015/02/02 203
table2 2015/02/03 198
table2 2015/02/05 200
Columns E, F and G would ideally, but not necessarily match up on table name and date, and have either the same or different counts. There "could" be a row for 2015/02/04 in the second set of data, however, there could be a row for a date, but there should never be a case where columns A:B have a record that isn't in columns E:F. Once there is a count for a table with a specific date, that row will carry forward. Might pick up new ones, but shouldn't drop existing ones.
>if the values in E,F and G are in A, B and C, but in a different row, does not return the proper results
In that case the formula returns "Both", i.e. both matches are valid. I don't understand from your last post what you want the results to be in that case - can you include some sample data (preferably in a workbook) with required results? - thanks
Note: there's nothing in my solution that requires the data to be sorted in any way
No worries - agreed on the sorting thing for "Range Lookup" as "False". (ie: an exact match) :))
Data sample posted as ID: 40619445 and explanation was helpful. I see no reason why a vLookup function would not work as long as concatenation is done right and the concatenated values in before and after data are unique.
I hope a moderator will attend to this soon and re-open your question if you don't think it's been answered, but in the meantime I'm quite happy to continue to help you towards a robust solution.
I'm sure you can use VLOOKUP here, but I'd question whether it's the best approach - given my understanding of the requirements I didn't think so, hence the use of COUNTIFS function, I'll explain why:
If you want to retrieve a value from another column then you probably need either VLOOKUP or INDEX/MATCH......but my understanding is that there is no value to be retrieved, you simply need an indicator of whether there is a match - for a single column that would suggest MATCH function, which can also be stretched to accommodate multiple column matches in a variety of ways.
However, often COUNTIF, for a single match, or COUNTIFS, for multiple matches has two main advantages:
COUNTIF(S) will return a zero rather than an error when there no matches, making it a little easier to handle in those cases, without needing additional functions like ISNUMBER or IFERROR, and bearing in mind here that we know there will be situations where that happens.
COUNTIFS is also more forgiving of mismatched data types, e.g. It can match a text formatted date with a true date, which neither VLOOKUP or MATCH will do. I have no idea whether that's an issue here but I might tend towards COUNTIFS because of that
Interesting concept. You're right, there is really no related data returned so the relative row is not important. Your idea should work great, allowing any "output" needed.
Considering that (from the sound of it) there should be no duplicate records, the count could also be a warning trigger if >1.
Ok, must have done something wrong in the original cut and paste because it seems to be working now. Strange. Broke up solution point. Had to give you something for the 'documentation'. :-) Thanks
0
Featured Post
As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clientsâ€™ hosting easier.
=IF(((A2=E2) * (B2=F2) * (C2=G2)),"**","NE")
Here's how it works.
If A2 and E2 are the same, the result of (A2=E2) is 1; if not, the result is 0.
So if all three tests are true, the result is 1, which is true and the IF function returns **.
If any of the tests are false, the result is 0 and the IF function returns NE (Not Equal).