Create a new row for each cell containing a value

Question

I have a table similar to this:

ID | Item 1 | Item 2 | Item 3 | Qty 1 | Qty 2 | Qty 3
1  |        |    X   |    X   |       |   3   |   4 
2  |    X   |        |    X   |   2   |       |   1
3  |        |        |    X   |       |       |   9

I need to find a way to create a new row for each item per ID:

ID | Item | Qty
1  |   2  |  3
1  |   3  |  4
2  |   1  |  2

Is there any way to accomplish this either through formula, native Excel tools, or VBA?

Answer 1

Use the technique described in detail here to get a Table that looks like this:

Delete where ColumnC is (Blanks) or X. Select ColumnB and HOME > Editing - Find & Select, Replace..., Find what: Qty, Replace All. Adjust column labels and center all. You should then have a Table like so:

Which you could choose to convert to Rage by right-clicking on one of its cells, Table, Convert to Range.

Answer 2

You could use Microsoft Query to point to your data sheet. Select your data and then give it a named range by typing a name in the box to the left of the Formula bar at the top.

Save your workbook to a directory somewhere. Then to choose it as a data source:

• Click the data tab at the top.
• Select "From Other Sources" button.
• Select "From Microsoft Query" from the menu.
• Choose the "Excel Files" data source.
• Find and select your data file that you saved the workbook as.
• If you get an error that no tables were found, click ok and then select "Options..." button at the bottom and make sure to check to show all types of tables.
• Select the table or sheet that your data is on and move all columns over to the right side box and then click next.
• click next again on the filter dialog box.
• click next again on the sort dialog box.
• Select View data or edit query in Microsoft Query and click Finish.
• Click the view menu in Microsoft Query and select "SQL..."
• Enter this SQL statement (and change the directory path to match where you saved your file:

    SELECT `Sheet1$`.ID, 
    `Sheet1$`.Qty1
    FROM `C:\Users\MyName\Desktop\data.xlsx`.`Sheet1$` `Sheet1$`
    WHERE (`Sheet1$`.Item1='x')
    union
    SELECT `Sheet1$`.ID, 
    `Sheet1$`.Qty2
    FROM `C:\Users\MyName\Desktop\data.xlsx`.`Sheet1$` `Sheet1$`
    WHERE (`Sheet1$`.Item2='x')
    union
    SELECT `Sheet1$`.ID, 
    `Sheet1$`.Qty3
    FROM `C:\Users\MyName\Desktop\data.xlsx`.`Sheet1$` `Sheet1$`
    WHERE (`Sheet1$`.Item3='x')

• Click Ok
• Select the file menu and then "Return Data to Microsoft Excel".
• Select the destination cell for your data and click OK.