Stacking multiple columns into one using an if statement - vba (excel)

Question

I need to to stack multiple columns into one using an if statement as the example below

Original table looks like the following:

Type	ID	Name	State	X	Y
Pay	01	Joe	NY	-5	0
Pay	02	Ann	FL	-2	-4
Receive	03	Lee	TX	1	0
Pay	04	Ken	CA	0	-1
Receive	05	John	NY	3	2

I would like to have the columns Type, ID, X and Y to be copied from sheet1 to sheet2 using the following conditions:

if Type = "Pay" and X <> 0 then copy columns "Type", "ID" and X * (-1)
if Type = "Pay" and Y <> 0 then copy columns "Type", "ID" and Y * (-1)
if Type = "Receive" and X <> 0 then copy columns "Type", "ID" and X
if Type = "Receive" and Y <> 0 then copy columns "Type", "ID" and Y

I would Like the final result to look like the following:

Type	ID	#
Pay	01	5	X
Pay	02	2	X
Receive	03	1	X
Receive	05	3	X
Pay	02	4	Y
Pay	04	1	Y
Receive	05	2	Y

Please help me

Thanks Phil

See 2nd answer [HERE](https://stackoverflow.com/questions/20541905/convert-matrix-to-3-column-table-reverse-pivot-unpivot-flatten-normal) Formula is not the best way to do this. It is possible with variations of INDEX, but PowerQuery can do this dynamically and faster. — Scott Craner, Aug 02 '21 at 17:30

Ron Rosenfeld · Accepted Answer · 2021-08-03T10:41:56.197

As @ScottCraner implied, you can obtain your desired output using Power Query, available in Windows Excel 2010+ and Office 365 Excel

Select some cell in your original table
Data => Get&Transform => From Table/Range
When the PQ UI opens, navigate to Home => Advanced Editor
Make note of the Table Name in Line 2 of the code.
Replace the existing code with the M-Code below
Change the table name in line 2 of the pasted code to your "real" table name
Examine any comments, and also the Applied Steps window, to better understand the algorithm and steps

Note that your "conditions", when applied to the unPivot table, are the same as

Filter out the zero values
Multiple the "Pay" values by -1

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table33"]}[Content],

//delete unneeded columns
    #"Removed Columns" = Table.RemoveColumns(Source,{"Name", "State"}),

//set data type
    typeIt = Table.TransformColumnTypes(#"Removed Columns",{
        {"Type", Text.Type},
        {"ID",   Text.Type},
        {"X",    Int64.Type},
        {"Y",    Int64.Type}
    }),

//unPivot, then remove the rows with zeros's
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(typeIt, {"Type", "ID"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> 0)),

//add column where Pay amount are multiplied by -1
//remove unneeded Value column
//Sort and reorder the columns
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "#", each if [Type]="Pay" then [Value] * -1 else [Value]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"Attribute", Order.Ascending}, {"ID", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Type", "ID", "#", "Attribute"})
in
    #"Reordered Columns"

Excellent answer! It worked perfectly for my needs. – Phil_Oliveira Aug 03 '21 at 18:10 — Phil_Oliveira, Aug 03 '21 at 18:10

Stacking multiple columns into one using an if statement - vba (excel)

1 Answers1