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 | | | ︙
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
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 | ︙
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?