ignoring blank cells in an array forumla

[ENAPo]

I have this formula in a cell:
=IF(ISERROR(INDEX($R$1:$AF$5000,SMALL(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$AF$5000)),ROW(5:5)),5)),"",INDEX($R$1:$AF$5000,SMALL(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$AF$5000)),ROW(5:5)),5))

If the cell it's pulling the information from is blank it puts a 0 in the cell. I want it to just leave it blank if the cell is blank but am at a loss where to put that in the formula. Hope that doesn't sound confusing.

Thanks!!

 

[ENAPo]

so far so good. BTW I have excel 2010

 

[Aladin Akyurek]

so far so good. BTW I have excel 2010

Is that first cell a big secret?

 

[markmzz]

so far so good. BTW I have excel 2010

Hi ENAPo,

If you have Excel 2010, so you can use one of this formulas (without any other format):

Array formulas - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IFERROR(INDEX(IF($R$1:$AF$5000="","",$R$1:$AF$5000),SMALL(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$R$5000)),ROW(5:5)),5),"")

Or

=IFERROR(INDEX(IF(INDEX($R$1:$AF$5000,,5)="","",INDEX($R$1:$AF$5000,,5)),
SMALL(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$R$5000)),ROW(5:5))),"")

Or

=IFERROR(INDEX(IF(INDEX($R$1:$AF$5000,,5)="","",INDEX($R$1:$AF$5000,,5)),
SMALL(IF(ISNUMBER(FIND("|"&$Q$1&"|","|"&$R$1:$AF$5000&"|")),ROW($R$1:$R$5000)),ROW(5:5))),"")

Do some tests and tell me if Works.

Markmzz

 

[Hercules1946]

Hi ENAPo,

If you have Excel 2010, so you can use one of this formulas (without any other format):

Array formulas - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IFERROR(INDEX(IF($R$1:$AF$5000="","",$R$1:$AF$5000),SMALL(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$R$5000)),ROW(5:5)),5),"")

Or

=IFERROR(INDEX(IF(INDEX($R$1:$AF$5000,,5)="","",INDEX($R$1:$AF$5000,,5)),
SMALL(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$R$5000)),ROW(5:5))),"")

Or

=IFERROR(INDEX(IF(INDEX($R$1:$AF$5000,,5)="","",INDEX($R$1:$AF$5000,,5)),
SMALL(IF(ISNUMBER(FIND("|"&$Q$1&"|","|"&$R$1:$AF$5000&"|")),ROW($R$1:$R$5000)),ROW(5:5))),"")

Do some tests and tell me if Works.

Markmzz

Hi Markmzz
In your third formula, what do the "|" charaacters do please ? Haven't seen that before......

Cheers
Hercules

 

[markmzz]

Hi Markmzz
In your third formula, what do the "|" charaacters do please ? Haven't seen that before......

Cheers
Hercules

Hi Hercules,

Look at the table below (look at Test1 and myTest1 and look at Test2 and myTest2):

[TABLE="width: 309"]
<tbody>[TR]
[TD="class: xl63, width: 61, bgcolor: transparent"]Header01
[/TD]
[TD="class: xl63, width: 17, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 42, bgcolor: transparent"]Lookup
[/TD]
[TD="class: xl63, width: 52, bgcolor: transparent"]Formula1
[/TD]
[TD="class: xl63, width: 238, bgcolor: transparent"]=COUNT(FIND(C2,$A$2:$A$6))
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Test1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]Test1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl64, bgcolor: #00B050"]=COUNT({1;3;#VALUE!;#VALUE!;#VALUE!})
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]myTest1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]Test2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl64, bgcolor: #00B050"]=COUNT({#VALUE!;#VALUE!;1;3;#VALUE!})
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Test2
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]Lookup
[/TD]
[TD="class: xl63, bgcolor: transparent"]Formula2
[/TD]
[TD="class: xl63, bgcolor: transparent"]=COUNT(FIND("|"&C5&"|","|"&$A$2:$A$6&"|"))
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]myTest2
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]Test1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl64, bgcolor: #00B050"]=COUNT({1;#VALUE!;#VALUE!;#VALUE!;#VALUE!})
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Test4
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]Test2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl64, bgcolor: #00B050"]=COUNT({#VALUE!;#VALUE!;1;#VALUE!;#VALUE!})
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]**********
[/TD]
[TD="class: xl63, bgcolor: transparent"]**
[/TD]
[TD="class: xl63, bgcolor: transparent"]*******
[/TD]
[TD="class: xl63, bgcolor: transparent"]*********
[/TD]
[TD="class: xl63, bgcolor: transparent"]*********************************************
[/TD]
[/TR]
</tbody>[/TABLE]

The Formula2 find only the exactly lookup value (only Test1 and only Test2).

I hope that this helps.

A small modification in one of my formulas:

Array formulas - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IFERROR(INDEX(IF(INDEX($R$1:$AF$5000,,5)="","",INDEX($R$1:$AF$5000,,5)),
SMALL(IF(ISNUMBER([COLOR=#ff0000]SEARCH[/COLOR]("|"&$Q$1&"|","|"&$R$1:$AF$5000&"|")),ROW($R$1:$R$5000)),ROW(5:5))),"")

Markmzz

 

[Hercules1946]

Hi Hercules,

Look at the table below (look at Test1 and myTest1 and look at Test2 and myTest2):

[TABLE="width: 309"]
<tbody>[TR]
[TD="class: xl63, width: 61, bgcolor: transparent"]Header01
[/TD]
[TD="class: xl63, width: 17, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 42, bgcolor: transparent"]Lookup
[/TD]
[TD="class: xl63, width: 52, bgcolor: transparent"]Formula1
[/TD]
[TD="class: xl63, width: 238, bgcolor: transparent"]=COUNT(FIND(C2,$A$2:$A$6))
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Test1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]Test1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl64, bgcolor: #00B050"]=COUNT({1;3;#VALUE!;#VALUE!;#VALUE!})
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]myTest1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]Test2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl64, bgcolor: #00B050"]=COUNT({#VALUE!;#VALUE!;1;3;#VALUE!})
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Test2
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]Lookup
[/TD]
[TD="class: xl63, bgcolor: transparent"]Formula2
[/TD]
[TD="class: xl63, bgcolor: transparent"]=COUNT(FIND("|"&C5&"|","|"&$A$2:$A$6&"|"))
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]myTest2
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]Test1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl64, bgcolor: #00B050"]=COUNT({1;#VALUE!;#VALUE!;#VALUE!;#VALUE!})
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Test4
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]Test2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl64, bgcolor: #00B050"]=COUNT({#VALUE!;#VALUE!;1;#VALUE!;#VALUE!})
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]**********
[/TD]
[TD="class: xl63, bgcolor: transparent"]**
[/TD]
[TD="class: xl63, bgcolor: transparent"]*******
[/TD]
[TD="class: xl63, bgcolor: transparent"]*********
[/TD]
[TD="class: xl63, bgcolor: transparent"]*********************************************
[/TD]
[/TR]
</tbody>[/TABLE]

The Formula2 find only the exactly lookup value (only Test1 and only Test2).

I hope that this helps.

A small modification in one of my formulas:

Markmzz

Thank you for takling the trouble to reply.
Sorry if Im missing something, but dont I need to know what values are in C2 and A2:A6 to follow you?
Also, should the entry against Formula2 be =COUNT(FIND("|"&C2&"|","|"&$A$2:$A$6&"|")) ?

 

[markmzz]

Thank you for takling the trouble to reply.
Sorry if Im missing something, but dont I need to know what values are in C2 and A2:A6 to follow you?
Also, should the entry against Formula2 be =COUNT(FIND("|"&C2&"|","|"&$A$2:$A$6&"|")) ?

If you look at the lookup values in C2 and C5 they are the same (Test1).

And if you look at the lookup values in C3 and C6 they are the same (Test2).

With the Formula1 we have found 2 values (Test1 and myTest1 for C2-Test1 and Test2 and myTest2 for C3-Test2)

With the Formula2 we have found 1 value (only Test1 for C5-Test1 and only Test2 for C6-Test2).

I hope that this helps.

Markmzz

 

[Hercules1946]

Sorry - I think I get it now .
I put my own data into the cells you've used in order to demonstrate the different results?. I'm afraid I'm good at missing the obvious sometimes..........

Thanks very much for your help.

Hercules

 

[Hercules1946]

If you look at the lookup values in C2 and C5 they are the same (Test1).

And if you look at the lookup values in C3 and C6 they are the same (Test2).

With the Formula1 we have found 2 values (Test1 and myTest1 for C2-Test1 and Test2 and myTest2 for C3-Test2)

With the Formula2 we have found 1 value (only Test1 for C5-Test1 and only Test2 for C6-Test2).

I hope that this helps.

Markmzz

OK - So the bars | | create discrete values for "Find_Text" and "Within_Text" and these are then used to ensure that the test isn't TRUE unless "Find Text" = "Within Text" ?

=FIND(find_text, within_text, [start_num])
Hope Ive got that right.

 

[ENAPo]

Is that first cell a big secret?

It is super secret.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]6[/TD]
[TD]10/31/2013[/TD]
[TD]14578[/TD]
[TD]Butter County[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10/16/2013[/TD]
[TD]15489[/TD]
[TD]Cream County[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10/6/2013[/TD]
[TD]45879[/TD]
[TD]Cheese County[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]11/02/2013[/TD]
[TD]45684[/TD]
[TD]Butter County[/TD]
[/TR]
</tbody>[/TABLE]







This is what I need to do and I hope I explain it right. I have a large amount of information with 15 columns and up to 2000+ rows, that I paste/import into Excel from Quickbooks. I have a drop down box that lists products that coincides with the numbers in the above table. (6,3...). When I select a product from the drop down menu, I want it to populate all the information in my 2000+ rows that equals the number into another section of the worksheet. Ideally, I would like a new worksheet separate from the main information. For instance, #6 is populated in cell Q1 when I select the product from the drop down menu. So in another worksheet or area of table, I want everything in that row that equals 6 to be populated and however many rows has 6. Does that make any sense? I basically want a whole other table that filters for only the item that I have selected. I need to show this to others and I would rather not have them mess with filtering. I would be REALLY nice if I can have it filter based on the month that is populated in A1 as well. For instance, only give me every line that equals 6 and is in the month of October. I am really new to all these formulas in excel and am trying my best. Not trying to be aloof.