1

I have a large spreadsheet (679 rows x 243 columns) consisting of a series of product SKUs in the first column, and each subsequent column represents an accessory. Every SKU to which the given accessory is applicable has the accessory part number populated. Because not every accessory is applicable to every SKU, a significant number of cells are populated by a dash (signifying "N/A"). enter image description here

I need to reorder this data in two columns: the first column to contain the product SKU, and the second column to contain the applicable accessory part numbers (each on its own row). The first column will repeat the product SKU in as many rows as necessary until all applicable accessories for that SKU have been populated. Subsequent SKUs will be populated similarly on subsequent rows. No dashes are to appear in the final accessories column. enter image description here

How can I accomplish this automatically? Multiple steps are fine, I'm just trying to avoid manually copying/pasting/deleting data by hand.

TRANSPOSE puts the accessory numbers (and dashes) in columns as desired, but the SKU is put once as a column header rather than repeated in a parallel column. If I could insert a new column to the left of each existing column and populate the existing column header in every row that has data, I could then use VSTACK to place all the data in two columns. Then I could delete the rows with dashes with a column filter. enter image description here

foolishgrunt
  • 117

1 Answers1

0

Two solutions, each courtesy of the comments section.

Solution #1 (courtesy of P.b):

  1. (If necessary) Copy/paste data into new worksheet and delete whatever few rows and columns whose data are irrelevant to the final product
  2. On new worksheet, enter the following formula: =LET(a,A2:A3,b,B2:G3,z,LAMBDA(r,TOCOL(IFS(b<>"-",r),2)),HSTACK(z(a),z(b)))

...where A2:A3 = SKUs range and B2:G3 = accessories range

  1. Copy all data and past as values
  2. Data > Filter
  3. 5elect all instances of "-" in column 2 and delete

Solution #2 (courtesy of bugdrown):

  1. Copy/paste data into new worksheet and (if necessary) delete whatever few rows and columns whose data are irrelevant to the final product
  2. Data > Get & Transform Data > From Table/Range
  3. Select data range from new worksheet
  4. Right-click header for column 1 (containing the product SKUs) > Unpivot Other Columns
  5. (If necessary) Delete new column containing the old column titles
  6. Close & Load
  7. Data > Filter
  8. Select all instances of "-" in column 2 and delete
foolishgrunt
  • 117