blanks and empty cells

[gnrboyd]

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!

 

[Derek]

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]

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

 

[Legacy 98055]

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

 

[Derek]

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]

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]

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]

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
	A	B	C	D	E
1	a	1		Field1	Field2
2	c	3		a	1
3				b	2
4	f	6		c	3
5				d	4
6	g	7		e	5
7	f	6		f	6
8	e	5		g	7
9
10				originaltable
11				sortedtable
12
13	a	1		Field1	Field2
14	c	3		a	1
15	e	5		b	2
16	f	6		c	3
17	f	6		d	4
18	g	7		e	5
19				f	6
20				g	7
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]

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]

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