Find From Unique

[kme]

1.Sheet 1 Have Some Sample Customer Data . Diffrent Branches Customer Data are here. Need to Look & Fill Credit Days & Credit Amount Colum "G and H" Through Formula

2.Formula Required in "Column G and H"

3.Look up data in Sheet 1 "Customer Data"

4.Some accounts are common accounts. Since it is a common account, there is one account that can be used by all branches under a Company.

5.Since it's a single account, it's have a Single credit days limit.But Credit Days limit for each entry in the Customer Data Sheet. Single Days & Limit Required Common Account In Answer table Example "ABC CO LTD "in Answer Table

6.Single formula Required Both Columns G and H

 

[AlanSidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Company", each if [Invoice Amount]=null then [Voucher] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Company"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Balance", each if Text.Contains([Voucher],"Sub Total") then [Balance Amount] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"Balance"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Branch Code] <> null)),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Branch Code", "Account Credit Days", "Account Credit Limit", "Company"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Removed Duplicates",{"Customer Name", "Branch Code", "Account Credit Days", "Account Credit Limit", "Balance"})
in
    #"Removed Other Columns"

 

[kme]

Can I get a formula!!!!

 

[AlanSidman]

Be patient and maybe one of the formula gurus will pickup. BTW: What is wrong with using PQ?