counting matches
 

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?

--
Message posted via http://www.officekb.com

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" wrote in message
...
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?

--
Message posted via http://www.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

--
Message posted via http://www.officekb.com

Ray,

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

HTH,
Bernie
MS Excel MVP

"Ray Newman via OfficeKB.com" wrote in message
...
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

--
Message posted via http://www.officekb.com