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]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][/tr]
[tr][td]

1​

[/td][td]FRUIT[/td][td]COLOR[/td][td]apple[/td][td]kivi[/td][td]pear[/td][td]berry[/td][/tr]


[tr][td]

2​

[/td][td]apple[/td][td]red[/td][td]red[/td][td]green[/td][td]yellow[/td][td]red[/td][/tr]


[tr][td]

3​

[/td][td]kivi[/td][td]green[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

4​

[/td][td]pear[/td][td]yellow[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

5​

[/td][td]berry[/td][td]red[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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
apple	red	green	yellow
kivi	green
pear	yellow
berry	red
grape	red	purple
orange	orange

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]FRUIT[/TD]
[TD="bgcolor: #FAFAFA"]COLOR[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]apple[/TD]
[TD="bgcolor: #FAFAFA"]red[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]kivi[/TD]
[TD="bgcolor: #FAFAFA"]green[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]pear[/TD]
[TD="bgcolor: #FAFAFA"]yellow[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]berry[/TD]
[TD="bgcolor: #FAFAFA"]red[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]apple[/TD]
[TD="bgcolor: #FAFAFA"]green[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]grape[/TD]
[TD="bgcolor: #FAFAFA"]red[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]apple[/TD]
[TD="bgcolor: #FAFAFA"]yellow[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]grape[/TD]
[TD="bgcolor: #FAFAFA"]purple[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]orange[/TD]
[TD="bgcolor: #FAFAFA"]orange[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet14​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]{=SUMPRODUCT(IF(MATCH($A$2:$A$10,$A$2:$A$10,0)=ROW($A$2:$A$10)-ROW($A$2)+1,1,0))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]I2[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]





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:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Fruit Name[/TD]
[TD]Colour[/TD]
[TD]List Headers (Fruit Names without duplicates)[/TD]
[TD]Colour 1[/TD]
[TD]Colour 2[/TD]
[TD]Colour 3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD]Red[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD]Green[/TD]
[TD]Kiwi[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Kiwi[/TD]
[TD]Green[/TD]
[TD]Pear[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pear[/TD]
[TD]White[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Orange[/TD]
[TD]Orange[/TD]
[TD]Grapefruit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Grapefruit[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Grapefruit[/TD]
[TD]Pink[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Grapefruit[/TD]
[TD]Yellow[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td][/td][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

2​

[/td][td]Fruit Name[/td][td]Colour[/td][td]Fruit[/td][td]Colour 1[/td][td]Colour 2[/td][td]Colour 3[/td][td][/td][/tr]


[tr][td]

3​

[/td][td]Apple[/td][td]Red[/td][td]Apple[/td][td]Red[/td][td]Green[/td][td][/td][td][/td][/tr]


[tr][td]

4​

[/td][td]Apple[/td][td]Green[/td][td]Kiwi[/td][td]Green[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

5​

[/td][td]Kiwi[/td][td]Green[/td][td]Pear[/td][td]White[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

6​

[/td][td]Pear[/td][td]White[/td][td]Orange[/td][td]Orange[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

7​

[/td][td]Orange[/td][td]Orange[/td][td]Grapefruit[/td][td]Red[/td][td]Pink[/td][td]Yellow[/td][td][/td][/tr]


[tr][td]

8​

[/td][td]Grapefruit[/td][td]Red[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

9​

[/td][td]Grapefruit[/td][td]Pink[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

10​

[/td][td]Grapefruit[/td][td]Yellow[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.