Help combining multiple columns into one

[joshuakaplan1]

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]

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.

 

[joshuakaplan1]

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.

 

[Guest]

Hi,

I did exactly what Dave said but starting with simple data, and it works. I
even a row that starts with data from A and end at D, then the next row
starts at Z and end at AC, and even some rows that are empty and it works
perfectly. You may want to try again, maybe with some simple data first to
make sure you do it correctly.

Regards,
Dolphin

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]

It did work for me.

I'd try it once more.

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

 

[joshuakaplan1]

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",'Organize by Final
Delivery Date'!A2:T2,20,FALSE))


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

 

[joshuakaplan1]

=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 ), 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!

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",'Organize by Final
Delivery Date'!A2:T2,20,FALSE))


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

 

[Dave Peterson]

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.

 

[joshuakaplan1]

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.