Merge 2 Columns to 1 with formula

[remeng]

Hi all,

I am generating two columns of data through formulas and once they are calculated would like to combine these two columns ito one, but I do not want any blank "" values, just the hard output numbers. Is there a way to do this with a formula instead of VB? If so, how can I go about doing it?

If there isn't what way may I do it with VB?

Thanks,

remeng

 

[jdhilljr]

I've looked a little closer at the posts. Are you trying to get totals at the end of this list and what was the problem with the filter where cell <> ""?

If you can't stand behind your troops, stand in front of them.
Semper Fidelis

Jim

 

[SkipVought]

use MS Query in a UNION join. But you have to get rid of the formulas that are outside of the actual data area.

This Join could be done in a matter of seconds and be easily refreshed at any time.

faq68-5829

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[remeng]

I'll look into everyones solutions tommorow, work is over for today. Thank you everyone for taking the time to look through this.

 

[remeng]

Ok,

So here is what is going on. I tried to use the "" to produce a blank cell. "" actually places "blank text" whatever that really means. The outcome of using "" is that the cell appears blank, but infact has a hidden character that holds no value. This means that if I were to export the data to an outside file I can not see it, but in excel, there is a "place holder" for a better term. This place holder is copied to other cells when I use the copy and paste special with values selected and skip blanks. if you try the formula =if(A1=5,true,"") and fill down, then, copy paste special to say Column C, it appears to contain blank cells, but if you select the top cell that you pasted to, double click the bottom edge of that cell, Excel will jump to the end of your paste area. This is also a way to go to the end of a list of filled cells. This is how I know that it is not actually skipping the blanks. I know this can be confusing so let me know if you have any questions.

 

[SkipVought]

NOT a good workbook design!

Use the Data > List feature to PROPOGATE the formulas, in adjacent columns to the right, the the next row when data is entered.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[remeng]

Skip,

Could you please give an example of how this might work and how to go about doing this? Thanks.

 

[SkipVought]

Search in Excel HELP on List

Create an Excel list

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[remeng]

I still do not understand how this is going to resolve the problem that I have other than to allow me to order something that is already in the order I want. Is this to resolve the paste special issue because I am very confused. Thanks Skip for the help

 

[SkipVought]

This is to resolve the unused formula issue, A16 - A32000 are just formulas that output "" Inefficient AND counter productive.

Your other issue is simply solved using MS Query, as stated above.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Lilliabeth]

after doing the paste special, select one of the "blank" cells and click in the formula bar... see... no characters. You say the end cell gets jumped to because it is the end of the paste range yet you seem to think this somehow proves the cells have characters in them.

Better yet, why won't you (after doing the paste special) turn on autofilter, use the drop-down and choose to filter out blanks. Please try it before you post with why that won't work.

--Lilliabeth

 

[remeng]

let me ask another question to try and see if I can go down the auto filter path farther. How can I autofilter the results and take all non-blank values and copy them into another column by using a macro. As a reminder Column A and B will have different total values in them.

Lillabeth, I have tried everything that has been suggested except MS Query with the List option (still working on that). I know things will not work because I have tried them prior to my origional post. I know that it seems like I am shooting stuff down, but I have done allot of other work with this application that tries many of these things. I have retried everything that you have suggested to verify that they will not work.

Please remember that this is an automated tool for operators to use and the more operations that can be automated the less problems that will occur later.

Thank you for the help, this isn't something that I expect to just work. I have worked on this for over 3 weeks. It may take allot longer. Please keep the suggestions coming they do help.

 

[SkipVought]

MS Query

Gives ONE Column of combined values...

Select [ColumnAFieldName] 
From [YourSheetName$]
Where [ColumnAFieldName]  Is Not Null
UNION
Select [ColumnBFieldName] 
From [YourSheetName$]
Where [ColumnBFieldName]  Is Not Null

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[remeng]

Hi all,

I resolved the issue with a simple vlookup. I had to had some extra formulas to an additional column, but it seems to be working now. Thank you all for the help and ideas. Sometimes you need to go left to get back to the right answer.