counting matches

[Ray Newman via OfficeKB.com]

I have a range in a spreadsheet consisting of 80 columns by 30 rows, I am
trying to sort and identify the matches included in that data. i would like
to be able to get a result showing the combination of times for 2 same, 3
same, 4 same etc and show the combo's.....any ideas?

 

[Bernie Deitrick]

Ray,

What do you mean by "Show the combo's"?

If you only want to get a count of matches in the data, try this.

If your data is in A1:CB30, first insert two columns at column A and B,
moving your table to C1:CD30, then insert a row at row 1, moving your table
to C2:CD31.

Then in cell A1, enter the label "Values", and in cell A2, enter the formula
=INDEX($C$2:$CD$31,MOD(ROW()-2,30)+1,INT((ROW()-2)/30)+1)

Then copy that formula down to A2:A2401.

Select the entire column of formulas, including the label, and use Data |
Pivot Table.... and click "Finish"

Drag the "Values" button once to the rows area, and once to the data area,
and you will get a table of the unique values in the table, and the number
of times they each appear.

HTH,
Bernie
MS Excel MVP

 

[Ray Newman via OfficeKB.com]

Bernie...let me clarify...i am trying to get the combination of number that
match in groups of 2,3,4,5,6,7,8,9,10. and the number of times they match

 

[Bernie Deitrick]

Ray,

You're going to have to be a little more clear. Post an example table with
a description.

HTH,
Bernie
MS Excel MVP