Find which unique ID's has the same attributes within all affiliated columns

Question

I have multiple unique Terms of Payment codes that I am mapping to understand what exactly are the attributes of said Terms of Payment codes. Each Terms of Payment Code written in Column "A" have static attributes in column "B" to "AA". All information in each individual column is either in "General" format or "Number" format. Some Terms of Payment Codes might have the exact same attributes in all columns and some may 50% exact same attributes. However, I am looking into finding out which Terms of Payment Codes are 100% the exact same information in column "B" to column "AA". However, this proved more difficult than expected. Below you will see an example of a Terms of Payment Code and some of the columns I would like to match.

Example of Terms of Payment Codes

I tried multiple index match formulas and tried some mixes of xlookup with if statements, but I cant seem to find the right solution. All I want is a way for me to identify which Terms of Payment Codes are exactly the same!

I would appreciate if anyone has a formula I could test out cause at this point I am lost.

Answer 1

Unless you tried this before, try creating a new column that will either concatenate all attributes in a single string, or generate a number by using each order of magnitude as a numeric code of particular attribute. You will get a unique identifier of every existing attributes' combo. Then, your task will be straightforward, either via a pivot table, or a formula setting a flag, or by grouping.

Answer 2

You could create an extra column, for example in column "AB" at the end of your table of data, and assign the cell content to be all of the previous columns concatenated. Using the first row of data as an example for this, you would enter into cell "AB2": =CONCAT(A2:AA2) (and then you can drag this down for all rows).

Now you can identify duplicates in this column with conditional formatting (Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values).

Note: This method will not tell you where a match is. It will only tell you if if 1 or more exact duplicates exist. It also doesn't fully depend on only formulas, which you might be after.

If you're after both of these things, you could add another column, for example column "AC", and use the COUNTIF function to know how many duplicates a row has, with the MATCH function to look for the first instance of a duplicate.

For the first row of data in cell "AC2", this would look something like (assuming the last row number is 1000):

=IF(COUNTIF(AB$2:AB$1000,AB2)>1, MATCH(AB2,AB$2:AB$1000), "Unique")

This can then be dragged down for all rows.

Now you will have the row number of the first instance of the exact same data, and the word "Unique" if there are no matches. This will also mean you can group stuff together since the first row number will act as a unique "code" for the groups of duplicates.

Hope this makes sense and helps!