4
1
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?
1Could you please add the sample? – Excellll – 2015-03-13T14:29:12.900
Yes, a sample of what the data looks like, and what you've attempted that isn't working. – FreeMan – 2015-03-13T15:05:43.813
In case this isn't obvious, we need an example of the data layout you have now *and* what you want to end up with. – Scott – 2015-03-13T19:58:57.640
Below is an small sample of what I want to do. I will be doing this for 900 items.
– Don – 2015-03-16T19:59:39.967Current sheet [1]: http://i.stack.imgur.com/FU0KS.jpg What I want to do. [2]: http://i.stack.imgur.com/s7uQP.jpg
3
possible duplicate of How do I split one row into multiple rows with Excel?
– Scott – 2015-03-17T15:39:06.670