Array function to display list horizontally rather than vertically.

[Isambard]

I have a really great formula (not my own design) which does 99% of what I want but falls at the last hurdle. My spreadsheet is basically a 2 column list of things, for example fruits, with a corresponding attribute, such as colours. So A:A might contain apple, apple, banana, banana... and the corresponding entries in B:B would be red, green, yellow, green... Elsewhere I have a horizontal list in row 1:1 starting in cell H1, with every fruit header: apple, banana... under which the formula:

{=IF(ISERROR(INDEX($A$1:$B$75,SMALL(IF($A$1:$A$75=H$1,ROW($A$1:$A$75)),ROW(1:1)),2)),"",INDEX($A$1:$B$75,SMALL(IF($A$1:$A$75=H$1,ROW($A$1:$A$75)),ROW(1:1)),2))}

can be dragged down and across to list all the attributes that ever appear with that fruit. (The apple column would have red and green in it, in this example.)

However I got this from minor edits to an existing spreadsheet and what I really want to have is a vertical list in H:H starting from H:2 with my headings which then give horizontal lists of all the attributes. I am new to array functions and really struggling to make this happen.

I would be hugely grateful if anyone can advise me.</SPAN>

 

[Aladin Akyurek]

Row\Col	A​	B​	H​	I​	J​	K​
1​	FRUIT	COLOR	apple	kivi	pear	berry
2​	apple	red	red	green	yellow	red
3​	kivi	green
4​	pear	yellow
5​	berry	red

In H2 control+shift+enter, not just enter, and copy across:

=IFERROR(INDEX($B$2:$B$5,SMALL(IF($A$2:$A$5=H$1,ROW($B$2:$B$5)-ROW($B$2)+1),COLUMNS(H$2:H2))),"")<strike></strike>

 

[Eric W]

Or maybe you'd like the output to look like this?

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L
1	FRUIT	COLOR
2	apple	red					6	apple	red	green	yellow
3	kivi	green						kivi	green
4	pear	yellow						pear	yellow
5	berry	red						berry	red
6	apple	green						grape	red	purple
7	grape	red						orange	orange
8	apple	yellow
9	grape	purple
10	orange	orange
11

<tbody>
</tbody>

Sheet14​

Array Formulas Cell Formula G2 {=SUMPRODUCT(IF(MATCH($A$2:$A$10,$A$2:$A$10,0)=ROW($A$2:$A$10)-ROW($A$2)+1,1,0))} H2 {=IF(ROW()-1<=$G$2,INDEX($A$2:$A$10,SMALL(IF(MATCH($A$2:$A$10,$A$2:$A$10,0)=ROW($A$2:$A$10)-ROW($A$2)+1,ROW($A$2:$A$10)-ROW($A$2)+1),ROW()-1)),"")} I2 {=IFERROR(INDEX($B$2:$B$10,SMALL(IF($A$2:$A$10=$H2,ROW($A$2:$A$10)-ROW($A$2)+1),COLUMNS($I2:I2))),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Enter the formula in G2 and confirm with Control-Shift-Enter. That's the number of unique fruits. Then enter the formula in H2 and confirm with Control-Shift-Enter. Then copy the cell and drag down. (If you plan to manually put in the fruit names, then you can skip both of those formulas.) Finally enter the formula in I2 and confirm with Control-Shift-Enter. Then copy it down and to the right as far as you need.

 

[Aladin Akyurek]

...

However I got this from minor edits to an existing spreadsheet and what I really want to have is a vertical list in H:H starting from H:2 with my headings which then give horizontal lists of all the attributes. I am new to array functions and really struggling to make this happen.
...

Would you post a small sample and the output desire if post #2 is not what you want?

 

[Isambard]

Thanks both. The second solution is very close to what I'm trying to do but I still can't get the functions you suggest working - all the cells I copied across just cam out blank.

There is other stuff in the sheet, but this is the layout of the relevant part:

	A	B	C	D	E	F	G	H	I	J	K
1						Fruit Name	Colour	List Headers (Fruit Names without duplicates)	Colour 1	Colour 2	Colour 3
2						Apple	Red	Apple
3						Apple	Green	Kiwi
4						Kiwi	Green	Pear
5						Pear	White	Orange
6						Orange	Orange	Grapefruit
7						Grapefruit	Red
8						Grapefruit	Pink
9						Grapefruit	Yellow
10

<tbody>
</tbody>

So in I2 onward I want the function that shows the colours corresponding to fruits in H:H. (I2 would be "Red", J2 would be "Green", K2 and all other cells in row 2 would be blank.

Thanks again for your help. I feel like I should be getting this, but just way out of my excel ability. I really appreciate you taking the time.

 

[Aladin Akyurek]

Thanks for the sample. One question though: Is it possible to have duplicate records in F:G, twice Kiwi & Green, for example?

 

[navic]

Try

In H2 cell. Copy to right and down
This is ARRAY formula

=IFERROR(INDEX($F$2:$F$9;MATCH(0;COUNTIF($H$1:H1;$F$2:$F$9);0));0)

In I2 cell. Copy to right and down
This is ARRAY formula

=IFERROR(INDEX($G$1:$G$10;SMALL(IF($H2=$F$1:$F$10;ROW($F$1:$F$10)-MIN(ROW($F$1:$F$10))+1;"");RIGHT(I$1;1)));"")

ARRAY or CSE formula should be finished by pressing Ctrl+Shift+Enter (not just enter)
BTW: Pay attention, you may need as a separator of arguments formula use a comma (,) instead of a semicolon (; )
Copy formula to Notepad and with Find/Replace change semicolon to comma

 

[Isambard]

That works perfectly Navic. Thank you. I'll make sure I practice more with indicies and things for the future.

 

[Aladin Akyurek]

Thanks for the sample. One question though: Is it possible to have duplicate records in F:G, twice Kiwi & Green, for example?

That works perfectly Navic. Thank you. I'll make sure I practice more with indicies and things for the future.

This means duplicates can occur in F:G records... The following is not just an alternative, I consider the set up better.

Row\Col	F​	G​	H​	I​	J​	K​	L​
1​			5​
2​	Fruit Name	Colour	Fruit	Colour 1	Colour 2	Colour 3
3​	Apple	Red	Apple	Red	Green
4​	Apple	Green	Kiwi	Green
5​	Kiwi	Green	Pear	White
6​	Pear	White	Orange	Orange
7​	Orange	Orange	Grapefruit	Red	Pink	Yellow
8​	Grapefruit	Red
9​	Grapefruit	Pink
10​	Grapefruit	Yellow

F3:F10 is named Frange; G3:G10 Grange.

Define additionally Ivec in Formulas | Name Manager as referring to:

=ROW(Frange)-ROW(INDEX(Frange,1,1))+1

In H3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($H$3:H3)<=$H$1,INDEX(Frange,SMALL(IF(FREQUENCY(IF(1-(Frange=""),
  MATCH(Frange,Frange,0)),Ivec),Ivec),ROWS($H$3:H3))),"")

In I3 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Grange,SMALL(IF(Frange=$H3,Ivec),COLUMNS($I3:I3))),"")

Note. Eliminating IFERROR in the last formula would be even better for reasons of efficiency.

 

[navic]

Thank you.

If you want to avoid duplication of color for a unique fruit then you use this ARRAY formula in I3 cell (copy right and down)

=IFERROR(INDEX($G$2:$G$10;MATCH(0;COUNTIF($H2:H2;$G$2:$G$10)+IF($F$2:$F$10<>$H2;1;0);0));"")

If you use this formula, then the header may be empty.