Hello Friends
Based on the column A `Level number' 1 level lower part numbers to be plotted as shown in the light yellow column D as results by formulas.
Note :
* Level `0' part numbers will usually more than one and different part numbers, but here only one `0' part number available.
* In Light yellow cells results need to be produced for all cells but shown only for some cells for illustration purpose.
Thanks in advance
Sekar
Sekar
Please try
D2
=IF(A2>0,INDEX(B$1:B2,AGGREGATE(14,6,ROW(A$1:A2)/(1/(A$1:A2=A2-1)),1)),"--")
Regards.
My English is very poor, so please be patient >_<"
My Dynamic Related Dropdown post:
https://www.excelforum.com/excel-for...ml#post5657376
https://www.excelforum.com/excel-for...ml#post5655711
https://www.excelforum.com/excel-for...ml#post5655762
Power Query
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level Number", Int64.Type}, {"Part #", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Level Number]-1), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = -1 or [Custom] = 0 or [Custom] = 1)), #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Custom.1", each if [Custom] = -1 then "--" else [#"Part #"] ), #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Level Number", "Part #", "Custom"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "Part # of Level Number minus 1"}}) in #"Renamed Columns"
In D2 then copy down
=IFERROR(LOOKUP(2,1/($A$1:$A1=(A2-1)),$B$1:$B1),"")
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Thanks to all. This is helped me to save my manual work time.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks