# Comparing multiple data sets

Options
✭✭✭✭

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!

Tags:

• ✭✭✭✭✭✭
Options

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")

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

Typo- 15,000 claims in total, good catch

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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")

• ✭✭✭✭
Options

@Paul Newcome this is perfect, thanks!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!