Convert the rows to Columns

aliikhlaq2006 · Aug 4, 2018

Posting Date	Document	G/L Acct/BP Name	Inventory UoM	Qty
FG00017		500 Litre Water Tank White
02.08.18	SI 2109	Finished Goods	Pcs	14
02.08.18	SI 2109	Finished Goods	Pcs	13
02.08.18	SI 2111	Finished Goods	Pcs	11
02.08.18	SI 2111	Finished Goods	Pcs	12

FG00018		750 Litre Water Tank White
02.08.18	SI 2109	Finished Goods	Pcs	14
02.08.18	SI 2109	Finished Goods	Pcs	15
02.08.18	SI 2111	Finished Goods	Pcs	16
02.08.18	SI 2111	Finished Goods	Pcs	12

FG00019		1000 Litre Water Tank White
02.08.18	SI 2109	Finished Goods	Pcs	6
02.08.18	SI 2109	Finished Goods	Pcs	8
02.08.18	SI 2111	Finished Goods	Pcs	10
02.08.18	SI 2111	Finished Goods	Pcs	6

I want the above result to be displayed by this

Posting Date	Item Name	Document	G/L Acct/BP Name	Inventory UoM	Qty
02.08.18	500 Litre Water Tank White	SI 2109	Finished Goods	Pcs	14
02.08.18	500 Litre Water Tank White	SI 2109	Finished Goods	Pcs	13
02.08.18	500 Litre Water Tank White	SI 2111	Finished Goods	Pcs	11
02.08.18	500 Litre Water Tank White	SI 2111	Finished Goods	Pcs	12
02.08.18	750 Litre Water Tank White	SI 2109	Finished Goods	Pcs	14
02.08.18	750 Litre Water Tank White	SI 2109	Finished Goods	Pcs	15
02.08.18	750 Litre Water Tank White	SI 2111	Finished Goods	Pcs	16
02.08.18	750 Litre Water Tank White	SI 2111	Finished Goods	Pcs	12
02.08.18	1000 Litre Water Tank White	SI 2109	Finished Goods	Pcs	6
02.08.18	1000 Litre Water Tank White	SI 2109	Finished Goods	Pcs	8
02.08.18	1000 Litre Water Tank White	SI 2111	Finished Goods	Pcs	10
02.08.18	1000 Litre Water Tank White	SI 2111	Finished Goods	Pcs	6

oldbrewer · Aug 4, 2018

Posting Date	Document	G/L Acct/BP Name	Inventory UoM	Qty
FG00017		500 Litre Water Tank White
02.08.18	SI 2109	Finished Goods	Pcs	14
02.08.18	SI 2109	Finished Goods	Pcs	13
02.08.18	SI 2111	Finished Goods	Pcs	11
02.08.18	SI 2111	Finished Goods	Pcs	12
FG00018		750 Litre Water Tank White
02.08.18	SI 2109	Finished Goods	Pcs	14
02.08.18	SI 2109	Finished Goods	Pcs	15
02.08.18	SI 2111	Finished Goods	Pcs	16
02.08.18	SI 2111	Finished Goods	Pcs	12
FG00019		1000 Litre Water Tank White
02.08.18	SI 2109	Finished Goods	Pcs	6
02.08.18	SI 2109	Finished Goods	Pcs	8
02.08.18	SI 2111	Finished Goods	Pcs	10
02.08.18	SI 2111	Finished Goods	Pcs	6



							FG00017	500 Litre Water Tank White
		very easy to lay out like this					FG00018	750 Litre Water Tank White
		item name comes from a lookup of code				FG00019	1000 Litre Water Tank White



CODE	Posting Date	Item Name	Document	G/L Acct/BP Name	Inventory UoM	Qty
FG00017	02.08.18	500 Litre Water Tank White	SI 2109	Finished Goods	Pcs	14
FG00017	02.08.18	500 Litre Water Tank White	SI 2109	Finished Goods	Pcs	13
FG00017	02.08.18	500 Litre Water Tank White	SI 2111	Finished Goods	Pcs	11
FG00017	02.08.18	500 Litre Water Tank White	SI 2111	Finished Goods	Pcs	12
FG00018	02.08.18	750 Litre Water Tank White	SI 2109	Finished Goods	Pcs	14
FG00018	02.08.18	750 Litre Water Tank White	SI 2109	Finished Goods	Pcs	15
FG00018	02.08.18	750 Litre Water Tank White	SI 2111	Finished Goods	Pcs	16
FG00018	02.08.18	750 Litre Water Tank White	SI 2111	Finished Goods	Pcs	12
FG00019	02.08.18	1000 Litre Water Tank White	SI 2109	Finished Goods	Pcs	6
FG00019	02.08.18	1000 Litre Water Tank White	SI 2109	Finished Goods	Pcs	8
FG00019	02.08.18	1000 Litre Water Tank White	SI 2111	Finished Goods	Pcs	10
FG00019	02.08.18	1000 Litre Water Tank White	SI 2111	Finished Goods	Pcs	6

aliikhlaq2006 · Aug 5, 2018

oldbrewer said:
Posting Date Document G/L Acct/BP Name Inventory UoM Qty
FG00017 500 Litre Water Tank White
02.08.18 SI 2109 Finished Goods Pcs 14
02.08.18 SI 2109 Finished Goods Pcs 13
02.08.18 SI 2111 Finished Goods Pcs 11
02.08.18 SI 2111 Finished Goods Pcs 12
FG00018 750 Litre Water Tank White
02.08.18 SI 2109 Finished Goods Pcs 14
02.08.18 SI 2109 Finished Goods Pcs 15
02.08.18 SI 2111 Finished Goods Pcs 16
02.08.18 SI 2111 Finished Goods Pcs 12
FG00019 1000 Litre Water Tank White
02.08.18 SI 2109 Finished Goods Pcs 6
02.08.18 SI 2109 Finished Goods Pcs 8
02.08.18 SI 2111 Finished Goods Pcs 10
02.08.18 SI 2111 Finished Goods Pcs 6
FG00017 500 Litre Water Tank White
very easy to lay out like this FG00018 750 Litre Water Tank White
item name comes from a lookup of code FG00019 1000 Litre Water Tank White
CODE Posting Date Item Name Document G/L Acct/BP Name Inventory UoM Qty
FG00017 02.08.18 500 Litre Water Tank White SI 2109 Finished Goods Pcs 14
FG00017 02.08.18 500 Litre Water Tank White SI 2109 Finished Goods Pcs 13
FG00017 02.08.18 500 Litre Water Tank White SI 2111 Finished Goods Pcs 11
FG00017 02.08.18 500 Litre Water Tank White SI 2111 Finished Goods Pcs 12
FG00018 02.08.18 750 Litre Water Tank White SI 2109 Finished Goods Pcs 14
FG00018 02.08.18 750 Litre Water Tank White SI 2109 Finished Goods Pcs 15
FG00018 02.08.18 750 Litre Water Tank White SI 2111 Finished Goods Pcs 16
FG00018 02.08.18 750 Litre Water Tank White SI 2111 Finished Goods Pcs 12
FG00019 02.08.18 1000 Litre Water Tank White SI 2109 Finished Goods Pcs 6
FG00019 02.08.18 1000 Litre Water Tank White SI 2109 Finished Goods Pcs 8
FG00019 02.08.18 1000 Litre Water Tank White SI 2111 Finished Goods Pcs 10
FG00019 02.08.18 1000 Litre Water Tank White SI 2111 Finished Goods Pcs 6

<tbody>
</tbody>

How can you tell me.

sandy666 · Aug 5, 2018

If you are allowed to use PowerQuery (PC Excel: 2010/2013 add-in, 2016 and higher - built-in) you can try this

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Posting Date", type text}, {"Document", type text}, {"G/L Acct/BP Name", type text}, {"Inventory UoM", type text}, {"Qty", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Code", each if Text.Contains([Posting Date], "FG") then [Posting Date] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Item", each if Text.Contains([#"G/L Acct/BP Name"], "Water") then [#"G/L Acct/BP Name"] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column1", each ([Posting Date] <> null)),
    #"Filled Down" = Table.FillDown(#"Filtered Rows",{"Code", "Item"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each ([Qty] <> null)),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Posting Date", "Code", "Item", "Document", "G/L Acct/BP Name", "Inventory UoM", "Qty"})
in
    #"Reordered Columns"

Posting Date	Code	Item	Document	G/L Acct/BP Name	Inventory UoM	Qty
02.08.18	FG00017	500 Litre Water Tank White	SI 2109	Finished Goods	Pcs	14
02.08.18	FG00017	500 Litre Water Tank White	SI 2109	Finished Goods	Pcs	13
02.08.18	FG00017	500 Litre Water Tank White	SI 2111	Finished Goods	Pcs	11
02.08.18	FG00017	500 Litre Water Tank White	SI 2111	Finished Goods	Pcs	12
02.08.18	FG00018	750 Litre Water Tank White	SI 2109	Finished Goods	Pcs	14
02.08.18	FG00018	750 Litre Water Tank White	SI 2109	Finished Goods	Pcs	15
02.08.18	FG00018	750 Litre Water Tank White	SI 2111	Finished Goods	Pcs	16
02.08.18	FG00018	750 Litre Water Tank White	SI 2111	Finished Goods	Pcs	12
02.08.18	FG00019	1000 Litre Water Tank White	SI 2109	Finished Goods	Pcs	6
02.08.18	FG00019	1000 Litre Water Tank White	SI 2109	Finished Goods	Pcs	8
02.08.18	FG00019	1000 Litre Water Tank White	SI 2111	Finished Goods	Pcs	10
02.08.18	FG00019	1000 Litre Water Tank White	SI 2111	Finished Goods	Pcs	6

aliikhlaq2006 · Aug 7, 2018

sandy666 said:
If you are allowed to use PowerQuery (PC Excel: 2010/2013 add-in, 2016 and higher - built-in) you can try this

Code:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Posting Date", type text}, {"Document", type text}, {"G/L Acct/BP Name", type text}, {"Inventory UoM", type text}, {"Qty", Int64.Type}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Code", each if Text.Contains([Posting Date], "FG") then [Posting Date] else null), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Item", each if Text.Contains([#"G/L Acct/BP Name"], "Water") then [#"G/L Acct/BP Name"] else null), #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column1", each ([Posting Date] <> null)), #"Filled Down" = Table.FillDown(#"Filtered Rows",{"Code", "Item"}), #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each ([Qty] <> null)), #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Posting Date", "Code", "Item", "Document", "G/L Acct/BP Name", "Inventory UoM", "Qty"}) in #"Reordered Columns"

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Posting Date[/COLOR] [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Code[/COLOR] [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Item[/COLOR] [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Document[/COLOR] [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G/L Acct/BP Name[/COLOR] [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Inventory UoM[/COLOR] [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Qty[/COLOR]
02.08.18 FG00017 500 Litre Water Tank White SI 2109 Finished Goods Pcs
14
02.08.18 FG00017 500 Litre Water Tank White SI 2109 Finished Goods Pcs
13
02.08.18 FG00017 500 Litre Water Tank White SI 2111 Finished Goods Pcs
11
02.08.18 FG00017 500 Litre Water Tank White SI 2111 Finished Goods Pcs
12
02.08.18 FG00018 750 Litre Water Tank White SI 2109 Finished Goods Pcs
14
02.08.18 FG00018 750 Litre Water Tank White SI 2109 Finished Goods Pcs
15
02.08.18 FG00018 750 Litre Water Tank White SI 2111 Finished Goods Pcs
16
02.08.18 FG00018 750 Litre Water Tank White SI 2111 Finished Goods Pcs
12
02.08.18 FG00019 1000 Litre Water Tank White SI 2109 Finished Goods Pcs
6
02.08.18 FG00019 1000 Litre Water Tank White SI 2109 Finished Goods Pcs
8
02.08.18 FG00019 1000 Litre Water Tank White SI 2111 Finished Goods Pcs
10
02.08.18 FG00019 1000 Litre Water Tank White SI 2111 Finished Goods Pcs
6

<tbody>
</tbody>

It worked for me Thank you SO much and i learn a new tool as well

sandy666 · Aug 7, 2018

You are welcome
Glad to help

Convert the rows to Columns

aliikhlaq2006

New Member

Excel Facts

oldbrewer

Well-known Member

aliikhlaq2006

New Member

sandy666

Banned - Rules violations

aliikhlaq2006

New Member

sandy666

Banned - Rules violations

Similar threads

Forum statistics

Share this page

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Posting Date[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Code[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Item[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Document[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G/L Acct/BP Name[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Inventory UoM[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Qty[/COLOR]
02.08.18	FG00017	500 Litre Water Tank White	SI 2109	Finished Goods	Pcs	14
02.08.18	FG00017	500 Litre Water Tank White	SI 2109	Finished Goods	Pcs	13
02.08.18	FG00017	500 Litre Water Tank White	SI 2111	Finished Goods	Pcs	11
02.08.18	FG00017	500 Litre Water Tank White	SI 2111	Finished Goods	Pcs	12
02.08.18	FG00018	750 Litre Water Tank White	SI 2109	Finished Goods	Pcs	14
02.08.18	FG00018	750 Litre Water Tank White	SI 2109	Finished Goods	Pcs	15
02.08.18	FG00018	750 Litre Water Tank White	SI 2111	Finished Goods	Pcs	16
02.08.18	FG00018	750 Litre Water Tank White	SI 2111	Finished Goods	Pcs	12
02.08.18	FG00019	1000 Litre Water Tank White	SI 2109	Finished Goods	Pcs	6
02.08.18	FG00019	1000 Litre Water Tank White	SI 2109	Finished Goods	Pcs	8
02.08.18	FG00019	1000 Litre Water Tank White	SI 2111	Finished Goods	Pcs	10
02.08.18	FG00019	1000 Litre Water Tank White	SI 2111	Finished Goods	Pcs	6

Convert the rows to Columns

aliikhlaq2006

New Member

Excel Facts

oldbrewer

Well-known Member

aliikhlaq2006

New Member

sandy666

Banned - Rules violations

aliikhlaq2006

New Member

sandy666

Banned - Rules violations

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock