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

[louise]

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?

 

[Max]

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 Next>Next.

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 OK>Finish. 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

 

[Teethless mama]

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(INDIRECT("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]

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!

 

[Max]

.. 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

---

 

[louise]

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

 

[Max]

No prob, Louise. You're welcome.

 

[Harlan Grove]

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$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.