0

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.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Alfred Bach
  • 3
  • 2
  • Please share sample data, better in Table Markdown format, you can use this [tool](https://www.tablesgenerator.com/markdown_tables) and also provide the expected output. Thanks – David Leal Feb 03 '23 at 19:23
  • What do you mean by this: ”I want Table 2 to extract the missing "Concatenation" items from Table 1”. I though Table 2 is the input table, then the result will be in other place, not in the Table 2 itself. Please provide the expected output for your sample data. Thanks. – David Leal Feb 04 '23 at 15:04

1 Answers1

0

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.

David Leal
  • 6,373
  • 4
  • 29
  • 56