Matching data

[mcarriev]

Hi there,

Number ID From Database1	Number ID From Database2	What Bob Entered in Database1	What Bob Entered in Database2
12345	12345	Owns a Car	Owns a Car
12345	12345	Owns a Boat	Owns a Boat
23456	23456	Owns a Computer	Owns a Computer
23456	23456	Owns a Cup	Owns a Boat

<colgroup><col span="2"><col span="2"></colgroup><tbody>
</tbody>

The above is an example of the data I'm working with, I want to match the action of whatever Bob enters in Database1 with what he puts into Database2. His actions are recorded with a number ID. If Bob implements mismatching data, such as the bold text above, I want that highlighted.

Originally, I use to do a vlookup with this data which worked out great but now I have duplicate number IDs. I hear that index match is a great option, but I'm not really familiar with that function.

What would you guys suggest?

 

[MarcelBeug]

So I understand that if the ID/description combination in DB1 is not in DB2 (or vice versa) it should be highlighted.

Conditional Formatting formula for columns A and C (DB1 not in DB2):

=ISERROR(MATCH($A2&$C2,$B$2:$B$5&$D$2:$D$5,0))

Conditional Formatting formula for columns B and D (DB2 not in DB1):

=ISERROR(MATCH($B2&$D2,$A$2:$A$5&$C$2:$C$5,0))

Hope this is what you're looking for.

 

[mcarriev]

@Marcel,

Will this work if the number IDs are out of order? For example, number ID 23456 can be in cell A50 in a different spreadsheet, and perhaps cell A1000 in a different spreadsheet. But I'll be putting the two number ID data sets side by side, just like in the example above.

I'm going to try this out in a bit, thanks for your help Marcel.

 

[mcarriev]

Quick update:

Hey Marcel, I tried this out and it only worked if I had my two example data sets lined up exactly. If I had the scenario in the above post, this will not work.

Originally, I use to do a vlookup since I did not have duplicate number IDs. But now a number ID can repeat itself several times.

 

[MarcelBeug]

Hm, that's disappointing and I really don't understand it.
The formulas are designed to work if the data is not lined up; with me it works perfectly with the example provided.

The only thing I can imagine: the formulas are actually array formulas, so if you enter the formulas not in the Conditional Formatting environment, they must be confirmed with CTRL-SHIFT-ENTER. In the Conditional Formatting environment, there is no difference between a regular or an array formula.

 

[mcarriev]

Hi Marcel,

Sorry, you're definitely right. The formula you gave me is correct and my implementation was wrong.

This formula definitely helped me out Marcel, thank you very much!

 

[MarcelBeug]

You're welcome.

 

[mcarriev]

Hi Marcel or anybody willing to help,

So the two formulas worked with the sample data I gave above, but when I tested it with the school data I might be working with... it didn't really workout exactly as I planned.

So the formula is telling me that I have mismatching data in columns C & D, which is great, but when I try to find the associated information on columns A & B I can't find them.

Originally, I use to do vlookup and it would rearrange my data nicely for some reason and I would see which placements did not (or do) have mismatching data. In my new data that I'm working on, I might have placements that may be duplicated two or three times and vlookup doesn't work well with duplicates.

Any suggestions anybody? My goal is to find mismatching or matching data, or N/A if the data isn't there period, in columns C & D. Then have these columns be lined up with their associated placements in columns A & B.

Sample Spreadsheet below as a WeTransfer link with 3 tabs included for reference:
1) New Formula tab is the tab for testing out new formulas
2) Vlookup tab is the formula I use to use
3) Vlookup Data is the dataset that Vlookup is referencing

http://we.tl/YbKSvqVyVF

Thank you for reading, and thanks in advance for your help.

 

[MarcelBeug]

In the sample spreadsheet I can hardly recognise anything from the case outlined above.
I don't see numbers, I don't see duplicates.
Can you clarify in terminology of the sample spreadsheet what you are trying to achieve?

 

[MarcelBeug]

The following solution will do the following:
if combination A2 (Rap Placemements concatenated with Creatives from Database1) and C2 (Rappers from Database1) is found in combination B & D (same info from DB2), it will return C2
else:
if A2 can be found in C it will return the first rapper from DB where B can be found in A and the combination B&D can not be found in A&C, if such is rapper is not found or if A2 can’t be found in C, it will return “No Match”.
In F2 similar but the other way around.

Example:
Combinations in DB1 with results in column E
12345A A
12345B C
12346A A
12346B No Match
12347A No Match

Combinations in DB2 with results in column F
12345A A
12345C B
12345D B
12346A A
12348A No Match

Assuming you are USING Excel 2007 or higher, data from DB1 is in row 2-13 and data from DB2 is in row 2-7, enter in E2 the following array formula, confirm with CTRL-SHIFT-ENTER, not just ENTER, and copy down:

=IF(ISNUMBER(MATCH(A2&C2,$B$2:$B$7&$D$2:$D$7,0)),C2,IFERROR(INDEX($D$2:$D$7,SMALL(IF(ISERROR(MATCH($B$2:$B$7&$D$2:$D$7,$A$2:$A$13&$C$2:$C$13,0))*($B$2:$B$7=$A2),ROW($B$2:$B$7)-ROW($B$2)+1),1)),"No Match"))

Enter in F2 the following array formula, confirm with CTRL-SHIFT-ENTER, not just ENTER, and copy down:

=IF(ISNUMBER(MATCH(B2&D2,$A$2:$A$13&$C$2:$C$13,0)),D2,IFERROR(INDEX($C$2:$C$13,SMALL(IF(ISERROR(MATCH($A$2:$A$13&$C$2:$C$13,$B$2:$B$7&$D$2:$D$7,0))*($A$2:$A$13=$B2),ROW($A$2:$A$13)-ROW($A$2)+1),1)),"No Match"))

Now you can highlight differences by comparing B and E for DB1, and D and F for DB2.

A few other remarks:
=ISERROR(…)=TRUE is the same as =ISERROR(…) (so the =TRUE is superfluous)
=AND(B2=C2) is the same as =B2=C2 (no AND required for 1 logical test)
Mind your fixed/mixed/variable cell references., e.g. D2:D100 will become D3:D101 when copied down. When referring to a range of multiple cells, you would typically want to have it fixed like $D$2:$D$100.