Sorting with following items

[Retiredtrialjudge]

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!!!!

[#2]

@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

[#3]

@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
	lollipop	1-Grocerlollipop
	lollipop	1-Grocerlollipop
	oh, lolli…	1-Groceroh, lolli…
2-Wal Mart		2-Wal Mart
	lime powder	2-Wal Martlime powder
	coconut oil	2-Wal Martcoconut oil
	gravy boat	2-Wal Martgravy boat
3-Pharmacy		3-Pharmacy
	Love Potion No. 9	3-PharmacyLove Potion No. 9
	Brylcreem	3-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.

[#4]

@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.

[#5]

Thanks LZ!!!

[#6]

@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