Transposing cell info to new rows, retaining previous info each time

[test23]

Hello everyone!

I'm working on a fairly large spreadsheet and am trying to transpose various pieces of information from one cell into rows of their own. This is an abstraction from what I'm working with (it is more than 1000 rows long though):

Profile_Database.xlsx
	A	B	C	D	E	F	G	H
1	Year	Week	Day	Time	Station	Programme	Commentary	Pieces
2	1946	26	Monday, 01.07.1946	06.15–06.30	Station01	Programme01	Comment01	Piece01$Piece02$Piece03$Piece04$
3	1946	26	Monday, 01.07.1946	07.00–08.00	Station01	Programme02		Piece05$Piece06$Piece07$Piece08$Piece09$Piece10$Piece11$
4	1946	26	Monday, 01.07.1946	08.30–09.00	Station01	Programme03	Comment02	Piece12$
5	1946	26	Monday, 01.07.1946	10.30–10.45	Station01	Programme04	Comment03	Piece13$Piece14$Piece15$Piece16$Piece17$
6	1946	26	Monday, 01.07.1946	11.00–11.45	Station01	Programme05		Piece18$Piece19$
Radio S

I would like to divide the pieces from column H into different rows while retaining the information from column A–G every time. It should look something like this:

Profile_Database.xlsx
	A	B	C	D	E	F	G	H
1	Year	Week	Day	Time	Station	Programme	Commentary	Pieces
2	1946	26	Monday, 01.07.1946	06.15–06.30	Station01	Programme01	Comment01	Piece01
3	1946	26	Monday, 01.07.1946	06.15–06.30	Station01	Programme01	Comment01	Piece02
4	1946	26	Monday, 01.07.1946	06.15–06.30	Station01	Programme01	Comment01	Piece03
5	1946	26	Monday, 01.07.1946	06.15–06.30	Station01	Programme01	Comment01	Piece04
6	1946	26	Monday, 01.07.1946	07.00–08.00	Station01	Programme02		Piece05
7	1946	26	Monday, 01.07.1946	07.00–08.00	Station01	Programme02		Piece06
8	1946	26	Monday, 01.07.1946	07.00–08.00	Station01	Programme02		Piece07
9	1946	26	Monday, 01.07.1946	07.00–08.00	Station01	Programme02		Piece08
10	1946	26	Monday, 01.07.1946	07.00–08.00	Station01	Programme02		Piece09
11	1946	26	Monday, 01.07.1946	07.00–08.00	Station01	Programme02		Piece10
12	1946	26	Monday, 01.07.1946	07.00–08.00	Station01	Programme02		Piece11
13	1946	26	Monday, 01.07.1946	08.30–09.00	Station01	Programme03	Comment02	Piece12
14	1946	26	Monday, 01.07.1946	10.30–10.45	Station01	Programme04	Comment03	Piece13
15	1946	26	Monday, 01.07.1946	10.30–10.45	Station01	Programme04	Comment03	Piece14
16	1946	26	Monday, 01.07.1946	10.30–10.45	Station01	Programme04	Comment03	Piece15
17	1946	26	Monday, 01.07.1946	10.30–10.45	Station01	Programme04	Comment03	Piece16
18	1946	26	Monday, 01.07.1946	10.30–10.45	Station01	Programme04	Comment03	Piece17
19	1946	26	Monday, 01.07.1946	11.00–11.45	Station01	Programme05		Piece18
20	1946	26	Monday, 01.07.1946	11.00–11.45	Station01	Programme05		Piece19
Radio S

I've tried various solutions and have googled extensively, trying previous VBA-suggestions to requests similar to mine (e.g. this one or this, which didn't work or I wasn't able to adapt to my worksheet, I'm fairly new to VBA), to just winging it manually (which takes way to long; transposing data using the "columns to rows"-function doesn't create new rows and overwrites subsequent rows. I left the "$"-sign as divider in the table from that attempt). I'm using Excel 365 for Mac, v.16.70, running on OS Ventura 13.1. I would really appreciate any help you can give. Thank you so much in advance!

 

[alansidman]

Use Power Query to split the field into rows

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Pieces", Splitter.SplitTextByDelimiter("$", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Pieces")
in
    #"Split Column by Delimiter"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[test23]

Thanks a lot! I meddled around with Power Query some and was able to get the result I needed quickly, even without learning to code!