Transpose & Create New Rows

[SimonLock94]

Hello,

I'm very much new to the world of VBA but I have a transposing problem I need help on!

I'm working with a large excel file - columns A through to I give details on a company, these are all consistent throughout the 9,000 or so rows. Columns J onwards (up to a maximum of column EF) given the names of officers of those companies. Each company has a varying number of officers so the number of columns used per row is different.

I want to give each officer a row of their own and match it up with the company information. So essentially I want to transpose columns J onwards from horizontal to vertical, and create the same number of new rows to match the number of officers. As yet I can't figure out a way to do this other than manually.

This is a sample of the sheet I have:

And this is how I ultimately want it to look:

Any advice would be greatly appreciated here!

S

 

[JGordon11]

in Power Query (change 2 in Merge step to the number of columns you need to skip):

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Merge = Table.CombineColumns(Source,List.Skip(Table.ColumnNames(Source),2),Combiner.CombineTextByDelimiter(";;", QuoteStyle.None),"Officer"),
    OfficerList = Table.TransformColumns(Merge, {{"Officer", each List.RemoveItems(Text.Split(_,";;"),{""})}}),
    Result = Table.ExpandListColumn(OfficerList, "Officer")
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Company	Revenues	Officer1	Officer2	Officer3	Officer4		Company	Revenues	Officer
2	A	$ 76,336,583	Adam Ant					A	$ 76,336,583	Adam Ant
3	B	$ 51,186,087	Bruce Banner					B	$ 51,186,087	Bruce Banner
4	C	$ 1,834,724	Charlie Chaplin					C	$ 1,834,724	Charlie Chaplin
5	D	$ 77,305,520	Delta Dawn					D	$ 77,305,520	Delta Dawn
6	E	$ 70,855,470	Emilio Estevez	Nick Nolte				E	$ 70,855,470	Emilio Estevez
7	F	$ 14,584,070	Fannie Farmer	Ozzy Osbourne				E	$ 70,855,470	Nick Nolte
8	G	$ 24,165,454	Galileo Galilei	Pol Pot				F	$ 14,584,070	Fannie Farmer
9	H	$ 52,189,470	Harry Houdini	Quintavius Quire				F	$ 14,584,070	Ozzy Osbourne
10	I	$ 68,743,148	Igor Ivanov	Ryan Reynolds				G	$ 24,165,454	Galileo Galilei
11	J	$ 88,684,021	Jonah Jameson	Sue Storm				G	$ 24,165,454	Pol Pot
12	K	$ 11,299,468	Kris Kristofferson	Tina Turner	Walt Whitman			H	$ 52,189,470	Harry Houdini
13	L	$ 31,543,586	Lois Lane	Uncle Ugo	Xavier Xman			H	$ 52,189,470	Quintavius Quire
14	M	$ 39,722,173	Micky Mantle	Victor VonDoom	Yvette Young	Zig Ziglar		I	$ 68,743,148	Igor Ivanov
15								I	$ 68,743,148	Ryan Reynolds
16								J	$ 88,684,021	Jonah Jameson
17								J	$ 88,684,021	Sue Storm
18								K	$ 11,299,468	Kris Kristofferson
19								K	$ 11,299,468	Tina Turner
20								K	$ 11,299,468	Walt Whitman
21								L	$ 31,543,586	Lois Lane
22								L	$ 31,543,586	Uncle Ugo
23								L	$ 31,543,586	Xavier Xman
24								M	$ 39,722,173	Micky Mantle
25								M	$ 39,722,173	Victor VonDoom
26								M	$ 39,722,173	Yvette Young
27								M	$ 39,722,173	Zig Ziglar
28
Sheet2