Comparing multiple data sets
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!
Best Answer
-
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")
Answers
-
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.
-
Typo- 15,000 claims in total, good catch
-
@Paul Newcome realistically about 5,000 of them will have the discrepancies, for what it's worth
-
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 this is perfect, thanks!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!