Unique list using Lookup, Countif, need one additional criteria

[jcooooper]

Hello all,

I stumbled across a wonderful formula which works perfect, but the logic is beyond my comprehension, so I'm having difficulty adding one bit of additional criteria.

In column H, I have a list of text strings, some of which are repeated

=IFERROR(LOOKUP(2,1/(COUNTIF($AA$2:AA3,$H$3:$H$34)=0),$H$3:$H$34),"")

The above creates a unique list from the list in column H. I drag this formula down and works fine...

What I need is it to exclude this name from the list if the corresponding value in column I is 0.

I tried this:

=IFERROR(LOOKUP(2,1/(COUNTIFS($AA$2:AA2,$H$3:$H$34,I4,"<>0")=0),$H$3:$H$34),"")

To no avail. The first name in the list pops up but then the rest start returning blanks.

Any help would be greatly appreciated

Thanks,
JC

 

[GazNicki]

Hi

What is in Column AA?

 

[AlanY]

try this array formula, confirm with shift-control-return together

=IFERROR(LOOKUP(2,1/(COUNTIF($AA$2:AA3,IF($I$3:$I$34<>0,$H$3:$H$34))=0),$H$3:$H$34),"")

 

[jcooooper]

Hmm I'm having the same problem... it works for the first entry then when I drag the formula down it blanks out.

I should specify, the formula is entered into cell AA4.... So it references the range above it as you drag it down

AA4 will reference $AA$2:AA3

AA23 will reference $AA$2:AA22

 

[jcooooper]

This is the unique list that the formula creates....it references the cells above it and grows dynamically as the forumla is dragged down...

AA4 will reference $AA$2:AA3

AA23 will reference $AA$2:AA22

etc etc

 

[AlanY]

try this, again an array formula

Book1
	H	I	Y
1
2
3	3	1
4	4	1	FALSE
5	5	1	23
6	6	1	22
7	7		21
8	8		20
9	9		19
10	10		18
11	11		17
12	12	1	14
13	13	1	13
14	14	1	12
15	15		6
16	16		5
17	17	1	4
18	18	1	3
19	19	1
20	20	1
21	21	1
22	22	1
23	23	1
Sheet6

Cell Formulas
Range	Formula
Y4	{=IFERROR(LOOKUP(2,1/(COUNTIF($Y$2:Y3,IF($I$3:$I$34<>0,$H$3:$H$34))=0),IF($I$3:$I$34<>0,$H$3:$H$34)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jcooooper]

try this, again an array formula

	H	I	Y

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]FALSE[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]23[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]22[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]21[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]20[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]19[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]18[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]17[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]14[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]13[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]12[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]6[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]5[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]4[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]3[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Y4[/TH]
[TD="align: left"]{=IFERROR(LOOKUP(2,1/(COUNTIF($Y$2:Y3,IF($I$3:$I$34<>0,$H$3:$H$34))=0),IF($I$3:$I$34<>0,$H$3:$H$34)),"")}[/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]

Ok this pretty much works..thanks! Only thing though is it is returning a false for those that have a 0..... Is there anyway to avoid the FALSE (can't have a blank either)

 

[AlanY]

i thought you might ask that.

the easiest way is to format the cell font colour as the fill colour, like this

Book1
	H	I	Y
1
2
3	3	1
4	4	1	FALSE
5	5	1	23
6	6	1	22
7	7		21
8	8		20
9	9		19
10	10		11
11	11		17
12	11		6
13	11	1	5
14	11	1	4
15	15		3
16	16
17	17	1
18	11	1
19	19	1
20	20	1
21	21	1
22	22	1
23	23	1
Sheet6

 

[jcooooper]

Yeah the issue is I am creating another table from this table, and it is dependant on a unique list with no blanks/falses/columns with 0 value as mentioned above....tricky one...I've tried a couple of people at work who are also stumped

 

[AlanY]

so, what will work in Cell Y4 above for the unique list?