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")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome this is perfect, thanks!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!