Sort by unique values and sum

[jide]

Hi,

I hope someone can point me in the right direction here.

I have a 16,000 row worksheet that is pulled weekly from a database,
values aren't static. I need to be able to filter a column by unique
values and add quantities of values that aren't unique. For example:

Carrier Date Customer RA# Store SKU Quantity EA/CS
Yellow 7/20/07 Wa 000136 153 1
ea
Roadway 6/11/07 Wa 000136 154 1896 ea
Roadway 6/11/07 Wa 000136 154 5232 ea
Yellow 7/11/07 Wa 000136 154 6
ea
Yellow 8/1/07 Wa 000136 156 6 ea
YELLOW 7/11/07 Wa 000136 NA 1503 1
Yellow 7/20/07 Wa 000136 1503 2 ea

I need all non-unique SKUs hidden or filtered, but the quantities of
non unique SKUs totaled.

Thanks,
Josh

 

[OssieMac]

Hi Josh,

I am not sure of what you actually want. You say that want "all non-unique
SKUs hidden" but you want "non unique SKUs totaled". If you are going to
hide them all, where are you going to put the totals?

Do you really mean that you want the extra SKUs hidden so that in your
example data you would finish up something like this:-

SKU Quantity
153 1
154 7134
156 6
1503 3

If you want it as above, because SKU 154 is spit between Yellow and Roadway,
which one do you want to show it against.

A picture is worth a 1000 words. Based on the data sample you have already
posted, can you post a sample of how you want it to look after processing.


Regards,

OssieMac

 

[jide]

Hi Josh,

I am not sure of what you actually want. You say that want "all non-unique
SKUs hidden" but you want "non unique SKUs totaled". If you are going to
hide them all, where are you going to put the totals?

Do you really mean that you want the extra SKUs hidden so that in your
example data you would finish up something like this:-

SKU Quantity
153 1
154 7134
156 6
1503 3

If you want it as above, because SKU 154 is spit between Yellow and Roadway,
which one do you want to show it against.

A picture is worth a 1000 words. Based on the data sample you have already
posted, can you post a sample of how you want it to look after processing.

Regards,

OssieMac

Thanks for the response,

Carrier, and the other columns don't really matter, it could show up
as either roadway or yellow. Just need an inventory overview of units
moved by SKU. Something like your example would be perfect.

SKU Quantity
153 1
154 7134
156 6
1503 3

The rest of the columns are pretty irrelevant for what I need.

Thanks again.

Josh

 

[OssieMac]

Hi Again Josh,

Since the rest of the columns are irrelevant, it's time to learn about Pivot
tables. You may need to look it up in Help but just to get you started:-

Select the 2 columns of data (including the column headers.)

To Select Pivot table:-
Pre xl2007 versions:-
Select menu item Data->Pivot Table and Pivot Chart report.
Follow the prompts in the dialog boxes.


xl2007 version:-
Select the Insert tab in the ribbon.
Select Pivot table (Far left of insert tab.
Follow the prompts in the dialog boxes.

You will have to drag the column headers to the required location in the
table.
SKU will be Row fields.
Quantity will be Data or Values.

If the values are counted (or anything else for that matter) instead of
summed, then right click on the header for Quantity (shows as Count of
Quantity etc) and select Field Settings and correct it in the dialog box. It
will change to Sum of Quantity or whatever you select for the field setting.

Pivot tables are worth mastering because when you do, you will then wonder
how you ever managed without them.

Regards,

OssieMac

 

[bigcivicj]

Thanks much,

That works just like I needed it to.

Think I'll spend a little time learning more about pivot tables.

Josh