blanks and empty cells

gnrboyd

Well-known Member
Joined
Jun 20, 2002
Messages
563
This should be an easy one for most of you. First of all, I am having difficulty understanding blanks and blank cells.

I am trying to use an if statement such as Cell B1 contains =If(A1<>"",vlookup(A1,data,2,false),""). I have this formula duplicated down the page. I basically want to perform the lookup only if cell A1 is not empty. When I sort the list by column B, the ones that the formula made blank, appear at the top of the list. (The info returned by the lookup is text. I tested numeric values and the blanks appear at the bottom)
Is there any other way to show the blank other than "" in the if statement?

I am using a macro to enter the vlookup formula for a user that isn't familiar with Excel but the list size varries so I run the formula down the page because I don't know how to tell the macro how far to go.

I need to be able to solve either the blank problem or the lookup problem mentioned in the above paragraph.

I would sure appreciate some assistance.

Thanks!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
Hi there

You could try something like this:
In a separate column use the LEFT formula to get the initial letter of the text in column B, then use this sort on that column:

Go to Tools / Options / Custom lists and make a list of the alphabet ending in "" (ef A,B,C,.....Y,Z,"").

Now when you sort, click the Options box and select your list from the dropdown box. Deselect the case sensitive box.

Hope this helps
regards
Derek
This message was edited by Derek on 2002-11-06 22:32
 

gnrboyd

Well-known Member
Joined
Jun 20, 2002
Messages
563
Derek,
Thanks for the suggestion but I tried it and the blanks ("") still sort at the top of the list. Also, I didn't think this would work since many people will be using this template and I would think this would require that they had the custom list set up on every computer. This wouldn't work too well. Any other ideas? How code for the macro to know how many lines to add code based upon blank or non blank lines?

Thanks,
Gary
 
L

Legacy 98055

Guest
VBA help...
Search IsEmpty function...
Remember that Cells are variants...
Tom
 

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592

ADVERTISEMENT

Hi again

The list method only works on an initial, you may have missed my amendment to my first post.
Easy alternative is to get it to return "zzzzz", which will sort to the bottom and can then be deleted.
If I can think of anything else I'll post it.

regards
Derek
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi gnrboyd:

I tried to duplicate your situation on a worksheet, and I could sort on column B without having to get entries with formulated blanks in column B on top of the list. If it will help to look at this, I will be glad to post a worksheet simulation.

Additionally, in response to your question, can you return some thing other than a blank from your VLOOKUP function ... answer is YES! as an example, I could use

=IF(A1<>"",VLOOKUP(A1,data,2,FALSE),99999)
so now I will be returning 99999 instead of a blank -- and if you use this method, with every thing else being the same in your approach, this strategy will bring the needed records to the bottom of the list where you want them.

Regards!
Yogi Anand
 

gnrboyd

Well-known Member
Joined
Jun 20, 2002
Messages
563

ADVERTISEMENT

Yogi,

Thanks so much for the reply.

I would be interested to see how you got that to work. My blanks ("") always sort at the top. As for your 99999 idea or zzzzz as mentioned by another member.... I know I could do that and delete the ones at the bottom but keep in mind I am trying to make this work as much like a program as possible rather than just a spreadsheet for other non-Excel users. I think I have a solution though. I am just adding a another operation after I retrieve data using the lookup. I simply set a filter on the entire list for blanks and then clear contents. I then release filters and sort. I am about to test it now but it should work. (that should word gets me sometimes....)

Thanks again for your ideas.

Gary
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi gnrboyd:

Regarding the sorting bit, see the following worksheet simulation, which shows that I have the records that retuned " " from the VLOOKUP formula are at the bottom.
y021106h1.xls
ABCDE
1a1Field1Field2
2c3a1
3 b2
4f6c3
5 d4
6g7e5
7f6f6
8e5g7
9 
10originaltable
11sortedtable
12
13a1Field1Field2
14c3a1
15e5b2
16f6c3
17f6d4
18g7e5
19 f6
20 g7
Sheet14
</SPAN>

Regards!
Yogi Anand

Edit: whether I returned " ", or "" from the VLOOKUP function, it really does not matter as far as the sort is concerned.
This message was edited by Yogi Anand on 2002-11-06 23:59
 

gnrboyd

Well-known Member
Joined
Jun 20, 2002
Messages
563
Yogi,

I was able to add the code to the macro to just clear the blank fields before sorting and it works fine. I looked at your example and that is pretty much what I have but mine still sorts with blanks at the end. Perhaps there is some type of setting we have in the program itself causing this. (I am using Excel 97 by the way)

Thanks for your assistance!

Gary
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi gnrboyd:

I am also using Excel97 for this post. In the Sort dialog box, try with
My list has ... No Header Row selected.

Regards!
Yogi Anand
 

Forum statistics

Threads
1,144,767
Messages
5,726,177
Members
422,660
Latest member
mrsteele

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top