Count data or list

[spydor]

I am trying to count a data group and return the quanity of unique
entries:
It might look like this:

The data would be in cols & rows

2 green widgets 25.5 inches long
3 green widgets 28.3 inches long
1 brown widgets 52.1 inches long
2 green widgets 25.5 inches long
1 brown widgets 52.1 inches long

the result would be:

4 green widgets 25.5 inches long
3 green widgets 28.3 inches long
2 brown widgets 52.1 inches long

Any simple straight forward way to do this???
Thanks,
Spydor

 

[duane]

since you do not know the unique items in advance (?) as well as the
number of them I think vba is only way to do it.

 

[Guest]

A Pivot Table would summarize your data for you.

Rows would be Product, Color, and Length
Data would be Sum of ItemCount

Is that something you can work with?

••••••••••
Regards,
Ron

 

[duane]

try this - note my assumptions - there no doubt is a more slick way to
do this

Sub Macro1()
'max of 100 rows of data assumed
Dim combo(100) As String
'
'assume first row of data is row 3 and data is in colums A:C
A is #
B is color of widgets
C is length of widgets
'
startrow = 3
endrow = Cells(startrow, 1).End(xlDown).Row
'read in unique widget color and length combos
i = 1
'
' first combo is unique of course (column 2 being color and column 3
being length)
'
combo(i) = Cells(startrow, 2).Value & " " & _
Cells(startrow, 3).Value
For j = startrow + 1 To endrow
thiscombo = Cells(j, 2).Value & " " & _
Cells(j, 3).Value
For k = 1 To i
If combo(k) = thiscombo Then GoTo dup
Next k
' define new unique combo
i = i + 1
combo(i) = thiscombo
dup:
Next j
'
'write unique combo list to column F
'
For l = 1 To i
Cells(l, 6).Value = combo(l)
Next l
'
'insert summation formula to column E row 1
'
Cells(1, 5).Formula = _
"=SUMPRODUCT((F1=$B$3:$B$7&"" ""&$C$3:$C$7)*($A$3:$A$7))"
For l = 1 To i
Cells(1, 5).Copy Cells(l, 5)
Next l
End Sub

 

[Bob Phillips]

Is that one column for the number, and one for the description, or many for
the description?

--

HTH

RP
(remove nothere from the email address if mailing direct)