Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
count unique combinations
Hi,
I have a spreadsheet with 6 columns. I need to count how many occurences of each unique set of data in the first 5 columns. For example: from column A to F: 2 1 0 a b 10:00 2 0 0 a b 11:00 3 1 2 d f 12:00 1 1 2 a c 13:00 1 1 2 a c 14:00 2 1 0 a b 15:00 The macro will give results below: The first 5 columns show the values of each unique combination and the last column has the number of occurences for that combination in the source data. 2 1 0 a b 2 2 0 0 a b 1 3 1 2 d f 1 1 1 2 a c 2 Thanks a lot, Mini |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
count unique combinations
I'd use a helper column and concatenate the values in each row to a larger
string in that helper column: =a1&"|"&b1&"|"&c1&"|"&d1&"|"&e1 | is just a separator that isn't used in any of the fields. Then you could sort by that column and use Data|Subtotals to get your count. (and hide the details using the outlining symbols to the left if you want.) mini wrote: Hi, I have a spreadsheet with 6 columns. I need to count how many occurences of each unique set of data in the first 5 columns. For example: from column A to F: 2 1 0 a b 10:00 2 0 0 a b 11:00 3 1 2 d f 12:00 1 1 2 a c 13:00 1 1 2 a c 14:00 2 1 0 a b 15:00 The macro will give results below: The first 5 columns show the values of each unique combination and the last column has the number of occurences for that combination in the source data. 2 1 0 a b 2 2 0 0 a b 1 3 1 2 d f 1 1 1 2 a c 2 Thanks a lot, Mini -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
count unique combinations
Thanks, it works very well though it takes some time for the subtotal
command to finish. Regards, Mini "Dave Peterson" wrote: I'd use a helper column and concatenate the values in each row to a larger string in that helper column: =a1&"|"&b1&"|"&c1&"|"&d1&"|"&e1 | is just a separator that isn't used in any of the fields. Then you could sort by that column and use Data|Subtotals to get your count. (and hide the details using the outlining symbols to the left if you want.) mini wrote: Hi, I have a spreadsheet with 6 columns. I need to count how many occurences of each unique set of data in the first 5 columns. For example: from column A to F: 2 1 0 a b 10:00 2 0 0 a b 11:00 3 1 2 d f 12:00 1 1 2 a c 13:00 1 1 2 a c 14:00 2 1 0 a b 15:00 The macro will give results below: The first 5 columns show the values of each unique combination and the last column has the number of occurences for that combination in the source data. 2 1 0 a b 2 2 0 0 a b 1 3 1 2 d f 1 1 1 2 a c 2 Thanks a lot, Mini -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filtering unique combinations of cells | Excel Discussion (Misc queries) | |||
Need possible combinations of 7 unique characters. | Excel Worksheet Functions | |||
Counting unique combinations in two columns | Excel Discussion (Misc queries) | |||
How count these combinations ? | Excel Programming | |||
Count combinations | Excel Programming |