Solved

Excel 2010 Formula to dynamically sort rows

Posted on 2016-09-19
9
52 Views
Last Modified: 2016-09-20
Hi,

I have an Excel worksheet with a master list of 728 supply items (e.g. pens, paper, etc.), each with their corresponding Item Type, Vendor, Subitem type, Item Number, Minimum Quantity, etc.  The master list is grouped by Item Type and sub-grouped by Vendor to simplify reordering.  I'm looking for an index/match array formula to use in a second worksheet so I can display the same data grouped by Subitem type (ideally not in alphabetical order, but in an order specified by a column of SubItem types on a different worksheet).  I don't want to use VBA and I want the second worksheet to automatically update when I add an item to the master list.  I know that sounds like a tall order, but I thought I'd ask anyway:).  I've uploaded the master list.

Thanks,

Dan
Master-List.xlsx
0
Comment
Question by:drmerwin
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 6

Expert Comment

by:DPatel
ID: 41805997
Which are the criteria?

Provide it
0
 
LVL 6

Expert Comment

by:DPatel
ID: 41806004
You should submit the sample Output to get work on this
0
 
LVL 6

Assisted Solution

by:DPatel
DPatel earned 125 total points
ID: 41806049
Find the use of index, match and array formula incorporated in the sheet attached herewith.
Master-List_Match-Array-Formula.xlsx
0
 
LVL 6

Expert Comment

by:DPatel
ID: 41806054
After opening the file press Ctrl+Shift+Enter in Cell 'C' to implement an array formula.
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 8

Assisted Solution

by:Koen
Koen earned 125 total points
ID: 41806244
the easiest way to do this without code and without creating a new list is as follows:

- turn your inventory into a table (that way when you add lines the range expands automatically and any formula is copied down automatically)
- add a column where you do a vlookup of the Sortindex (i.e. a logical number you add to the list on the seperate sheet with the subtypes -since you want the custom sort)
- then use the filter to sort on this index
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 41807596
Check attached.

The sub item sort list is on the sheet SortIndex, where the list is in reverse order.
Just to show that the order does not matter.
It can be any order, as long as all subitems are there, if not an error will occur.
The sort order list counts the number of subitems, and finds the start row for that subitem to use on the sheet SubItemSort.

Column Q (Index) on sheet Inventory sets the position (row) to use on SubItemSort.
Column Q (Index) on sheet SubItemSort finds the row to use on Inventory.
It is not specifically needed, but makes the Index functions on SubItemSort much faster, when the row number is only searched once.

The Index functions on SubItemSort are made to row 1000, just copy down if more is needed.

Inventory and SortIndex are tables, because then formulas automatically adjust.
Master-List-Index.xlsx
0
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 250 total points
ID: 41807601
Revised version where empty cells are not shown as 0.
Master-List-Index.xlsx
1
 

Author Comment

by:drmerwin
ID: 41807945
That is great!  I still have some small details to work out but your solution is terrific and has opened my eyes to the benefit of tables in Excel.

All I have to do is manually change the order of SortIndex Column A and the SubItemSort worksheet will update, right?  Is there anything else I need to know?  Thank you so much for helping with this!!!

Dan
0
 

Author Closing Comment

by:drmerwin
ID: 41807947
Can't thank y'all enough for your help.  Have a great day.
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

Suggested Solutions

Title # Comments Views Activity
Excel conversion issue with Sql server 14 45
time format showing wrong 12 49
Microsoft Edge, Outlook OWA 7 35
Excel:  Find text between 2nd hyphen and next space 10 29
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 …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

947 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

21 Experts available now in Live!

Get 1:1 Help Now