Home |
Search |
Today's Posts |
#1
|
|||
|
|||
HELP! Filtering and sorting lists.
Hi Folks. I have 2 columns of info, 'A' contains quantities and 'B'
contains product name. The same product appears multiple times in column B, with a respective quantity of the product in the same row in column 'A'. For example, row 1 may detail 15 apples, row 2 may contain 12 bananas, etc. IF row 3 also has apples, say with a quantity of 5 apples and row 4 has bananas with say a quantity of 6, how do I consolidate the data to to present a total of 20 apples and 18 bananas? I hope my simple analogy is clear. I would ideally like the info presented as a consolidated list, ie row 1 = 20 apples; row 2 = 18 bananas. Any assistance available would be readily and gratefully received. Thx. AJ Fletcher |
#2
|
|||
|
|||
One way is to try Pivot Table?
Taking your example, assume the table below in A1:B5 in Sheet1 (say) Qty Prod 15 apples 12 bananas 5 apples 6 bananas Click anywhere within the data Click Data Pivot Table Report Next Next In step 3 of the wizard: drag Prod and drop within ROW area drag Qty and drop within DATA area (it'll appear as Sum of Qty) Click Finish The Pivot Table (PT) will appear in a new sheet to the left of Sheet1 The PT will appear as: Sum of Qty Prod Total apples 20 bananas 18 Grand Total 38 (Do hang around awhile for other ways / insights ..) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- wrote in message oups.com... Hi Folks. I have 2 columns of info, 'A' contains quantities and 'B' contains product name. The same product appears multiple times in column B, with a respective quantity of the product in the same row in column 'A'. For example, row 1 may detail 15 apples, row 2 may contain 12 bananas, etc. IF row 3 also has apples, say with a quantity of 5 apples and row 4 has bananas with say a quantity of 6, how do I consolidate the data to to present a total of 20 apples and 18 bananas? I hope my simple analogy is clear. I would ideally like the info presented as a consolidated list, ie row 1 = 20 apples; row 2 = 18 bananas. Any assistance available would be readily and gratefully received. Thx. AJ Fletcher |
#3
|
|||
|
|||
Cheers Max - Perfect. I always wondered what pivot tables were all
about. Andy |
#4
|
|||
|
|||
You're welcome, Andy !
Thanks for the feedback .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- wrote in message oups.com... Cheers Max - Perfect. I always wondered what pivot tables were all about. Andy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting and filtering | Excel Worksheet Functions |