Solved

Excel Function to Match Data

Posted on 2015-02-18
18
64 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

758 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

19 Experts available now in Live!

Get 1:1 Help Now