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

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

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