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.