Solved

Excel - Sort multiple columns based on single column

Posted on 2012-12-20
7
375 Views
Last Modified: 2012-12-28
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
Comment
Question by:tru504187211
  • 3
  • 2
  • 2
7 Comments
 
LVL 7

Expert Comment

by:leptonka
ID: 38711540
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
 
LVL 9

Expert Comment

by:chwong67
ID: 38711558
You could also use COUNTIFS formula...
0
 

Author Comment

by:tru504187211
ID: 38711559
Thanks for both of your replies...do you have a simple example of what you mean?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 9

Accepted Solution

by:
chwong67 earned 350 total points
ID: 38711601
Attached sample...
sample.xls
0
 

Author Comment

by:tru504187211
ID: 38711609
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
 
LVL 7

Assisted Solution

by:leptonka
leptonka earned 150 total points
ID: 38711624
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
 

Author Closing Comment

by:tru504187211
ID: 38727067
COUNTIFS worked best in my situation, but both solutions could work.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now