Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
One problem
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
One problem
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 Sopranos wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
One problem
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. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Sopranos" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |