Solved

Excel Function to Match Data

Posted on 2015-02-18
18
71 Views
Last Modified: 2016-02-11
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. "**").
0
Comment
Question by:dbbishop
  • 7
  • 5
  • 3
  • +1
18 Comments
 
LVL 13

Expert Comment

by:Mark Bullock
ID: 40618153
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).
0
 
LVL 2

Expert Comment

by:DanielT
ID: 40618185
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.
0
 
LVL 15

Author Comment

by:dbbishop
ID: 40618189
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 13

Expert Comment

by:Mark Bullock
ID: 40618207
Like DanielT mentioned, you could concatenate values, perhaps putting those values in two new columns to make the VLOOKUP simpler.
0
 
LVL 2

Expert Comment

by:DanielT
ID: 40618215
Agreed, you'll need to combine both of the "columns of two" (A&B) and (D&F) and then use vlookup for a matching "entry"
0
 
LVL 2

Expert Comment

by:DanielT
ID: 40618223
Remember that the vLookup data must be sorted in ascending order and be sure to use exact match.

Depending on your data however, this may need to be done in VBA with some counters to keep track of position for rows of offset data.
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 40618606
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

regards, barry
0
 
LVL 2

Expert Comment

by:DanielT
ID: 40618735
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.
0
 
LVL 15

Author Comment

by:dbbishop
ID: 40619307
Works great. Thanks.
0
 
LVL 15

Author Comment

by:dbbishop
ID: 40619328
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.
0
 
LVL 2

Expert Comment

by:DanielT
ID: 40619404
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.
0
 
LVL 15

Author Comment

by:dbbishop
ID: 40619445
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.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 40619540
Hello dbbishop,

You say

>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

regards, barry
0
 
LVL 2

Expert Comment

by:DanielT
ID: 40619588
barry

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.
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 500 total points
ID: 40620683
Hello dbbishop,

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

regards, barry
0
 
LVL 2

Expert Comment

by:DanielT
ID: 40621032
Barry

Excellent post and explanation!

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.
0
 
LVL 15

Author Closing Comment

by:dbbishop
ID: 40633457
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now