Convert single row data with multiple columns into multiple-row data with only two columns

[basin_street]

I have a sheet with a list of song codes in the leftmost column, each row contains a unique song code. For each row there are 10 columns with names of songwriters/composers that correspond to each song code. The number of names per row varies; some rows have only 1 name, some have up to 10. I need the writer/composer names to all be in one column, with the same corresponding song codes. The rows with only one writer will remain unchanged. The rows with multiple writers will need to be duplicated so the song code appears the same number of times as there are writers of the song. ie. a song with three writers will have its song code repeated three times, each on a separate row, and with the three different songwriters on each row, as opposed to having them all in one row with the code only listed once.

Please see the attached sheet, as it will make what I am trying to accomplish much clearer. I have attached a sheet with two tabs—the first tab is the data as I currently have it, the second tab shows my desired result. In actuality I have many more lines to go through, so doing this manually is not an option. Any help with how to do this will be greatly appreciated.

[alansidman]

Using Power Query/Get and Transform found on the Data Tab, Highlihgt your table and place in PQ. Highlight the first column. Click on transform Tab, select Unpivot. Select Unpivot other Columns. Delete the middle column.

Here is the resulting Mcode from that action.

Please Login or Register  to view this content.

Excel 2016 (Windows) 32 bit

	A	B
1	Song Code	Value
2	USBAS0000001	Irvin Mayfield
3	USBAS0000008	Bill Summers
4	USBAS0000008	Victor Atkins
5	USBAS0000008	Manuel Lander
6	USBAS0100001	W.C. Handy
7	USBAS0100001	Walter Hirsch
8	USBAS0100002	Kermit Ruffins
9	USBAS0100003	Wilmore Jones
10	USBAS0100003	Fleecy Moor
11	USBAS0200045	Edwin B. Edwards
12	USBAS0200045	James D. La Rocca
13	USBAS0200045	Henry Ragas
14	USBAS0200045	Anthony Sbarbaro
15	USBAS0200045	Larry Shields
16	USBAS0200045	Carl Sigman
17	USBAS0200046	Dr. Michael White
18	USBAS0200049	Thurland Chattaway
19	USBAS0200049	F. A. Mills
20	USBAS0200060	Ray Manzarek
21	USBAS0200060	John Densmore
22	USBAS0200060	Robert Krieger
23	USBAS0200060	Jim Morrison
24	USBAS1400111	Paul Blaize
25	USBAS1400111	Rodney Williamson
26	USBAS1400111	Steven Workman
27	USBAS1400111	Phil Frazier
28	USBAS1400111	Keith Frazier
29	USBAS1400111	Derrick Tabb
30	USBAS1400111	Stafford Agee
31	USBAS1400111	Gregory Veals
32	USBAS1400111	Derrick Shezbie
33	USBAS1400111	Chadrick Honore
34	USBAS1400111	Vincent Broussard
35	USBAS1400416	Joseph Modeliste
36	USBAS1400416	Arthur Lanon Neville
37	USBAS1400416	Cyril Garrett Neville
38	USBAS1400416	Leo Nocentelli
39	USBAS1400416	George Joseph Porter Jr.
40	USBAS1400417	Harold Raymond Battiste, Jr.
41	USBAS1400418	Wayne Shorter
42	USBAS1400426	Kurt Weill
43	USBAS1400426	Eugen Berthold Brecht
44	USBAS1600204	John Lennon
45	USBAS1600204	Paul McCartney
46	USBAS1800106	George Gershwin
47	USBAS1800106	Ira Gershwin
48	USBAS1800106	DuBose Heyward
49	USBAS1800106	Dorothy Kuhns
50	USBAS1800107	Dr. Michael White
51	USBAS1800108	Luther Dixon
52	USBAS1800108	Al Smith

Sheet: Sheet1

Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps

M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced

[basin_street]

Hi Alan,

I am trying to do as you suggested, but am running in to issues, I think because I am on a Mac. How do I do this step?
"highlight your table and place in PQ"
When I click on the Data tab, I see buttons on the left that say "From Filemaker" "From HTML" "From Text" "New Database Query" the last one has a drop-down menu with two options: "SQL Server ODBC" and "From Database"
I have attached screenshots of what this looks like along with the Data Menu. Do you know the process to use Power Query / Get Transform on a Mac?
Attachment 684029
Attachment 684030

[Limor_OP]

Hi,
I created 2 helper columns
Column C copied from Column A, "Original Data" tab , and column D to provide the number of Writer/Composers thereof.

Please Login or Register  to view this content.

In F1 = C2

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please also see attached.

[Limor_OP]

**All formulas are Array type. To work correctly, it needs to be entered with control + shift + enter.

[alansidman]

Power Query is not an option for Mac users. It is only available to Excel Windows version 2010 and later.

[basin_street]

Thank you belinda200! Your solution worked! I had to make some minor changes to the formulas due to the different size of my actual sheet, but this accomplished what I was trying to achieve.

[Limor_OP]

Great to hear it helped you. You're welcome.