Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 406
  • Last Modified:

Excel - Sort multiple columns based on single column

Hi everyone,

Hopefully I can explain this correctly...

I am creating a spreadsheet to keep track of inventory.  I would like to take the SKU #, model #, size, and style (wide, medium, etc.), which are already in a row...

SKU                          MODEL     SIZE    STYLE
829024209851      26063      14          W
829024209852      26063      12          W
829024209853      26063      19          M

...and copy them into another worksheet on the same spreadsheet in a different format.

MODEL     STYLE          12     13     14     15     16     17     18     19
26063      W                 1                1
26063      M                                                                                1

...What this would do is allow there to be a quick way of seeing how many of each style there are per size.  Each row is a different model (of course, each model has multiple styles, or widths, such as medium, wide, extra wide, etc.).

Is this possible???
0
tru504187211
Asked:
tru504187211
  • 3
  • 2
  • 2
2 Solutions
 
leptonkaCommented:
I suggest to use Pivot table.
Model and Style will be in Row header, Size in column header and the Data could be Count of size.

Cheers,
Kris
0
 
chwong67Commented:
You could also use COUNTIFS formula...
0
 
tru504187211Author Commented:
Thanks for both of your replies...do you have a simple example of what you mean?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
chwong67Commented:
Attached sample...
sample.xls
0
 
tru504187211Author Commented:
AWESOME!....I think the last part then is getting the formula to have the single row of 26063 W, and the single row of 26063 M...from the rows below...

SKU                          MODEL     SIZE    STYLE
829024209851      26063      14          W
829024209852      26063      12          W
829024209853      26063      19          M

I'm really scuffling with this part...
0
 
leptonkaCommented:
Hi,

Attached a pivot table example.
If you have new data rows - go to pivot table menu (you find it above the ribbon, when the pivot table is active) and change the data source.
If you change the data in the table, right click on the table, then refresh.
(You can see it on sheet Data and Pivot.)

OR

You can define your data as Table and build the pivot table on this table. This way it will be dynamic, when you add new data to the Table (write in the rows below) you will see the Table is expanding. You only need to refresh your Pivot table, it will immediately capture the new data rows.
(You can see this example on sheet Data as Tabel and Dynamic Pivot.)

Cheers,
Kris
Pivot-table-example.xlsx
0
 
tru504187211Author Commented:
COUNTIFS worked best in my situation, but both solutions could work.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now