CODEWITHSUNDEEP
excelvlookup
Alfred Bach
Alfred Bach

Reputation: 3

I need to pull a complete row from one excel sheet to another escel sheet, based on a cell value

I have 2 Excel worksheets. In the first I have a table that has a column named "Sales Order" and "SO Item" of each row (product) plus some other columns. In this table I concatenate "Sales Order" and "SO Item" so that I have Sales Order parent (xxxxxxx00) and also Sales Order childs (xxxxxxx01, xxxxxxx02,...,xxxxxxx09). However, in the second worksheet I also have the "concatenation" column but only contains Sales Order parents. How can I pull the whole rows containing the childs of each parent from worksheet 1 to worksheet 2?

I've tried to do it using VLOOKUP but this only returns a single child value (xxxxxxx001) and also its not returning the whole row where this code is located

Table 1 is:

Sales Order SO Item Concatenation Material Description Feas Plan Date
2503319449 100 2503319449100 SYS-7210 SAS-Mxp Bundle Header
2503319449 101 2503319449101 PS-7210 SAS-T/Mxp 1/31/2023
2503319449 102 2503319449102 SYS-7210 SAS-Mxp2VDC Global Allocation
2503319449 200 2503319449200 OS-7210 SAS-Mxp 1/31/2023

Table 2 is:

Sales Order SO Item Concatenation Material Description Feas Plan Date
2503319449 100 2503319449100 SYS-7210 SAS-Mxp Bundle Header
2503319449 200 2503319449200 OS-7210 SAS-Mxp 1/31/2023

I want Table 2 to extract the missing "Concatenation" items from Table 1.

Upvotes: 0

Views: 83

Answers (1)

David Leal
David Leal

Reputation: 6759

It is not clear from the question, how to present the output. I assume Table2 is your lookup table. Based on the input data, you need to return the entire Table1, I assume your Table1 has more data in your real case, and you want to extract just the information based on the lookup table. In the way you construct the concatenation, for the lookup it is only necessary the SO Item column values. Put on G2 the following formula:

=LET(tbA, A3:E4, tbB, A9:E12, soA, 1*INDEX(tbA,,2), soB, 1*INDEX(tbB,,2),
  DROP(REDUCE("", soA, LAMBDA(ac,x, LET(f,
  FILTER(tbB, (soB >= x) * (soB < x+100),""), IF(@f="", ac, VSTACK(ac,f))))),1))

Here is the output: excel output

The condition:

IF(@f="", ac, VSTACK(ac,f))

It is just to prevent empty result from the FILTER output (f), it is not really necessary if you want to include the parent (condition: soB >= x as it is in the formula), but if you want to exclude it (soB > x) then you need it. Check my answer to the question: how to transform a table in Excel from vertical to horizontal but with different length on how to use DROP/REDUCE/VSTACK pattern. I convert to numeric values (multiplying INDEX by 1) the value of SO Item column, in case the input data is in text format, otherwise it is not necesary.

Upvotes: 0

Related Questions