Compare sets of data

QuestionEdit

Victor, I appreciate your help with this. For simplicity, I will use numbers.

Let's say I have five columns with 1 row and any number 1 through 50 that can be in any cell within the row. This is repeated for the next 5 rows. If two pairs of numbers are repeated in any of the other rows, I need a count to say 2.

For example:

25 24 49 30 5 17 56 37 40 51 24 28 5 41 38 16 18 51 53 4 35 27 16 24 5

In another sheet I have the numbers 5 and 24 in cells A1 and B1 to use for evaluation. These two numbers show up 3 times within my sample, so I need C1 to say 3. This would be repeated down each row that has numbers for evaluation. For example, if I have 35 and 16 in the next row, I need c2 to say 1 as this came up only 1 time in my sample above.

I'm guessing that I would need some kind of loop that would allow me to evaluate each set of data until there is no more data to evaluate.

Can you help with this?

AnswerEdit

Excel has a built-in function COUNTIF that can easily do what you need.

Use the following formula:

=COUNTIF($A$1:$E$16,A1) =COUNTIF($A$1:$E$16,B1) ... copy down

assuming that A1:E16 contains the 5 columns of data and A1 is the value to compare in the dataset. So if the value of A1 is 5, the function will return the value 3 since 5 appears in the dataset 3 times.

Hope this helps.