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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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")


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!