count of same data in 4 columns

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
723
I have 6 columns of data where I need to sum the count of identical entrys in 4 of the columns. Can't get VBHTML to work yet so pardon my alignment!
I need to find out how many entries match in columns a b c f

A B C D E F
Style Series Color A B Size
DH 100 WH 20 30 84
DH 100 WH 20 30 84
DPD 100 WH 20 30 102
DPD 100 WH 20 30 98
DPD 100 WH 20 30 98
C1 100 WH 20 30 88
C1 100 WH 20 30 88
DH 100 WH 20 30 88
DH 100 WH 20 30 88
DH 100 WH 20 30 71
BH 200 WH 20 30 67
BH 200 WH 20 30 67

result to be:

Q Style Series Color Size
2 DH 100 WH 84
1 DPD 100 WH 102
2 DPD 100 WH 98
2 C1 100 WH 88
2 DH 100 WH 88
1 DH 100 WH 71
2 BH 200 WH 67

(ideally, sorted by 2nd column of results)

Thanks!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

A pivot table can get you the data and almost the layout you want:
Book1
ABCDEFGHIJKL
1StyleSeriesColorABSizeCount of Size
2DH100WH203084StyleSeriesColorSizeTotal
3DH100WH203084BH200WH672
4DPD100WH2030102C1100WH882
5DPD100WH203098DH100WH711
6DPD100WH203098842
7C1100WH203088882
8C1100WH203088DPD100WH982
9DH100WH2030881021
10DH100WH203088
11DH100WH203071
12BH200WH203067
13BH200WH203067
Sheet1
 

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,749
I can't get into details now as I am about to go to bed, but you can do it by:

1. Making a new field either in the left or right of your data, concatenating each of the fields then you can do advanced filtering to extract unique values into a sepaprate range. After this, you can use countif.

2. You can construct pivot table and just place all the fields in the row fields and one of the fields in the data field.
 

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
723
Fairwinds:
Thanks but aside from not understanding how to set up the pivot table to get the results you did, I do need the style to show on each line as well.

Patsys:

That was the avenue I had started to follow but once the concantenations were done, I wasn't successful in doing any filtering. It appeared to filter the formulas rather than the data, therefore did not change the list when I checked unique data only.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

data | filter | advanced filter | unique records only & copy to another location. you can also limit the columns of data you copy to the new location by setting up only the column headers for the columns you need. check out the excel help topic 'Filter by using advanced criteria', and the advice in the 'tip':

"When you copy filtered rows to another location, you can specify which columns to include in the copy. Before filtering, copy the column labels for the columns you want to the first row of the area where you plan to paste the filtered rows. When you filter, enter a reference to the copied column labels in the Copy to box. The copied rows will then include only the columns for which you copied the labels. "

That'll set up the unique data in the format you want. the count can achieved with something like:

=SUMPRODUCT(--(CONCATENATE($A$1:$A$10,$B$1:$B$10,$C$1:$C$10)=CONCATENATE(E1,F1,G1)))

...with ranges etc adjusted to suit your data
 

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
723
Paddy:

I've got the filtering working correctly but can't get the sumproduct to function. Based on my interpretation, the 2nd half of the formula needs to have the range of 12 rows since it's entirely possible the filtered data may be identical to the original data, therefore I changed the formula to:

=SUMPRODUCT(--(CONCATENATE($AI$10:$AI$21,$AJ$10:$AJ$21,$AK$10:$AK$21,$AL$10:$AL$21)=CONCATENATE(AP10:AP21,AQ10AQ21,AR10:AR21,AS10:AS21,AT10:AT21)))

which returns - #NAME?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697
You've missed a colon within one of your references. Try replacing...

AQ10AQ21

with

AQ10:AQ21

Hope this helps!
 

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
723
Domenic:

Thank you for pointing out the obvious! With that and changing the formula to:

=SUMPRODUCT(--(CONCATENATE($AI$10:$AI$21,$AJ$10:$AJ$21,$AK$10:$AK$21,$AL$10:$AL$21)=CONCATENATE(AP10,AQ10,AR10,AS10,AT10)))

as shown by Paddy, it works perfectly. Now I know why I've missed this board for the past several years...

Thanks to all
 

Forum statistics

Threads
1,136,504
Messages
5,676,244
Members
419,616
Latest member
quickflip

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top