table of # of rows with unique combos of values in 4 separated col
 

I have a spreadsheet such as the following
A 1 4 F
A 1 4 F
A 2 4 G
A 2 4 G
A 2 4 H
B 3 5 M
B 3 5 M
B 1 4 F
and want to create a table that shows how many of each unique combination
are in my sheet.
Count
A 1 4 F 2
A 2 4 G 2
A 2 4 H 1
B 3 5 M 2
B 1 4 F 1
There are about 500 unique combinations, so I need to be able to generate
the list automatically, and there are other columns in between with data I
want to ignore.

Any suggestions?

table of # of rows with unique combos of values in 4 separated col
 

One quick option - use a pivot table

Some easy steps to lead you in (xl03)
Insert a new top header row, label it as say: Dat1,Dat2,..Dat4
Click Data Pivot table ... Click NextNext.

In step3, click Layout
1. Drag n drop Dat1,Dat2,..Dat4 within ROW area, one below the other
Double-click on each header, set Subtotals to None
2. Drag n drop 1 of the 4 headers, say, Dat1 within DATA area
Ensure it's set to "Count of"
(If it appears as "Sum of" when you drop it there,
just double-click on the header, then set it to summarize by Count)
3. Click OKFinish. That's it!

Hop over next door (the new pivot sheet to the left) for the results, viz:
the Unique "combos" listing & the count of each
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"louise" wrote:
I have a spreadsheet such as the following
A 1 4 F
A 1 4 F
A 2 4 G
A 2 4 G
A 2 4 H
B 3 5 M
B 3 5 M
B 1 4 F
and want to create a table that shows how many of each unique combination
are in my sheet.
Count
A 1 4 F 2
A 2 4 G 2
A 2 4 H 1
B 3 5 M 2
B 1 4 F 1
There are about 500 unique combinations, so I need to be able to generate
the list automatically, and there are other columns in between with data I
want to ignore.

Any suggestions?

table of # of rows with unique combos of values in 4 separated col
 

Assume your data in column A to column D

Create a helper column E
In E1: =A1&B1&C1&D1
copy down
RngE is a defined name range in column E

In Column F
In F1:
=IF(ISERR(SMALL(IF(MATCH(RngE,RngE,)=ROW(INDIRECT( "1:"&ROWS(RngE))),MATCH(RngE,RngE,)),ROWS($1:1))), "",INDEX(RngE,SMALL(IF(MATCH(RngE,RngE,)=ROW(INDIR ECT("1:"&ROWS(RngE))),MATCH(RngE,RngE,)),ROWS($1:1 ))))

ctrl+shift+enter, not just enter
copy down

In Column G
In G1: =IF(F1="","",COUNTIF(RngE,F1))
copy down


"louise" wrote:

I have a spreadsheet such as the following
A 1 4 F
A 1 4 F
A 2 4 G
A 2 4 G
A 2 4 H
B 3 5 M
B 3 5 M
B 1 4 F
and want to create a table that shows how many of each unique combination
are in my sheet.
Count
A 1 4 F 2
A 2 4 G 2
A 2 4 H 1
B 3 5 M 2
B 1 4 F 1
There are about 500 unique combinations, so I need to be able to generate
the list automatically, and there are other columns in between with data I
want to ignore.

Any suggestions?

table of # of rows with unique combos of values in 4 separated
 

I assumed by defined name range youo meant something like E$2:E$2500 (row 1
is my header row in the real file, row 2500 has the last entry). It seems to
be working, though Excel is having to chug for quite some time!

THANK YOU Teethless mama, because I was never going to stumble my way into
creating that formula, and I just don't seem to grasp pivot tables!

"Teethless mama" wrote:

Assume your data in column A to column D

Create a helper column E
In E1: =A1&B1&C1&D1
copy down
RngE is a defined name range in column E

In Column F
In F1:
=IF(ISERR(SMALL(IF(MATCH(RngE,RngE,)=ROW(INDIRECT( "1:"&ROWS(RngE))),MATCH(RngE,RngE,)),ROWS($1:1))), "",INDEX(RngE,SMALL(IF(MATCH(RngE,RngE,)=ROW(INDIR ECT("1:"&ROWS(RngE))),MATCH(RngE,RngE,)),ROWS($1:1 ))))

ctrl+shift+enter, not just enter
copy down

In Column G
In G1: =IF(F1="","",COUNTIF(RngE,F1))
copy down


"louise" wrote:

I have a spreadsheet such as the following
A 1 4 F
A 1 4 F
A 2 4 G
A 2 4 G
A 2 4 H
B 3 5 M
B 3 5 M
B 1 4 F
and want to create a table that shows how many of each unique combination
are in my sheet.
Count
A 1 4 F 2
A 2 4 G 2
A 2 4 H 1
B 3 5 M 2
B 1 4 F 1
There are about 500 unique combinations, so I need to be able to generate
the list automatically, and there are other columns in between with data I
want to ignore.

Any suggestions?

table of # of rows with unique combos of values in 4 separated
 

.. though Excel is having to chug for quite some time!
... and I just don't seem to grasp pivot tables!

Failed to mention in my response earlier that the pivot option
gets you there in a matter of seconds

Perhaps a sample file to illustrate:
http://www.freefilehosting.net/download/3i3il
Unique combos via pivot.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

table of # of rows with unique combos of values in 4 separated
 

Thanks for persisiting Max, and helping me figure out how to use a pivot
table. And it was a LOT faster!

"Max" wrote:

.. though Excel is having to chug for quite some time!
... and I just don't seem to grasp pivot tables!

Failed to mention in my response earlier that the pivot option
gets you there in a matter of seconds

Perhaps a sample file to illustrate:
http://www.freefilehosting.net/download/3i3il
Unique combos via pivot.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

table of # of rows with unique combos of values in 4 separated
 

No prob, Louise. You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"louise" wrote in message
...
Thanks for persisting Max, and helping me figure out how to use a pivot
table. And it was a LOT faster!

table of # of rows with unique combos of values in 4 separatedcol
 

Teethless mama wrote...
Assume your data in column A to column D

Create a helper column E
....

Unnecessary AND can lead to problems. If the OP's data is simplified
for posting, then it's possible (likely) some of the fields may be
multiple character. If different fields could have varying lengths,
then the records

XY 1 52 M
XY 15 2 M

should be treated as distinct, but they'd be treated as identical
using simple concatenation.

Assuming the results would be in cols G to K (G-J fields, K distinct
count) with the first result in row 1,

G1: =A1

Fill G1 right into H1:J1.

K1: =SUMPRODUCT((A$1:A$8=G1)*(B$1:B$8=H1)*(C$1:C$8=I1) *(D$1:D$8=J1))

G2: =INDEX(A$1:A$8,SUM(1,$K$1:$K1),0)

Fill G2 right into H2:J2, then fill K1 down into K2, then select G2:K2
and fill down as far as needed. This should recalc fairly quickly, and
unlike a pivot table, it'll recalc automatically.