Search with some sort of combination find and mark

[ZBEER]

Hello Excel Forum,

I would like to ask for help with the following:
I am testing a design in 3D. I have 12 unique item/data in one row, and I have 8500 rows. I have many columns with the highest number of data available. Would like to find a formula that finds and counts the most occurrences of each column per row. Sure, could find by filtering search in months, but I'm sure there is a fix for this. A fix, that can count and add up for all 12 data per rows, how many times one row of data repeats through 8500 rows. Say for example row 12 repeats as is, same order, 4 times in 8500 rows, and a cell that marks/counts it specifically for that row.

Thank you

[Crooza]

Your example didn't actually have any duplicate rows so I doctored it up for the purpose of testing. I'd probably concatenate the 12 columns into one using this formula (in say N2)

Please Login or Register  to view this content.

then use a match formula in N3 like this to idientify if other cells match. If a number other than 0 comes up it will indicate a row above that has a match

Please Login or Register  to view this content.

Example attached

[ZBEER]

Hi Crooza,

thank yo so much for the answer! Sorry couldn't get back to you sooner.
Sorry for you had to doctor it. It will work, thank you so much!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.