Array Formula Results Across Single Row (not down column)

[VvM]

Hi All,

I'm having a tough time with an array formula. My goal is for the formula to find unique values in a list and return them in separate cells. I can get the formula to work in a single column and multiple rows, but I cannot get it to work in a single row and multiple columns.

I've included a very simplified version of what I'm trying to accomplish.

If any seasoned array experts happen to see this and have any suggestions it would be greatly appreciated.

Thank you!

 

[eibi]

VvM,

Welcome to the forums!

The problem in your formula in in the COUNTIF range (bold below)

=INDEX(Item, SMALL(IF(($C$10=Category)*(COUNTIF($E$9:E10,Item)=0), ROW(Category)-MIN(ROW(Category))+1, ""), 1))

Try this instead -- paste in cell E10 and drag horizontal to fill.

=INDEX(Item, SMALL(IF(($C$10=Category)*(COUNTIF(OFFSET($D$10,,,1,COLUMNS($D$10:D10)),Item)=0), ROW(Category)-MIN(ROW(Category))+1, ""), 1))

See attached. Does this meet your needs?

All best.

 

[VvM]

Thank you so much eibi!

I appreciate you help and like your solution.

After banging our heads against the wall, a non-array solution was found (=IFERROR(INDEX($D3:$D6,MATCH(1,INDEX(($C$3:$C$6=$C10)/(COUNTIF($D10:D10,$D3:$D6)=0),0),0)),"") see attached).

Both work, and time will tell if the array or iferror solution performs better on the larger, live data set.

Again, thank you eibi (and Chandoo for hosting).

VvM

 

[eibi]

As I reviewed this thread, I realized that I overcomplicated the solution. It should have been as easy as changing this:

(in cell E10, the original array formula)

=INDEX(Item, SMALL(IF(($C$10=Category)*(COUNTIF($E$9:$E$9,Item)=0), ROW(Category)-MIN(ROW(Category))+1, ""), 1))

to this (see attached):

=INDEX(Item, SMALL(IF(($C$10=Category)*(COUNTIF($D$10:D10,Item)=0), ROW(Category)-MIN(ROW(Category))+1, ""), 1))

Silly me.

 

[Somendra Misra]

Hi,

Just my thought over it. Since you may have a large data set, why not try Pivot table as it will be fast and you may not skip any result as in the case of formula if you don't stretch it far enough, which you might not know in a large data set.

Regards,

 

[VvM]

Hi SM,

A pivot table was my first approach, but the individual who gave me the project wanted the results to be contained within a formula.

Ergo the convoluted formulas.