One problem

S

Sopranos

I have numbers in 7 columns (A-G) and 499 rows (1-499).
-Question 1: How could I see if exist two or more same rows?For example:


A B C D E
F G
3 4 19 21 25 32 38
2 3 20 22 24 25 38
6 12 13 23 28 38 39
3 6 7 17 32 37 38
5 14 23 27 29 31 37
3 4 19 21 25 32 38
5 6 11 20 22 24 34
2 5 18 22 23 25 37
2 7 13 18 25 31 32
5 13 17 28 32 36 39


How can I get how manny combinations of 3 4 19 21 25 32 38

is there in whole table?


-Question 2: How could I see how manny combinations with 5 same numbers and
two diferent there is? For example:

A B C D E
F G
3 4 19 21 25 32 38
2 3 20 22 24 25 38
6 12 13 23 28 38 39
3 6 7 17 32 37 38
5 14 23 27 29 31 37
3 4 19 21 24 27 38
5 6 11 20 22 24 34
2 5 18 22 23 25 37
2 7 13 18 25 31 32
5 13 17 28 32 36 39


How can i see that there is 2 combinations where exist 5 same numbers and 2
diferrent?

3 4 19 21 25 32 38


3 4 19 21 24 27 38



Sorry for my english... I hope that you understand me... Thanks!
 
K

Ken

The first part is pretty easy. In column H put the formula

=A5&B5&C5&D5&E5&F5&G5

to concatenate the first 7 columns. Then sort by that column to get
all the identical rows together. You can put formula in the next
column to count how many each combination, or filter the unique
combinations to another location and put your count formula next to the
unique records. You will be able to see pretty easily how many of the
specific combintation you have.

As for Question 2, I think you need to explain a little more.

Good luck.

Ken
Norfolk, Va
 
M

Martin Fishlock

Q1: To check similar rows, assuming that they are ordered, in cell H2
(assuming start at row 2) =A2&":"&B2&":"&C2&":"&D2&":"&E2&":"&F2&":"&G2
and in cell I2 =COUNTIF($H$2:$H$11,H2)
This will give you the number of rows that are the same where the number is
larger than 1.
Q2 & Q3: There are permut and combin functions in Excel but you need to work
out exactly what you are asked for in your questions.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top