That's true. I will use this shorter version of the Vlookup formula -
because as long as I'm going to be doing a search & replace anyway, I
don't need anything special to come back if it's 'false'. #N/A is just
as easy to work with as ///.

This has been a big help. Much appreciated.


Dave Peterson wrote:
There are a couple of other workarounds you could use, too:

If I return "" to a cell and later convert to values, I'll select that range
then Edit|replace
what: (leave blank)
with: $$$$$
replace all

And then do the reverse:
(with the range still selected)
edit|Replace
what: $$$$$
with: (leave blank)
replace all

=======
But in your case, I'd just use a formula like:
=VLOOKUP("COMPLETED",'Organize by Final Delivery Date'!A2:T2,20,FALSE)

And let the #n/a's be #n/a's.

Convert to values
Then Edit|replace
what: #n/a
with: (leave blank)
replace all

It might make your formulas re-evaluate a bit quicker (since it's only doing one
=vlookup()) and who cares if you're converting to values and then cleaning up.



wrote:

=IF(ISNA(VLOOKUP("COMPLETED",'Organize by Final Delivery
Date'!A2:T2,20,FALSE)),"///",VLOOKUP("COMPLETED",'Organize by Final
Delivery Date'!A2:T2,20,FALSE))

Okay, there's my workaround.

After searching the group (should've probably done that first :P), I've
discovered that the 'forumlas won't return true blanks' problem is a
common complaint about excel.

So I changed my Vlookup to return "///" if it's false, and then I'll do
a search/replace all and change every instance of "///" to a true blank
that way (after pasting/special/values to a new sheet of course).

It's an extra step in what I'd hoped would be a fully automated
process, but doing that little bit extra allows me to select all blanks
and delete them so the columns end up organized how I need them.

I'll take it. Thanks again for the advice!

wrote:
Thanks guys, I figured out what the problem is (though I could still
use some help fixing it!)

1) The source sheet (the one that contains all the data spread out over
different columns that I need to consolidate) is created by a series of
Vlookups.

2) What I am doing - once the formulas have retrieved all the correct
info - is highlighting/copying that page and doing a
paste/special/values onto a new sheet (so that the cells on this new
sheet contain the actual data, not the vlookup formula that retrieved
it).

3) On this NEW worksheet, I am trying to use the 'select only blanks'
tip that was kindly offered above.

4) The problem is this: when I paste/special/values onto a new
worksheet... even though the cells LOOK blank... excel does not
recognize those blank cells as truly empty unless I physically go over
each one and manually press delete.
I assume this is because the paste/special/values function is
putting some kind of invisible placeholder in those cells for which my
Vlookup returned a 'false'.

I imagine this will be fixable if I can change my Vlookup function to
return TRUE BLANKS when the logic test comes back false (I thought it
WAS returning blanks, but I guess it is populating the cell with a
'space').

Here is a sample of the Vlookup formula I am using. Can someone tell
me how to modify it so that it returns a TRUE BLANK in a 'false' cell -
instead of a placeholder space that cannot be autodeleted using the tip
provided above?:

=IF(ISNA(VLOOKUP("COMPLETED",'Organize by Final Delivery
Date'!A2:T2,20,FALSE)),"",VLOOKUP("COMPLETED",'Org anize by Final
Delivery Date'!A2:T2,20,FALSE))


THANKS AGAIN TO ALL! YOU ARE SAVING MY LIFE!!!! :)





Dave Peterson wrote:
It did work for me.

I'd try it once more.

And it worked for Dolphin (if that is his/her real name <bg).

wrote:

Thanks for the tip, but unfortunately it did not work. Excel
highlights only entire columns that have no data, but if there is data
in even just one cell in a column, it will not highlight the blank
spaces in that column to be deleted.

What I need is a way to delete the blank cells in the first X amount of
cells that precede the cells containing actual data. This 'X' number
will change from row to row (so in row 1, there could be only 4 blank
spaces to be deleted, but in row 2 there may be 20 blank spaces, and in
row 3 there may be 12, etc...). Eventually, no matter how many blanks
needed to be removed from each row, I want to end up with a sheet
listing data only in columns A-D.

The tip you provided only allows me to select large, contiguous areas
of uninterrupted blank cells when I really need to be able to select a
bizarre kind of mix-n-match.

I fear I am just making things more confusing the more I try to explain
myself. Does what I'm looking for make sense? Is it just not
possible? Thx.

Dave Peterson wrote:
Select all the columns that have something in them (or select the whole
worksheet if you want).

Edit|goto|special|check blanks
edit|delete|shift cells left

You may want to do it against a test worksheet--just in case that's not what you
really want.


wrote:

Hi!
Thanks in advance for any advice. I am a relative novice stuck at a
crossroads and really appreciate any help.

I've created a spreadsheet that spits out the information I need
organized in the following format:

. A - B - C - D - E - F - G - H - I - J - K - L .....
1 X X X X
2 X X X X
3 X X X X
4 X X X X
5 X X X X
6 X X X X
____________________________

This means that, on any given row, there are 4 (and only 4) columns
containing information - and the rest of the cells are blank.

Now I need to know how to consolidate this information so that it is
all contained in one set of columns (eg):

. A - B - C - D
1 X X X X
2 X X X X
3 X X X X
4 X X X X
5 X X X X
6 X X X X
_____________________________

Like I said, I have it set up so that, on every single row, there are
exactly 4 consecutive cells containing info. I have also made sure
that there will be no blank rows to deal with (every row WILL contain 4
cells of info). However, the placement of those 4 cells along the X
axis varies from row to row (sometimes it is in cells A-D, and
sometimes in cells CH-CK, etc).

In simple terms, I need to just end up with a sheet containing only 4
columns of data, no more.

I never realized how hard it was to explain a spreadsheet! Hopefully I
made some sense, and someone out there will have some advice. Let me
know if there are any details I need to add that I've forgotten. Thx
again!

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson