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
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.
not a professional, just trying to assist.....
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
"spydor" wrote:
>
> 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
>
>
> --
> spydor
> ------------------------------------------------------------------------
> spydor's Profile: http://www.excelforum.com/member.php...o&userid=28438
> View this thread: http://www.excelforum.com/showthread...hreadid=482482
>
>
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
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)
"spydor" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> spydor
> ------------------------------------------------------------------------
> spydor's Profile:
http://www.excelforum.com/member.php...o&userid=28438
> View this thread: http://www.excelforum.com/showthread...hreadid=482482
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks