Excel mapping over two sheets using a concat

Question

I have the following two sheets in an Excel file:

Sheet "Missing_models": Column A contains the article number of a product. In the subsequent columns B to maximum H (can also only be only one entry, so only a value in B), there are designations for models to which the product belongs.

In the second sheet "Models", column A contains the id_m, column B contains the name, and column C contains the color. The combination of name and color, i.e., columns B and C, separated by a space, exactly matches the model designations in columns B to H in "Missing_models".

Now, in a new sheet, I want to have the article number in column A and the corresponding id_m in column B, where the mapping between columns B-H of "Missing_models" and the concatenation of columns B and C from "Models" should take place.

So for example in missing_models i have the following entry:

A	B	C
123	model green	model red
456	model blue

In models there are the entries:

A	B	C
1	model	green
2	model	blue
3	model	red

From that i would like to have a new sheet like this:

A(articelnumber)	B (id_m)
123	1
123	3
456	2

How can I achieve this?

Let's say product 1 has in its B-H columns from missing_models two models (cat_yellow and elephant_red) that are available with the concatenation of the models sheet columns. What would you expect to be the result in the new sheet? — CarlosMorente, Jun 15 '23 at 10:13
Please provide some sample data and expected output with that using markdown table https://www.tablesgenerator.com/markdown_tables — P.b, Jun 15 '23 at 10:22
Thanks for your advice. I edited the question to make it more clear. — Alex, Jun 15 '23 at 10:29

P.b · Answer 1 · 2023-06-15T13:44:40.783

This would do in Microsoft 365:

=LET(missing, missing_models!A1:C2,
     models,  FILTER(models!B:B&" "&models!C:C,models!A:A<>""),
     a,       DROP(REDUCE(0,SEQUENCE(ROWS(missing)),LAMBDA(x,y,LET(z,INDEX(missing,y,),
     d,       DROP(z,,1),VSTACK(x,TAKE(z,,1)&","&TOCOL(d,3))))),1),
HSTACK(TEXTBEFORE(a,","),
       INDEX('models'!A:A,XMATCH(TEXTAFTER(a,","),models))))

It first changes the missing_models data into a list of it's permutations where the article number and model are separated by delimiter ,.

Then it stacks the values in front of the ,-delimiter of aand the ID from column A of the models sheet, where the concatenation of column B and C from that sheet meet the value after the ,-delimiter of a.

Basically it looks up the row number of the missing models to the concatenation of column B & C in models and returns the value in column A from models. The complication is the way the missing models are stored, hence the permutations solution.

Further explanation on the permutations part can be found here

Instead of `missing, missing_models!A1:H2,` you might want to use `missing, FILTER(missing_models!A:H,missing_models!A:A<>""),` — P.b, Jun 16 '23 at 08:50

Excel mapping over two sheets using a concat

1 Answers1