Comparing Data Across Multiple Columns on the Same Sheet

Nancy Heater

Hello Smartsheet Community!

We have (4) columns of data that we need to compare against one other (2 sets of 2 columns)

One set of (2) columns represents what was ordered, the other set of (2) columns represents what was received.

The ID numbers in these columns could be in either column, based on how the data was entered (its a manual entry process).

We need to look at both sets and compare the data across all (4) columns to see if the data matches, regardless of which column it is in for its group (ordered vs received).

Columns: ID1 ordered, ID2 ordered, ID1 received ID2 received. I have mocked this up in excel to show a representation of what we're looking for.

Any help is greatly appreciated. Thank you!

Melissa Boehl

May I ask why there are 2 columns for ordered and 2 for received? If I can understand the reason for this and exactly what you are trying to accomplish, I may be able to assist.

Nancy Heater

Good morning @Melissa Boehl - there are (2) ID items per row for what is ordered and what has been received. These IDs "should" match all of the time, but due to the manual data entry part of the process we need to confirm that they do match. The ID's are attached to specific items on an order.

Thank you for your help!

Melissa Boehl

@Nancy Heater This is how I would handle that. I dont know if there is a better way but I would just hide the count and match columns and leave the "CHECK" column in view so that I knew which ones needed to be checked. Hope this helps.

Count Distinct Column Formula: =COUNT(DISTINCT([ID1 Ordered]@row:[ID2 Received]@row))

Order 1 Match Column Formula: =IF(ISNUMBER([ID1 Ordered]@row), IF(OR([ID1 Ordered]@row = [ID1 Received]@row, [ID1 Ordered]@row = [ID2 Received]@row), 1, 0), 0)

Order 2 Match Column Formula: = =IF(ISNUMBER([ID2 Ordered]@row), IF(OR([ID2 Ordered]@row = [ID1 Received]@row, [ID2 Ordered]@row = [ID2 Received]@row), 1, 0), 0)

CHECK Column Formula: =IF([Count Distinct]@row = COUNTIF([Order 1 Match]@row:[Order 2 Match]@row, 1), 0, 1)

Nancy Heater

Good morning @Melissa Boehl !!

Thank you for your help - we're much closer to getting this to work now!

We had to remove the "is number" from the match formulas in order to get it to work for our sheet. We think this may because the data we are comparing is pulling in from other sheets.

Unfortunately, we are not able to get the checkbox formula to work for some reason. It just returns #unparseable.

Melissa Boehl

Good Morning!!@Nancy Heater

The isnumber function is there to ensure it only returns a flag if the order column isn't blank. It may work okay without it depending on how your sheet is structured. You could also try not(isblank(column@row)). If you copy/paste the checkbox formula you entered and let me know which parts of the formula reference a different sheet, I could take a look. If I am correct in my understand of your setup, a matrix or roll-up sheet would likely be your best solution.