lookup initial entry in multiple transfers

dorinb

New Member
Joined
Oct 10, 2019
Messages
6
Hi,

I have two columns with Entry no and TRansferred from entry no and would need to calculate a column Expected initial Entry no, that brings the initial entry no if an item has transferred ( it's differnt than zero).

It is easy to bring the value if we have one transfer for example entry 2 but I couldnt find a solution where you have multiple transfers. Eg. entry no 5, the initial entry no is 1.
Entry no
Transferred from entry no
Expected result initial entry
Comment how expected should work
1
0
1
Entry no is 1 and it's transferred from 0 then bring Entry no 1
2
1
1
Entry no 2 is coming from entry no 1, and entry no 1 is transferred from zero than bring 1
3
0
3
Entry no 3 is transferred from 0 then bring Entry no 3
4
2
1
Entry no 4 is transferred from 2, and 2 it's transferred from 1, and 1 is transferred from 0, then bring 1
5
4
1
Entry no 5 is transferred from 4, and 4 it's transferred from 2, 2 is transferred from 1 and 1 is transferred from 0, then bring 1
6
5
1
Entry no 6 is transferred from 5, and 5 is transferred from 4, 4 is transferred from 2, 2 from 1 and 1 from 0 then 1
7
0
7
Entry 7 is transferred from 0 then bring Entry no 7
8
4
1
Entry no 8 is transferred from 4, and 4 it's transferred from 2, 2 is transferred from 1 and 1 is transferred from 0, then bring 1
9
1
1
Entry no 9 is transferred from 1, 1 is tranferred from zero then bring 1

<tbody>
</tbody>

Any help would be much appreciated.
 
Dorin,
Horseyride posted his code just before I was about to post something very similar using the same recursive function approach. I have run my version and his for a test file of 50000 records both from a table in the workbook and also from a tab delimited external text file. For both sets of code the local table refreshes in about 30 seconds and the external text file 40 seconds. Does your source data have additional columns that you do not show in your example data?

Peter
 
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Depending on how many times the rows are looking up the same value, this might work faster since it does not repeat the tree search if above row was looking up same value

If sorting matters:


Code:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entry no", type text}, {"Transferred from entry no", type text}}),
    Entry_List=List.Buffer(#"Changed Type"[Entry no]),
    Transfer_List=List.Buffer(#"Changed Type"[Transferred from entry no]),

    Highest =  (n as text) as text=>
   let  Spot = List.PositionOf( Entry_List, n ),
        Transfer=Transfer_List{Spot}
        in if Transfer="0" then n else @Highest(Transfer),

    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Sorted Rows" = Table.Sort(#"Added Index",{{"Transferred from entry no", Order.Ascending}}),
    #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index2", 0, 1),

    Expected = Table.AddColumn( #"Added Index1", "Expected result initial entry", each if [Transferred from entry no] ="0" then [Entry no] else if #"Added Index1"{[Index2]}[Transferred from entry no] = #"Added Index1"{[Index2]-1}[Transferred from entry no] then null else  Highest( [Entry no]), type text),
    #"Filled Down" = Table.FillDown(Expected,{"Expected result initial entry"}),
    #"Sorted Rows1" = Table.Sort(#"Filled Down",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Index", "Index2"})
in
    #"Removed Columns"

If sorting does not matter, this is probably faster still than the one above

Code:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entry no", type text}, {"Transferred from entry no", type text}}),
    Entry_List=List.Buffer(#"Changed Type"[Entry no]),
    Transfer_List=List.Buffer(#"Changed Type"[Transferred from entry no]),

   Highest =  (n as text) as text=>
   let  Spot = List.PositionOf( Entry_List, n ),
        Transfer=Transfer_List{Spot}
        in if Transfer="0" then n else @Highest(Transfer),

    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Transferred from entry no", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),

    Expected = Table.AddColumn( #"Added Index", "Expected result initial entry", each if [Transferred from entry no] ="0" then [Entry no] else if #"Added Index"{[Index]}[Transferred from entry no] = #"Added Index"{[Index]-1}[Transferred from entry no] then null else Highest( [Entry no]), type text),
    #"Filled Down" = Table.FillDown(Expected,{"Expected result initial entry"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"})
in
    #"Removed Columns"
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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