track where the balance is coming from

B

BORUCH

Well-known Member
Joined
Mar 1, 2016
Messages
548
Office Version
  1. 365
Platform
  1. Windows
hi all i have the below table
AM Balance Order Jul.08.24 (Part 3).xlsx
ABCDEFGHIJKLMNO
1ITEM #AM SNACKSORIGINAL BALANCE DATEORIGINAL BALANCEORDER DATE QTY ORDEREDBALANCERECEIVED DATEINVOICE #QTY RECEIVEDBALANCERECEIVED DATEINVOICE #QTY RECEIVEDBALANCE
200001apples9/6/2023811/2/2023758311/19/20239999107311/30/20231235023
300002oranges9/6/2023-3411/2/202350046611/19/202388884006611/30/2023456606
Sheet1
Cell Formulas
RangeFormula
G2:G3G2=D2+F2
K2:K3,O2:O3K2=G2-J2


i would like an excel formula on another sheet for each item to track the balance by date
 
Sort by date Sort by votes
I am guessing that the number of columns is variable ? It this correct ? Is there an upper limit to the number of columns ?
Is using VBA an option if I can't resolve it in PQ ?
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
The easiest is to convert the nulls to 0 in PQ for Column J before doing the unpivot BUT you have another received column N, do we convert all the nulls in that column to 0 as well.
Then since we already have 2 received columns will there be more ?
 
Upvote 0
Alex Blakenburg said:
The easiest is to convert the nulls to 0 in PQ for Column J before doing the unpivot BUT you have another received column N, do we convert all the nulls in that column to 0 as well.
Then since we already have 2 received columns will there be more ?
Click to expand...
there can be more columns i would prefer the power query option and yes the number of columns is variable and they can be zero in column n to
 
Upvote 0
It would solve a lot of issues if you populate the columns with 0 when you put in the Invoice Date and Invoice No. Is that practicable?
 
Upvote 0
OK See it this works for you.
The first line currently refers to Table1, so change that to your table name.
The change from the previous code is the addition of the 2nd and 3rd line.

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Qty_Recvd_Columns = List.Select(Table.ColumnNames(Source), each Text.Contains(_,"QTY RECEIVED",Comparer.OrdinalIgnoreCase)),
    Qty_NullToZero = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,Qty_Recvd_Columns),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Qty_NullToZero, {"ITEM #", "AM SNACKS"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 1, 1, Int64.Type),
    AddedCondColDate = Table.AddColumn(#"Added Index", "Date", each if Text.EndsWith([Attribute], "DATE") then [Value] else null),
    AddedCondColInvNo = Table.AddColumn(AddedCondColDate, "InvoiceNo", each if Text.StartsWith([Attribute], "INVOICE") then [Value] else null),
    AddedCondColBalance = Table.AddColumn(AddedCondColInvNo, "Balance", each if Text.Contains([Attribute], "BALANCE") and not Text.Contains([Attribute], "DATE") then [Value] else null),
    AddedCondColType = Table.AddColumn(AddedCondColBalance, "Type", each if Text.Contains([Attribute], "QTY") then [Attribute] else if [Attribute] = "ORIGINAL BALANCE" then [Attribute] else null),
    ReplacedValueQTY = Table.ReplaceValue(AddedCondColType,"QTY ","",Replacer.ReplaceText,{"Type"}),
    #"Split Column by Character Transition" = Table.SplitColumn(ReplacedValueQTY, "Type", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Type.1", "Type.2"}),
    RemovedColumnsTypeNo = Table.RemoveColumns(#"Split Column by Character Transition",{"Type.2"}),
    RenamedColumnsType = Table.RenameColumns(RemovedColumnsTypeNo,{{"Type.1", "Type"}}),
    AddedCondColQty = Table.AddColumn(RenamedColumnsType, "Qty", each if [Type] = "RECEIVED" then -1 * [Value] else if Text.StartsWith([Attribute], "QTY") then [Value] else null),
    #"Filled Down" = Table.FillDown(AddedCondColQty,{"Date", "InvoiceNo"}),
    FilledUpBalance = Table.FillUp(#"Filled Down",{"Balance"}),
    FilteredRowsNullType = Table.SelectRows(FilledUpBalance, each [Type] <> null and [Type] <> ""),
    #"Removed Columns" = Table.RemoveColumns(FilteredRowsNullType,{"Attribute", "Value", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ITEM #", "AM SNACKS", "Date", "InvoiceNo", "Type", "Qty", "Balance"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"ITEM #", Int64.Type}, {"AM SNACKS", type text},  {"Date", type date}, {"InvoiceNo", Int64.Type}, {"Qty", Int64.Type}, {"Type", type text}, {"Balance", Int64.Type}})
in
    #"Changed Type"
 
Last edited:
Upvote 0
Alex Blakenburg said:
OK See it this works for you.
The first line currently refers to Table1, so change that to your table name.
The change from the previous code is the addition of the 2nd and 3rd line.

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Qty_Recvd_Columns = List.Select(Table.ColumnNames(Source), each Text.Contains(_,"QTY RECEIVED",Comparer.OrdinalIgnoreCase)),
    Qty_NullToZero = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,Qty_Recvd_Columns),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Qty_NullToZero, {"ITEM #", "AM SNACKS"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 1, 1, Int64.Type),
    AddedCondColDate = Table.AddColumn(#"Added Index", "Date", each if Text.EndsWith([Attribute], "DATE") then [Value] else null),
    AddedCondColInvNo = Table.AddColumn(AddedCondColDate, "InvoiceNo", each if Text.StartsWith([Attribute], "INVOICE") then [Value] else null),
    AddedCondColBalance = Table.AddColumn(AddedCondColInvNo, "Balance", each if Text.Contains([Attribute], "BALANCE") and not Text.Contains([Attribute], "DATE") then [Value] else null),
    AddedCondColType = Table.AddColumn(AddedCondColBalance, "Type", each if Text.Contains([Attribute], "QTY") then [Attribute] else if [Attribute] = "ORIGINAL BALANCE" then [Attribute] else null),
    ReplacedValueQTY = Table.ReplaceValue(AddedCondColType,"QTY ","",Replacer.ReplaceText,{"Type"}),
    #"Split Column by Character Transition" = Table.SplitColumn(ReplacedValueQTY, "Type", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Type.1", "Type.2"}),
    RemovedColumnsTypeNo = Table.RemoveColumns(#"Split Column by Character Transition",{"Type.2"}),
    RenamedColumnsType = Table.RenameColumns(RemovedColumnsTypeNo,{{"Type.1", "Type"}}),
    AddedCondColQty = Table.AddColumn(RenamedColumnsType, "Qty", each if [Type] = "RECEIVED" then -1 * [Value] else if Text.StartsWith([Attribute], "QTY") then [Value] else null),
    #"Filled Down" = Table.FillDown(AddedCondColQty,{"Date", "InvoiceNo"}),
    FilledUpBalance = Table.FillUp(#"Filled Down",{"Balance"}),
    FilteredRowsNullType = Table.SelectRows(FilledUpBalance, each [Type] <> null and [Type] <> ""),
    #"Removed Columns" = Table.RemoveColumns(FilteredRowsNullType,{"Attribute", "Value", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ITEM #", "AM SNACKS", "Date", "InvoiceNo", "Type", "Qty", "Balance"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"ITEM #", Int64.Type}, {"AM SNACKS", type text},  {"Date", type date}, {"InvoiceNo", Int64.Type}, {"Qty", Int64.Type}, {"Type", type text}, {"Balance", Int64.Type}})
in
    #"Changed Type"
Click to expand...
hi
thanks this basically does it one problem is that the date column is always picking up the receiving date its suppose to show me the date corresponding to the type, Ordered is order date etc..
 
Upvote 0
so in the above example if i would to change the Receiving date in column L to 12/29/2024 it wouldn't pick it up in the table
 
Upvote 0
Sorry my Date logic used Ends With and needs to be Contains, try this:
Rich (BB code): 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Qty_Recvd_Columns = List.Select(Table.ColumnNames(Source), each Text.Contains(_,"QTY RECEIVED",Comparer.OrdinalIgnoreCase)),
    Qty_NullToZero = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,Qty_Recvd_Columns),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Qty_NullToZero, {"ITEM #", "AM SNACKS"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 1, 1, Int64.Type),
    AddedCondColDate = Table.AddColumn(#"Added Index", "Date", each if Text.Contains([Attribute], "DATE") then [Value] else null),
    AddedCondColInvNo = Table.AddColumn(AddedCondColDate, "InvoiceNo", each if Text.StartsWith([Attribute], "INVOICE") then [Value] else null),
    AddedCondColBalance = Table.AddColumn(AddedCondColInvNo, "Balance", each if Text.Contains([Attribute], "BALANCE") and not Text.Contains([Attribute], "DATE") then [Value] else null),
    AddedCondColType = Table.AddColumn(AddedCondColBalance, "Type", each if Text.Contains([Attribute], "QTY") then [Attribute] else if [Attribute] = "ORIGINAL BALANCE" then [Attribute] else null),
    ReplacedValueQTY = Table.ReplaceValue(AddedCondColType,"QTY ","",Replacer.ReplaceText,{"Type"}),
    #"Split Column by Character Transition" = Table.SplitColumn(ReplacedValueQTY, "Type", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Type.1", "Type.2"}),
    RemovedColumnsTypeNo = Table.RemoveColumns(#"Split Column by Character Transition",{"Type.2"}),
    RenamedColumnsType = Table.RenameColumns(RemovedColumnsTypeNo,{{"Type.1", "Type"}}),
    AddedCondColQty = Table.AddColumn(RenamedColumnsType, "Qty", each if [Type] = "RECEIVED" then -1 * [Value] else if Text.StartsWith([Attribute], "QTY") then [Value] else null),
    #"Filled Down" = Table.FillDown(AddedCondColQty,{"Date", "InvoiceNo"}),
    FilledUpBalance = Table.FillUp(#"Filled Down",{"Balance"}),
    FilteredRowsNullType = Table.SelectRows(FilledUpBalance, each [Type] <> null and [Type] <> ""),
    #"Removed Columns" = Table.RemoveColumns(FilteredRowsNullType,{"Attribute", "Value", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ITEM #", "AM SNACKS", "Date", "InvoiceNo", "Type", "Qty", "Balance"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"ITEM #", Int64.Type}, {"AM SNACKS", type text},  {"Date", type date}, {"InvoiceNo", Int64.Type}, {"Qty", Int64.Type}, {"Type", type text}, {"Balance", Int64.Type}})
in
    #"Changed Type"
 
Upvote 0
Alex Blakenburg said:
Sorry my Date logic used Ends With and needs to be Contains, try this:
Rich (BB code): 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Qty_Recvd_Columns = List.Select(Table.ColumnNames(Source), each Text.Contains(_,"QTY RECEIVED",Comparer.OrdinalIgnoreCase)),
    Qty_NullToZero = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,Qty_Recvd_Columns),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Qty_NullToZero, {"ITEM #", "AM SNACKS"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 1, 1, Int64.Type),
    AddedCondColDate = Table.AddColumn(#"Added Index", "Date", each if Text.Contains([Attribute], "DATE") then [Value] else null),
    AddedCondColInvNo = Table.AddColumn(AddedCondColDate, "InvoiceNo", each if Text.StartsWith([Attribute], "INVOICE") then [Value] else null),
    AddedCondColBalance = Table.AddColumn(AddedCondColInvNo, "Balance", each if Text.Contains([Attribute], "BALANCE") and not Text.Contains([Attribute], "DATE") then [Value] else null),
    AddedCondColType = Table.AddColumn(AddedCondColBalance, "Type", each if Text.Contains([Attribute], "QTY") then [Attribute] else if [Attribute] = "ORIGINAL BALANCE" then [Attribute] else null),
    ReplacedValueQTY = Table.ReplaceValue(AddedCondColType,"QTY ","",Replacer.ReplaceText,{"Type"}),
    #"Split Column by Character Transition" = Table.SplitColumn(ReplacedValueQTY, "Type", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Type.1", "Type.2"}),
    RemovedColumnsTypeNo = Table.RemoveColumns(#"Split Column by Character Transition",{"Type.2"}),
    RenamedColumnsType = Table.RenameColumns(RemovedColumnsTypeNo,{{"Type.1", "Type"}}),
    AddedCondColQty = Table.AddColumn(RenamedColumnsType, "Qty", each if [Type] = "RECEIVED" then -1 * [Value] else if Text.StartsWith([Attribute], "QTY") then [Value] else null),
    #"Filled Down" = Table.FillDown(AddedCondColQty,{"Date", "InvoiceNo"}),
    FilledUpBalance = Table.FillUp(#"Filled Down",{"Balance"}),
    FilteredRowsNullType = Table.SelectRows(FilledUpBalance, each [Type] <> null and [Type] <> ""),
    #"Removed Columns" = Table.RemoveColumns(FilteredRowsNullType,{"Attribute", "Value", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ITEM #", "AM SNACKS", "Date", "InvoiceNo", "Type", "Qty", "Balance"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"ITEM #", Int64.Type}, {"AM SNACKS", type text},  {"Date", type date}, {"InvoiceNo", Int64.Type}, {"Qty", Int64.Type}, {"Type", type text}, {"Balance", Int64.Type}})
in
    #"Changed Type"
Click to expand...
thanks so much this worked perfectly

i just copied the logic you did by date to invoice #
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
626
Peter_SSs
Peter_SSs
trishcollins
  • Question Question
Looking for formula/VBA to determine where to best put extra funds
Replies
1
Views
263
trishcollins
trishcollins
A
  • Solved
Create accounts by user form to show balance by calculation for two columns
Replies
4
Views
125
abdo meghari
A
M
  • Solved
Macro to populate amounts in list based on adjacent amount for last row and previous lastrow
Replies
6
Views
172
Mussala
M
A
  • Question Question
Loan Tracker with prior/missed payments
Replies
0
Views
112
aurelius33
A

Forum statistics

Threads
1,224,820
Messages
6,181,161
Members
453,021
Latest member
Justyna P

Share this page

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