Index with small limitations

[mbarbera83]

Hi all.
I am trying to create a list of products by brand, using the Index function with Small, but it seems that my data base is too big for the memory. when i index only to row 21, then it works, but when i take it to row 1500 which I need, i get an error

=IF(ROWS(E$3:E3)>E$2,"",INDEX($A$2:$A$21,SMALL(IF($B$2:$B$21=E$1,ROW($B$2:$B$21)-ROW($B$2)+1),ROWS(E$3:E3))))

is there any way to get around this?

[TABLE="width: 331"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Brand[/TD]
[TD][/TD]
[TD][/TD]
[TD]Brand1[/TD]
[TD]Brand2[/TD]
[/TR]
[TR]
[TD]product1[/TD]
[TD]Brand1[/TD]
[TD][/TD]
[TD][/TD]
[TD]=count Brand1 in column B[/TD]
[TD]=count Brand2 in column[/TD]
[/TR]
[TR]
[TD]product2[/TD]
[TD]Brand2[/TD]
[TD][/TD]
[TD][/TD]
[TD]FUNCTION GOES HERE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]product3[/TD]
[TD]Brand1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[GerryZ]

Why don't you use filter or advance filter
those are two great tools

 

[Aladin Akyurek]

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

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

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

1​

[/td][td]Product[/td][td]Brand[/td][td][/td][td][/td][td]Brand1[/td][td]Brand2[/td][/tr]

[tr][td]

2​

[/td][td]product1[/td][td]Brand1[/td][td][/td][td][/td][td]

2​

[/td][td]

1​

[/td][/tr]

[tr][td]

3​

[/td][td]product2[/td][td]Brand2[/td][td][/td][td][/td][td]product1[/td][td]product2[/td][/tr]

[tr][td]

4​

[/td][td]product3[/td][td]Brand1[/td][td][/td][td][/td][td]product3[/td][td][/td][/tr]

[tr][td]

5​

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

E2, just enter and copy across:

=COUNTIF($B$2:$B$4,E$1)

E3, control+shift+enter, not just enter, copy acsoss, and down:

=IF(ROWS(E$3:E3)<=E$2,INDEX($A$2:$A$4,SMALL(IF($B$2:$B$4=E$1,
   ROW($B$2:$B$4)-ROW($B$2)+1),ROWS(E$3:E3))),"")

 

[mbarbera83]

I need this to be dynamic, as the product list changes, and i am using the final result to create validation lists.

Why don't you use filter or advance filter
those are two great tools

 

[Aladin Akyurek]

You skip post #3...

 

[mbarbera83]

I am aware of the Array cntrl shift enter function, the problem is that as soon as the data in column A goes over 22 lines, (my data is 1400 lines long) the array does not work

 

[Aladin Akyurek]

I am aware of the Array cntrl shift enter function, the problem is that as soon as the data in column A goes over 22 lines, (my data is 1400 lines long) the array does not work

Really? Why don't you check it out the suggested set up?

 

[mbarbera83]

Is there any way i can send you the excel file.

even with the updated formula i get an error.

 

[Aladin Akyurek]

Is there any way i can send you the excel file.

No.

even with the updated formula i get an error.

See the workbook that implements the formula set up: https://dl.dropboxusercontent.com/u/65698317/mbarbera83 brand sublists.xlsx

 

[mbarbera83]

apologies - i did not realize that there was a change in the formula. but even with your formula - as soon as i extend the array further down the column, then i get #VALUE