No output (while expected)

[Yoeri]

Hello all,

I hope someone can help me with the following issue:

I have 2 tabs in Excel:

1. Product Line Attributes
2. Data Sheet

In tab 1 (Product Line Attributes) I have a list of product lines (1 column for the product line ID's and 1 for the product line names) with the attributes that are linked to it in the next column. These are all in Table1
In tab 2 (Data Sheet) I have a list of products in a column with behind it the product line ID and the product line name in separate columns (like in Table1) but here there is no table format used.

Below an example:

Tab 1 called "Product Line Attributes" with "Table1":



[TABLE="width: 0, align: left"]
<tbody>[TR]
[TD]Product Line ID[/TD]
[TD]Product Line name[/TD]
[TD]Attribute ID[/TD]
[/TR]
[TR]
[TD]970731[/TD]
[TD]Scrapers[/TD]
[TD]ned_application_lov[/TD]
[/TR]
[TR]
[TD]970731[/TD]
[TD]Scrapers[/TD]
[TD]type_lov[/TD]
[/TR]
[TR]
[TD]970731[/TD]
[TD]Scrapers[/TD]
[TD]content_lov[/TD]
[/TR]
</tbody>[/TABLE]







Tab 2 called "Data Sheet" where the following is shown:


[TABLE="width: 0, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column L[/TD]
[TD]Column M[/TD]
[TD]Column S[/TD]
[TD]Column V[/TD]
[TD]Column Y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Product no.[/TD]
[TD]Product Line ID[/TD]
[TD]Product Line Name[/TD]
[TD]Att 1[/TD]
[TD]Att 2[/TD]
[TD]Att 3[/TD]
[/TR]
[TR]
[TD]Row 884[/TD]
[TD]123[/TD]
[TD]970731[/TD]
[TD]Scrapers[/TD]
[TD]ned_application_lov[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 885[/TD]
[TD]456[/TD]
[TD]970731[/TD]
[TD]Scrapers[/TD]
[TD]ned_application_lov[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]












Based on the formula that I am using I would expect that Att 2 and Att 3 are also being filled in with type_lov and content_lov. But that is not the case. The remain blank.



I am using the following formula in cell S884:

{=IFERROR(INDEX(Table1[AttributeID];SMALL(IF(Table1[Product Line ID]=$L884;ROW(Table1[AttributeID])-MIN(ROW(Table1[AttributeID]))+1);COLUMNS($S$2:S884)));"")}

For cell S885 I use this formula:

{=IFERROR(INDEX(Table1[AttributeID];SMALL(IF(Table1[Product Line ID]=$L885;ROW(Table1[AttributeID])-MIN(ROW(Table1[AttributeID]))+1);COLUMNS($S$2:S885)));"")}


I am using the following formula in cell V884:

{=IFERROR(INDEX(Table1[AttributeID];SMALL(IF(Table1[Product Line ID]=$L884;ROW(Table1[AttributeID])-MIN(ROW(Table1[AttributeID]))+1);COLUMNS($S$2:V885)));"")}


I am using the following formula in cell V885:

{=IFERROR(INDEX(Table1[AttributeID];SMALL(IF(Table1[Product Line ID]=$L885;ROW(Table1[AttributeID])-MIN(ROW(Table1[AttributeID]))+1);COLUMNS($S$2:V885)));"")}


For the Y cells same story:

{=IFERROR(INDEX(Table1[AttributeID];SMALL(IF(Table1[Product Line ID]=$L884;ROW(Table1[AttributeID])-MIN(ROW(Table1[AttributeID]))+1);COLUMNS($S$2:Y884)));"")}

and

{=IFERROR(INDEX(Table1[AttributeID];SMALL(IF(Table1[Product Line ID]=$L885;ROW(Table1[AttributeID])-MIN(ROW(Table1[AttributeID]))+1);COLUMNS($S$2:Y885)));"")}


I Have found the original formula here where also the explanation is:
https://exceljet.net/formula/extract-multiple-matches-into-separate-columns


Just to be sure:
What I would like is that the where in "Table1" the product lines with the attributes in the next column are shown in vertical order, to have them shown horizontally in the "Data Sheet" as shown in the example. I would expect that where Att 2 and Att 3 are now empty, that those are filled. This is the case with other items that I have in this "Data Sheet". And since we are talking about hundreds of items there is no way t do it by hand. Therefore this formula, but somehow it does either not work and/or give me the result that I am expecting.

Can somebody please help me with this?

Thanks a lot already in advance,
Yoeri

 

[wideboydixon]

COLUMNS($S$2:V885) is going to return 4; not 2 as you would need. Try this in S884 and then copy it across to columns V and Y (references should adjust automatically)

{=IFERROR(INDEX(Table1[AttributeID];SMALL(IF(Table1[Product Line ID]=$L884;ROW(Table1[AttributeID])-MIN(ROW(Table1[AttributeID]))+1);(COLUMNS($S884:S884)-1)/3+1));"")}

If all is well you'll get the following in V and Y:

{=IFERROR(INDEX(Table1[AttributeID];SMALL(IF(Table1[Product Line ID]=$L884;ROW(Table1[AttributeID])-MIN(ROW(Table1[AttributeID]))+1);(COLUMNS($S884:V884)-1)/3+1));"")}
{=IFERROR(INDEX(Table1[AttributeID];SMALL(IF(Table1[Product Line ID]=$L884;ROW(Table1[AttributeID])-MIN(ROW(Table1[AttributeID]))+1);(COLUMNS($S884:Y884)-1)/3+1));"")}

WBD

 

[Yoeri]

COLUMNS($S$2:V885) is going to return 4; not 2 as you would need. Try this in S884 and then copy it across to columns V and Y (references should adjust automatically)

{=IFERROR(INDEX(Table1[AttributeID];SMALL(IF(Table1[Product Line ID]=$L884;ROW(Table1[AttributeID])-MIN(ROW(Table1[AttributeID]))+1);(COLUMNS($S884:S884)-1)/3+1));"")}

If all is well you'll get the following in V and Y:

{=IFERROR(INDEX(Table1[AttributeID];SMALL(IF(Table1[Product Line ID]=$L884;ROW(Table1[AttributeID])-MIN(ROW(Table1[AttributeID]))+1);(COLUMNS($S884:V884)-1)/3+1));"")}
{=IFERROR(INDEX(Table1[AttributeID];SMALL(IF(Table1[Product Line ID]=$L884;ROW(Table1[AttributeID])-MIN(ROW(Table1[AttributeID]))+1);(COLUMNS($S884:Y884)-1)/3+1));"")}

WBD

Very very nice. Thank you very much. It is working now.

Thank you for your time and effort that you have spend to look at it, really appreciated.