Sorting with following items

New Contributor

There is probably an easy way to do this, but I can't find/figure it out.

 

I want to go shopping to the Grocers, Wal Mart, and the Pharmacy.   I have several items to get at each place.

 

So I enter "Grocer" in A1, and put the items I need there in B2, B3, B4 and so on.

 

I enter "Wal Mart" in A5, and put the items I need there in B6, B7 B8.

 

I enter "Pharmacy in A9, and put the items I need there in B10, B11, andso on.

 

Now I want to sort my shopping list alphabetically, but I want the items from each store to "follow" the store in the store's place in the list.  (In my real problem, I have 144 items for column A, and 10-12 items for each Column A entry in Column B)   For reasons of printing, I have to list the items in Column B vertically, and NOT horizontally.

 

HOW CAN I DO THIS?     Thanks!!!!

 

 

5 Replies

@Retiredtrialjudge 

There is probably an easy way to do this... Not really. Recording data as you do causes challenges when it comes to sorting, grouping, suming...

 

Attached is a Power Query option with your data in columns A-B formated as Table (Header Row isn't displayed). On sheet SORTED, the same as above, Sorted by [Shop]

 

When you add data to your Shopping list > Switch to SORTED > Right-click somewhere in Column A-B > Refresh

@Retiredtrialjudge 

L z. is right about challenges when you store data that way, but I think there is still an easy solution...

 

The shop names are not in alphabetical order (and you presumably want them to remain in that order).  If you are willing to prefix them with a number (1-Grocer, 2-Wal Mart, 3-Pharmacy) that helps a lot.  Then, you just need a new column (which can be hidden, or at least not included in the print area) with a formula that combines cell values for sorting purposes (shown here for row 1 only):

 

 

=LOOKUP(2,1/(COUNTIF(A$1:A1,">"&A$1:A1&CHAR(7))=0),A$1:A1) & B1

 

 

Copy (drag) that formula down to the remaining data cells in that column.

 

So then you'll have...

1-Grocer 1-Grocer
 lollipop1-Grocerlollipop
 lollipop1-Grocerlollipop
 oh, lolli…1-Groceroh, lolli…
2-Wal Mart 2-Wal Mart
 lime powder2-Wal Martlime powder
 coconut oil2-Wal Martcoconut oil
 gravy boat2-Wal Martgravy boat
3-Pharmacy 3-Pharmacy
 Love Potion No. 93-PharmacyLove Potion No. 9
 Brylcreem3-PharmacyBrylcreem

[hoping this comes out OK; the preview function complains about an error in the HTML it generated]

 

Sort all the rows on (ascending) values in the new column, but only select columns A & B for printing.

@SnowMan55    Thanks Snow Man!

 

That would be a LOT of work for my 1500+ entries.    Easier to cut and paste by store.    But thanks for the thoughts.

Thanks LZ!!!

@Retiredtrialjudge 

 

Another option if you run Excel 365. With your data in Table named ShoppingList (as previously shared):

 

=LET(
  FillShop, SCAN(,ShoppingList[Shop],
    LAMBDA(seed,shop, IF(shop <> "", shop, seed))
  ),
  StackData, HSTACK(FillShop, IF(ShoppingList <> "", ShoppingList, "")),
  DROP(SORT(StackData, 1),,1)
)

 

In attached file next to the Power Query option in sheet SORTED