Excel Catalogue item list parts Quantity problem

[dov]

Hello all,

A user posted a similar request on this post:

http://groups.google.ca/group/micro...uCevTY1wWyctPve98PQdd4_us5frce720_MwAPPaMmhGu

But, the only difference is, I would like to have the opposite done, if
possible.

What they did:

SKU Description quantity
2021 blue widget 1
2021 blue widget 1
2021 blue widget 1
2022 red widget 1
2022 red widget 1
2023 green widget 1

What we need:


SKU Description quantity
2021 blue widget 3
2022 red widget 2
2023 green widget 1


If someone could PLEASE PLEASE reply back to me at dov at mdmtrade dot
com. I would greatly appreciate it!!

Thanks a lot!

Dov

 

[Dave Peterson]

Sort your data and do data|subtotals

or data|pivottable to get those summaries.

 

[dov]

Dave,

Thank you for the prompt reply (I actually tried adding you on msn, i
dont know if you noticed) but anyhow.

I'm sorry if this is a stupid question, that did what I wanted it to
do, but it still shows both of the items with exception that the total
qnty for each item is right below. How do I just keep one record of
that item and the total qnty ? Pretty much to condense the total amount
of rows on the excel sheet as possible.

Thanks,

Dov

 

[Dave Peterson]

I'd still sort and then use data|Subtotals.

Then use the outlining symbols on the left to hide all the details.
Select the range
edit|goto|special|visible cells only
edit|copy

And edit|paste to a new location (on a new worksheet???)

Then remove the " total" by:
select the first column
edit|replace
what: (spacebar)Total
with: (leave blank)
replace all

(and delete that Grand total line if you copied it.)

and remove the formatting (boldness) if you want.

 

[Pete_UK]

A slightly different approach, which doesn't involve sorting the
original data - highlight columns A and B (SKU and Description) and
copy to a new sheet. Apply Data | Filter | Advanced filter to column B,
to select Filter in Place and Unique Records only - once your have
these you may want to sort them in Sheet 2. Then in C2 of this second
sheet enter the formula:

=SUMIF(Sheet1!B$2:B$500,B2,Sheet1!C$2:C$500)

You might want to adjust the range - I've assumed up to 500 items, so
change these as necessary. Copy the formula down as required.

Hope this helps.

Pete

 

[Dave Peterson]

I still think a pivottable would be the easiest (after the learning curve is
over) solution.

 

[Rich 80105]

Perhaps, but the SUMIF (or COUNTIF as applicable) function does give
greater flexibility over layout.

Rich