Excel – Turn one row into multiple rows in Excel

microsoft excel

I have multiple items that are available in multiple distribution centers (i.e., a many-to-many relationship). There is currently one row per item, with one column for each distribution center. A cell in the row for item X and the column for distribution center Y is marked with the code for distribution center Y if item X is available there and blank otherwise. An item with multiple distribution centers will have multiple distribution center codes (in their respective columns). So the current sheet looks like:

    |   A    |         B         |*|                      S-AJ                      |
1   |  ID #  |   Description     |…|              Distribution Centers              |
2   |   17   |   Ginkgo Biloba   |…|      |      |      |      |      |      |  SE  |
3   |   42   |   Ginseng         |…|      |  MP  |  MS  |      |  NW  |      |      |
                     ︙

Columns C through R contain other attributes of the items, such as UPC code, cost, and price, which are not relevant to this question. My actual sheet has 18 distribution centers, spanning columns S through AJ; I reduced that to get the example to fit into Stack Exchange’s window.

I need to have a single distribution center column, with a single distribution code per row, and then duplicate the rows as needed for items that currently contain multiple codes. The result should look like:

    |   A    |         B         |*|   S  |
1   |  ID #  |   Description     |…|  DC  |
2   |   17   |   Ginkgo Biloba   |…|  SE  |
3   |   42   |   Ginseng         |…|  MP  |
4   |   42   |   Ginseng         |…|  MS  |
5   |   42   |   Ginseng         |…|  NW  |
                     ︙

where cells A3:R3, A4:R4, and A5:R5, contain the same information.

The only way I can think of doing this, which would be time consuming, would be to copy the item number into multiple rows; and in the column that has the distribution code I would change code for the item that is available in each distribution center. I will be doing this for 900 items. Is there an easier way to do this?

Best Answer

Create a new sheet. Copy the header row(s), the column widths, and the formats, except do not copy Columns T-AJ. It may be easiest to copy the entire sheet, then delete all rows other than 1 and unmerge Columns S-AJ.
First we want to replicate each item row from Sheet1 onto Sheet2 18 times — once for each distribution center. Type =INDEX(Sheet1!A:A, INT((ROW()-2)/18)+2, 1) & "" in Sheet2!A2. INT((ROW()-2)/18)+2 maps rows 2-19 on Sheet2 to row 2 on Sheet1, rows 20-37 on Sheet2 to row 3 on Sheet1, etc. The & "" causes Excel to display a blank when referencing a blank cell in Sheet1. If you don’t have any blanks in Sheet1, you can leave this off. If you don’t like this particular solution, you can use one of the other solutions from Display Blank when Referencing Blank Cell in Excel.

Drag/fill this to the right, to cell R2.
Enter =INDEX(Sheet1!$S:$AJ, INT((ROW()-2)/18)+2, MOD(ROW()-2, 18)+1) into Sheet2!S2. This references the same row in Sheet1 as the above formula, but Sheet2!S2 gets the value from Sheet1!S2, Sheet2!S3 gets the value from Sheet1!T2, Sheet2!S4 gets the value from Sheet1!U2, etc. This will display 0s for blanks.
Select the entire row A2:S2 and drag/fill down to get all your data. This will need to be 18 times as many rows as you have on Sheet1; i.e., 18×900=16200.
Copy all of Sheet2 and paste values.
Filter Column S. Display only the zeroes. Delete all the rows (other than row 1). Remove the filter.

Done.

STEP 1:

Name ranges for convenience. PARAMETERS is the list of parameters from A2 on down; LOCATIONS is the list of locations, from B1 across; DATA is the large square from B2 to the end. See my example:

STEP 2:

In another sheet, set up your new table. First column prints out all the locations, and it lists each location as many times as there are parameters:

That formula:

=INDEX(LOCATIONS,ROUNDUP((ROW()-1)/COUNTA(PARAMETERS),0))

That formula copies down.

STEP 3:

Second column prints out all the parameters, and it lists each parameter once until there are no more to list (note that this count corresponds to the count of how many times to list each Location in Step 2). Now you've got your entire list of every location/parameter combination, once each:

That formula:

=INDEX(PARAMETERS,MOD(ROW()-2,COUNTA(PARAMETERS))+1)

That formula copies down.

STEP 4:

From here, the way forward should be clear - we're now working with a simple INDEX MATCH to find the data at the intersection of the given location and parameter.

That formula:

=INDEX(DATA,MATCH(B2,PARAMETERS,0),MATCH(A2,LOCATIONS,0))

That formula copies down.

CONCLUSION:

With three formulas you've created your join table. Please consider selecting this answer so this question can be removed from the unanswered queue.

NOTES:

This works dynamically no matter how many columns/rows you have in your data (as long as you adjust the named ranges as needed, if you add more than the 365*768 records in this question's spec).
It doesn't do anything special with missing or empty data, though; you could easily wrap the final INDEX MATCH in Step 4 with an IF(ISBLANK()) to return something more useful than '0'.
This is NOT designed to skip those records, which adds a layer of complexity that's outside the scope of this question.

Best Answer

Related Solutions

Excel – How to combine values from multiple rows into a single row using module

Excel – Creating multiple rows from one row of Excel Data

STEP 1:

STEP 2:

STEP 3:

STEP 4:

CONCLUSION:

NOTES:

Related Question