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

test23

New Member
Joined
Mar 9, 2023
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
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
ABCDEFGH
1YearWeekDayTimeStationProgrammeCommentaryPieces
2194626Monday, 01.07.194606.15–06.30Station01Programme01Comment01Piece01$Piece02$Piece03$Piece04$
3194626Monday, 01.07.194607.00–08.00Station01Programme02Piece05$Piece06$Piece07$Piece08$Piece09$Piece10$Piece11$
4194626Monday, 01.07.194608.30–09.00Station01Programme03Comment02Piece12$
5194626Monday, 01.07.194610.30–10.45Station01Programme04Comment03Piece13$Piece14$Piece15$Piece16$Piece17$
6194626Monday, 01.07.194611.00–11.45Station01Programme05Piece18$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
ABCDEFGH
1YearWeekDayTimeStationProgrammeCommentaryPieces
2194626Monday, 01.07.194606.15–06.30Station01Programme01Comment01Piece01
3194626Monday, 01.07.194606.15–06.30Station01Programme01Comment01Piece02
4194626Monday, 01.07.194606.15–06.30Station01Programme01Comment01Piece03
5194626Monday, 01.07.194606.15–06.30Station01Programme01Comment01Piece04
6194626Monday, 01.07.194607.00–08.00Station01Programme02Piece05
7194626Monday, 01.07.194607.00–08.00Station01Programme02Piece06
8194626Monday, 01.07.194607.00–08.00Station01Programme02Piece07
9194626Monday, 01.07.194607.00–08.00Station01Programme02Piece08
10194626Monday, 01.07.194607.00–08.00Station01Programme02Piece09
11194626Monday, 01.07.194607.00–08.00Station01Programme02Piece10
12194626Monday, 01.07.194607.00–08.00Station01Programme02Piece11
13194626Monday, 01.07.194608.30–09.00Station01Programme03Comment02Piece12
14194626Monday, 01.07.194610.30–10.45Station01Programme04Comment03Piece13
15194626Monday, 01.07.194610.30–10.45Station01Programme04Comment03Piece14
16194626Monday, 01.07.194610.30–10.45Station01Programme04Comment03Piece15
17194626Monday, 01.07.194610.30–10.45Station01Programme04Comment03Piece16
18194626Monday, 01.07.194610.30–10.45Station01Programme04Comment03Piece17
19194626Monday, 01.07.194611.00–11.45Station01Programme05Piece18
20194626Monday, 01.07.194611.00–11.45Station01Programme05Piece19
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!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Use Power Query to split the field into rows

Power Query:
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.
 
Upvote 0
Solution
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!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top