Comparing multiple data sets

murphyspccms

Hi all,

I'm attempting to compare data sets between 3 different sheets. Basically I want to compare our internal data to the clients data. The common denominator between all 3 sheets is a claim number, the dates in the columns though will likely be different, and my goal is to identify which claim numbers have dates that don't match up to ours.

I'm imagining that the comparison sheet has all the claim numbers in the Primary column, then a formula is written for Column 1 to reference one sheet, another for Column 2, and so on.

I've tried using reports, but the problem is I've got over 15,00 claim numbers to compare, so that has been time consuming.

This is an open question since I'm trying to figure out which route to take to solve my problem.

I'm wondering if a =INDEX(Collect( is the correct approach, but I've never actually used those formulas. Any help/suggestions are appreciated!

Paul Newcome

Instead of creating a third sheet, you are going to need to put a formula on either the internal sheet or the client sheet. You would use an INDEX/MATCH to pull in the date based on the claim number then nest it in an IF to say if the INDEX/MATCH does not equal the date in the sheet containing the formula, then output "NO MATCH" or whatever you want for the output.

=IF(INDEX({Client Sheet Date Column}, MATCH([Claim Number]@row, {Client Sheet Claim Number Column}, 0)) <> [Date Column]@row, "NO MATCH")

Paul Newcome

In you post you indicated 15,00. Is the comma in the wrong place, or did you forget a zero. I don't mean to be picky, but scale could become a factor in this one.

murphyspccms

Typo- 15,000 claims in total, good catch

murphyspccms

@Paul Newcome realistically about 5,000 of them will have the discrepancies, for what it's worth

Paul Newcome

Instead of creating a third sheet, you are going to need to put a formula on either the internal sheet or the client sheet. You would use an INDEX/MATCH to pull in the date based on the claim number then nest it in an IF to say if the INDEX/MATCH does not equal the date in the sheet containing the formula, then output "NO MATCH" or whatever you want for the output.

=IF(INDEX({Client Sheet Date Column}, MATCH([Claim Number]@row, {Client Sheet Claim Number Column}, 0)) <> [Date Column]@row, "NO MATCH")

murphyspccms

@Paul Newcome this is perfect, thanks!

Paul Newcome

Happy to help. 👍️