Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count data or list
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 |
#2
|
|||
|
|||
Count data or list
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. -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=482482 |
#3
|
|||
|
|||
Count data or list
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 |
#4
|
|||
|
|||
Count data or list
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 -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=482482 |
#5
|
|||
|
|||
Count data or list
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation and Blanks in List | Excel Worksheet Functions | |||
Data Val list Excel 97 fire macro | Excel Discussion (Misc queries) | |||
HOW DO I MAKE VALIDATION LIST CONTAING DATA FROM A DIFFERENT BO | Excel Discussion (Misc queries) | |||
How do I get LIST on the DATA menu bar-I need Create List | New Users to Excel | |||
Using Validation List from Another Workbook with Dependent Data | Excel Worksheet Functions |