Redesign excel format

[MiniFav]

I have the following spreadsheet with about 2000 rows,

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Ref No(s)			Title				Eff Date		ATA	Comments
2	TEST 1			B737 ADVERTISING PLACARD INSTALLATION						25	Referenced By: TEST
3		Paragraph			Method Of Compliance	R	Life		Last Compl		E/D O/Ride	Limit/ Interval	F/ L	Next Due	Remain
4		Initial Issue, Installation	B737 ADVERTISING PLACARD INSTALLATION		TEST	N	Date Days (Calendar) Hours Landings Works Order		28Aug2013 5287 36714:16 25270 055072				First	Completed
5		Initial Issue, Removal	B737 ADVERTISING PLACARD REMOVAL		TEST	N	Date Days (Calendar) Hours Landings Works Order		02Nov2013 5352 37283:01 25591 057123				First	Completed
6	Ref No(s)			Title				Eff Date		ATA	Comments
7	TEST 2			Portable Equipment Device Fire Kit (PEDFK) installation in Cabin Compartment						25-20	References: TEST
8		Paragraph			Method Of Compliance	R	Life		Last Compl		E/D O/Ride	Limit/ Interval	F/ L	Next Due	Remain
9		Initial Issue	Paragraph Not Defined		TEST	N	Date Days (Calendar) Hours Landings Works Order		27Oct2018 7173 49948:51 33402 110028			31Oct2018	First	Completed
10	Ref No(s)			Title				Eff Date		ATA	Comments
11	TEST 3			Introduction of TEST DATA				04Aug2004		31	Referenced By: TEST DATA
12		Paragraph			Method Of Compliance	R	Life		Last Compl		E/D O/Ride	Limit/ Interval	F/ L	Next Due	Remain
13		Issue 1	Micro QAR Installation		TEST	N	Date Days (Calendar) Hours Landings		25Oct2004 2058 12569:00 10497				First	Completed
14		Issue 2	Micro QAR Installation				N/A - Other reason Issue 1 was performed
15		Issue 3	Micro QAR Installation				N/A - Other reason Issue 1 was performed
16		Issue 4	Micro QAR Installation				N/A - Other reason Issue 1 was performed
17		Issue 5	Micro QAR Installation				N/A - Other reason Issue 1 was performed
18		Issue 6	Micro QAR Installation				N/A - Other reason Issue 1 was performed
19		Issue 7	Micro QAR Installation				N/A - Other reason Issue 1 was performed
20		Issue 8, Installation	Micro QAR Installation				N/A - Other reason Issue 1 was performed
21		Issue 8, Removal	Micro QAR Removal		TEST	N	Date Days (Calendar) Hours Landings						First
Sheet1

And would like to make it one line per item like this,

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	REF No(s)	Title	Eff Date	ATA	Comments	Paragraph			Method Of Compliance	R	Life		Last Compl		E/D O/Ride	Limit/ Interval	F/ L	Next Due	Remain
2	TEST 1	B737 ADVERTISING PLACARD INSTALLATION		25	Referenced By: TEST	Initial Issue, Installation	B737 ADVERTISING PLACARD INSTALLATION		TEST	N	Date Days (Calendar) Hours Landings Works Order		28Aug2013 5287 36714:16 25270 055072				First	Completed
3	TEST 1	B737 ADVERTISING PLACARD INSTALLATION		25	Referenced By: TEST	Initial Issue, Removal	B737 ADVERTISING PLACARD REMOVAL		TEST	N	Date Days (Calendar) Hours Landings Works Order		02Nov2013 5352 37283:01 25591 057123				First	Completed
4	REF No(s)	Title	Eff Date	ATA	Comments	Paragraph			Method Of Compliance	R	Life		Last Compl		E/D O/Ride	Limit/ Interval	F/ L	Next Due	Remain
5	Test 2	Portable Equipment Device Fire Kit (PEDFK) installation in Cabin Compartment		25-20	References: TEST	Initial Issue	Paragraph Not Defined		TEST	N	Date Days (Calendar) Hours Landings Works Order		27Oct2018 7173 49948:51 33402 110028			31Oct2018	First	Completed
6	REF No(s)	Title	Eff Date	ATA	Comments	Paragraph			Method Of Compliance	R	Life		Last Compl		E/D O/Ride	Limit/ Interval	F/ L	Next Due	Remain
7	Test 3	Introduction of TEST DATA	04Aug2004	31	Referenced By: TEST DATA	Issue 1	Micro QAR Installation		TEST	N	Date Days (Calendar) Hours Landings		25Oct2004 2058 12569:00 10497				First	Completed
8	Test 3	Introduction of TEST DATA	04Aug2004	31	Referenced By: TEST DATA	Issue 2	Micro QAR Installation				N/A - Other reason Issue 1 was performed
9	Test 3	Introduction of TEST DATA	04Aug2004	31	Referenced By: TEST DATA	Issue 3	Micro QAR Installation				N/A - Other reason Issue 1 was performed
10	Test 3	Introduction of TEST DATA	04Aug2004	31	Referenced By: TEST DATA	Issue 4	Micro QAR Installation				N/A - Other reason Issue 1 was performed
11	Test 3	Introduction of TEST DATA	04Aug2004	31	Referenced By: TEST DATA	Issue 5	Micro QAR Installation				N/A - Other reason Issue 1 was performed
12	Test 3	Introduction of TEST DATA	04Aug2004	31	Referenced By: TEST DATA	Issue 6	Micro QAR Installation				N/A - Other reason Issue 1 was performed
13	Test 3	Introduction of TEST DATA	04Aug2004	31	Referenced By: TEST DATA	Issue 7	Micro QAR Installation				N/A - Other reason Issue 1 was performed
14	Test 3	Introduction of TEST DATA	04Aug2004	31	Referenced By: TEST DATA	Issue 8, Installation	Micro QAR Installation				N/A - Other reason Issue 1 was performed
15	Test 3	Introduction of TEST DATA	04Aug2004	31	Referenced By: TEST DATA	Issue 8, Removal	Micro QAR Removal		TEST	N	Date Days (Calendar) Hours Landings						First
Sheet1

Does anyone know a quick way to do this?

 

[KRice]

I would suggest Power Query to unpivot your table.

 

[KRice]

Something like this script in Power Query produces an output very close to your example. You may want to revise the table structure to avoid the extended/merged cells (e.g., columns K:S) in some of the groups. This example assumes your starting table is "Table1"

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,null,"",Replacer.ReplaceValue,{"Column1", "Column2"}),
    #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 1, 1, Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "IdxUpperBlock", each if Text.Contains([Column1], "Ref No") then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"IdxUpperBlock"}),
    #"Added Conditional Column1" = Table.AddColumn(#"Filled Down", "IdxLowerBlock", each if Text.Contains([Column2], "Paragraph") then [IdxUpperBlock] else null),
    #"Filled Down1" = Table.FillDown(#"Added Conditional Column1",{"IdxLowerBlock"}),
    StagedTable = Table.AddColumn(#"Filled Down1", "DeleteRow", each if [IdxUpperBlock] = [IdxLowerBlock] then "delete" else null),
    #"Filtered Rows" = Table.SelectRows(StagedTable, each ([DeleteRow] = null)),
    #"Added Conditional Column2" = Table.AddColumn(#"Filtered Rows", "UpperHds", each if Text.Contains([Column1], "Ref No") then 0 else [IdxUpperBlock]),
    Upper = Table.SelectColumns(#"Added Conditional Column2",{"Column1", "Column4", "Column8", "Column10", "Column11", "UpperHds"}),
    BeginPreppingLower = Table.SelectRows(StagedTable, each ([DeleteRow] = "delete")),
    #"Removed Columns2" = Table.RemoveColumns(BeginPreppingLower,{"Column1"}),
    #"Added Conditional Column3" = Table.AddColumn(#"Removed Columns2", "IdLowerHds", each if [Column2] = "Paragraph" then 0 else [IdxUpperBlock]),
    Lower = Table.Group(#"Added Conditional Column3", {"IdLowerHds"}, {{"LowerGroups", each _, type table [Column2=text, Column3=nullable text, Column4=any, Column5=nullable text, Column6=nullable text, Column7=text, Column8=any, Column9=nullable text, Column10=any, Column11=nullable text, Column12=any, Column13=nullable text, Column14=nullable text, Column15=nullable text, Index=number, IdxUpperBlock=number, IdxLowerBlock=number, DeleteRow=text, IdLowerHds=number]}}),
    #"Merged Queries" = Table.NestedJoin(Upper, {"UpperHds"}, Lower, {"IdLowerHds"}, "Lower", JoinKind.LeftOuter),
    #"Expanded Lower" = Table.ExpandTableColumn(#"Merged Queries", "Lower", {"IdLowerHds", "LowerGroups"}, {"IdLowerHds", "LowerGroups"}),
    #"Expanded LowerGroups" = Table.ExpandTableColumn(#"Expanded Lower", "LowerGroups", {"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Index", "IdxUpperBlock", "IdxLowerBlock", "DeleteRow", "IdLowerHds"}, {"Column2", "Column3", "Column4.1", "Column5", "Column6", "Column7", "Column8.1", "Column9", "Column10.1", "Column11.1", "Column12", "Column13", "Column14", "Column15", "Index", "IdxUpperBlock", "IdxLowerBlock", "DeleteRow", "IdLowerHds.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded LowerGroups",{"Column1", "Column4", "Column8", "Column10", "Column11", "Column2", "Column3", "Column4.1", "Column5", "Column6", "Column7", "Column8.1", "Column9", "Column10.1", "Column11.1", "Column12", "Column13", "Column14", "Column15", "Index", "IdxUpperBlock", "IdxLowerBlock", "DeleteRow", "IdLowerHds.1", "UpperHds", "IdLowerHds"}),
    #"Removed Duplicates" = Table.Distinct(#"Reordered Columns", {"Index"}),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index", "IdxUpperBlock", "IdxLowerBlock", "DeleteRow", "IdLowerHds.1", "UpperHds", "IdLowerHds"})
in
    #"Removed Columns"

Column2	Column3	Column4.1	Column5	Column6	Column7	Column8.1	Column9	Column10.1	Column11.1	Column12	Column13	Column14	Column15
Paragraph			Method Of Compliance	R	Life		Last Compl		E/D O/Ride	Limit/ Interval	F/ L	Next Due	Remain
Initial Issue, Installation	B737 ADVERTISING PLACARD INSTALLATION		TEST	N	Date Days (Calendar) Hours Landings Works Order		28Aug2013 5287 36714:16 25270 055072				First	Completed
Initial Issue, Removal	B737 ADVERTISING PLACARD REMOVAL		TEST	N	Date Days (Calendar) Hours Landings Works Order		02Nov2013 5352 37283:01 25591 057123				First	Completed
Paragraph			Method Of Compliance	R	Life		Last Compl		E/D O/Ride	Limit/ Interval	F/ L	Next Due	Remain
Initial Issue	Paragraph Not Defined		TEST	N	Date Days (Calendar) Hours Landings Works Order		27Oct2018 7173 49948:51 33402 110028			43404	First	Completed
Paragraph			Method Of Compliance	R	Life		Last Compl		E/D O/Ride	Limit/ Interval	F/ L	Next Due	Remain
Issue 1	Micro QAR Installation		TEST	N	Date Days (Calendar) Hours Landings		25Oct2004 2058 12569:00 10497				First	Completed
Issue 2	Micro QAR Installation				N/A - Other reason Issue 1 was performed
Issue 3	Micro QAR Installation				N/A - Other reason Issue 1 was performed
Issue 4	Micro QAR Installation				N/A - Other reason Issue 1 was performed
Issue 5	Micro QAR Installation				N/A - Other reason Issue 1 was performed
Issue 6	Micro QAR Installation				N/A - Other reason Issue 1 was performed
Issue 7	Micro QAR Installation				N/A - Other reason Issue 1 was performed
Issue 8, Installation	Micro QAR Installation				N/A - Other reason Issue 1 was performed
Issue 8, Removal	Micro QAR Removal		TEST	N	Date Days (Calendar) Hours Landings						First

A sample of the workbook is available here...the starting point is Table1 on Sheet1 and the output table from Power Query is loaded onto the Table1 worksheet (when executed with Data > Refresh All):

MrExcel20220326.xlsx

Shared with Dropbox

www.dropbox.com

 

[MiniFav]

That is fantastic, All of what you have used is new to me but very simple to use with your instruction.

 

[KRice]

I'm glad to hear that and happy to help.