Sort A-Z but keep Products/Variation Integrity-CodePudding

I am working on a price sheet that has products with variations to that product underneath. It has 15,000 rows and I would like to sort in alphabetical order.

However doing so breaks the product/variations, so you wont know what variations are with what product. I have color coded all the products as well as the variations.

What I need is the Products to be sorted A-Z, along with keeping the variations underneath it.

Sorting breaks the product/variation setup.

CodePudding user response：

Using an excel formula, here is a possible approach. In cell D2, put the following formula:

=LET(rng, A2:B9, types, INDEX(rng,,1), names, INDEX(rng,,2),
  groups, SCAN("", names, LAMBDA(acc,name, IF(acc="",name,
    IF(XLOOKUP(name, names, types)="Product", name, acc)))),
 DROP(SORT(HSTACK(rng, groups), COLUMNS(rng) 1),,-1)
)

Here is the output:

groups name contains an array of the same number of rows of rng, having the product name for the product and its variation. Then we just need to sort by this intermediate column created the input range.

Note: This solution assumes the first row always starts with a Product, not a Variation.

Adding a helper column C is another alternative. For example, put in C2 the following formula and drag it down:

=IF(A2="Product", B2, C1)

then you can use this column for sorting. This is exactly what the SCAN formula does, but generating the values programmatically.

CodePudding user response：

Make a new column in front of A (or wherever, doesn't really matter, visually is easiest imo): "Sort"

=If(B2 = "Product"; C2; A1 1)

Copy paste as Values and then sort descending. You can delete/hide the A column after then. Making "keys" is the easiest way to sort/organize/check for doubles.