PDA

View Full Version : [SOLVED] Listing unique vales in a range with blank cells

bifjamod
04-28-2016, 01:10 PM
I have a list of values in column B, starting at B3 and potentially extending all the way to B100. There will be no blank cells between cells with values, but there may be blank cells afterward (B3:B13 may have values, with the remainder of B14:B100 being blank.)

I've tried the following two formulas to return unique values from that list, with results as indicated. Can anyone offer a solution which helps me avoid the undesirable side effects I'm getting?

=IFERROR(INDEX('Entry Listing'!\$B\$3:\$B\$100, MATCH(0,COUNTIF(\$D\$13:D14, 'Entry Listing'!\$B\$3:\$B\$100), 0)),"")
This formula returns a zero in the first cell without a returned value. All others are then blank. (This formula exists in cells D14:28).

{=INDEX('Entry Listing'!\$B\$3:\$B\$100, MATCH(0, IF(ISBLANK('Entry Listing'!\$B\$3:\$B\$100), 1, COUNTIF(\$D\$13:D13, 'Entry Listing'!\$B\$3:\$B\$100)), 0))}
This formula returns an #N/A error in all cells without a returned value. I suppose some method of incorporating an IF(ISNA...) into this formula might solve it, but so far I can only get it to return a value of FALSE by adding that.

Zack Barresse
04-28-2016, 02:18 PM
This can get a little tricky with formulas. There's no native way to do this, but there are several workarounds. I'd recommend using Advanced Filter first, because it's an easy way to extract a list of uniques. Or to use the Remove Duplicates feature on a copy of the data, which will do the same thing but takes a few extra clicks. Additionally there are ways of doing this with VBA, which, IMHO, can sometimes be easier.

If you still want a formula it will be long, and is generally best being chunked up for both maintenance and readability. This will work though...

=IF(ROWS('Entry Listing'!\$B\$1:B1)<=SUM(IF(FREQUENCY(IF('Entry Listing'!\$B\$3:\$B\$100<>"",MATCH("~"&'Entry Listing'!\$B\$3:\$B\$100&"",'Entry Listing'!\$B\$3:\$B\$100&"",0)),ROW('Entry Listing'!\$B\$1:\$B\$98)-ROW('Entry Listing'!\$B\$1)+1),1)),INDEX('Entry Listing'!\$B\$3:\$B\$100,SMALL(IF(FREQUENCY(IF('Entry Listing'!\$B\$3:\$B\$100<>"",MATCH("~"&'Entry Listing'!\$B\$3:\$B\$100&"",'Entry Listing'!\$B\$3:\$B\$100&"",0)),ROW('Entry Listing'!\$B\$3:\$B\$100)-ROW('Entry Listing'!\$B\$3)+1),ROW('Entry Listing'!\$B\$3:\$B\$100)-ROW('Entry Listing'!\$B\$3)+1),ROWS('Entry Listing'!\$B\$3:B3))),"")

snb
04-29-2016, 12:09 AM
In VBA e.g.

Sub M_snb()
sn = Application.Transpose(Filter([transpose(if(B3:B100="","~",B3:B100))], "~", 0))
Cells(1, 5).Resize(UBound(sn)) = sn
End Sub

In Excel the arrayformula:

=INDEX(B\$1:B\$100,SMALL(IF(B\$3:B\$100<>"",ROW(B\$3:B\$100)),ROW(A1)))

bifjamod
04-29-2016, 07:47 AM
In Excel the arrayformula:

=INDEX(B\$1:B\$100,SMALL(IF(B\$3:B\$100<>"",ROW(B\$3:B\$100)),ROW(A1)))

This one does not work; it produces multiples, zeroes and #NUM! errors.

bifjamod
04-29-2016, 07:51 AM
This can get a little tricky with formulas. There's no native way to do this, but there are several workarounds. I'd recommend using Advanced Filter first, because it's an easy way to extract a list of uniques. Or to use the Remove Duplicates feature on a copy of the data, which will do the same thing but takes a few extra clicks. Additionally there are ways of doing this with VBA, which, IMHO, can sometimes be easier.

If you still want a formula it will be long, and is generally best being chunked up for both maintenance and readability. This will work though...

=IF(ROWS('Entry Listing'!\$B\$1:B1)<=SUM(IF(FREQUENCY(IF('Entry Listing'!\$B\$3:\$B\$100<>"",MATCH("~"&'Entry Listing'!\$B\$3:\$B\$100&"",'Entry Listing'!\$B\$3:\$B\$100&"",0)),ROW('Entry Listing'!\$B\$1:\$B\$98)-ROW('Entry Listing'!\$B\$1)+1),1)),INDEX('Entry Listing'!\$B\$3:\$B\$100,SMALL(IF(FREQUENCY(IF('Entry Listing'!\$B\$3:\$B\$100<>"",MATCH("~"&'Entry Listing'!\$B\$3:\$B\$100&"",'Entry Listing'!\$B\$3:\$B\$100&"",0)),ROW('Entry Listing'!\$B\$3:\$B\$100)-ROW('Entry Listing'!\$B\$3)+1),ROW('Entry Listing'!\$B\$3:\$B\$100)-ROW('Entry Listing'!\$B\$3)+1),ROWS('Entry Listing'!\$B\$3:B3))),"")

WOW! That is an obscenely large formula.....BUT, it seems to do the trick! I had originally intended to accomplish this via VBA, but as my project progressed, it seemed to me that for the scale and scope of it, a formulaic solution for this little section of it might be the easiest way to go.

Thank you - I appreciate your time and assistance.

snb
04-29-2016, 07:58 AM
This one does not work; it produces multiples, zeroes and #NUM! errors.

You might do something yourself.....

=IFerror(INDEX(B\$1:B\$100,SMALL(IF(B\$3:B\$100<>"",ROW(B\$3:B\$100)),ROW(A1))),"")

So I hope other people will benefit.

bifjamod
04-29-2016, 08:03 AM
Thank you snb; I do appreciate that. I had not considered adding an IFERROR (or ISNUM) type solution. I also appreciate the VBA snippet. I'll keep that one in my back pocket for future use....

Zack Barresse
04-29-2016, 11:20 AM
In VBA e.g.

Sub M_snb()
sn = Application.Transpose(Filter([transpose(if(B3:B100="","~",B3:B100))], "~", 0))
Cells(1, 5).Resize(UBound(sn)) = sn
End Sub

Very clever. :)

Zack Barresse
04-29-2016, 11:51 AM
WOW! That is an obscenely large formula.....BUT, it seems to do the trick! I had originally intended to accomplish this via VBA, but as my project progressed, it seemed to me that for the scale and scope of it, a formulaic solution for this little section of it might be the easiest way to go.

Thank you - I appreciate your time and assistance.

Yeah, I know it's long. It's easier to maintain if you break it out into multiple cells, which you can. I know how alluring it can be to have everything in a single cell though. The downside is it makes maintenance/understanding the formula extremely difficult. I think I would do this via code. Mostly because I don't like copying formulas down to a large area when not needed. But that's me. Glad it works for you. :)