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

 

[Lilliabeth]

Is this what you are trying to do?

=TRIM(A1&A2)

What is a "hard output" number? Is that a metric thing, because I am in the US and I don't think we have those.



Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[Lilliabeth]

Geesh, my bad. I hope you know I meant something like
=TRIM(A1&B1)

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[remeng]

Nope, sorry. If there is a list and number outputs for A1 - A15, and A16 - A32000 are just formulas that output "", i would like to place A1-A15 in a column with a second column of values that also has the same issue. B1-B5 have number outputs, and B6-B32000 has a output of "".

Hope that helps.

 

[AnotherHiggins]

I am generating two columns of data through formulas and once they are calculated would like to combine these two columns ito one

You can just combine the formulas, you know.

Let's say that the first cell contains "=A1+B1" and the second cell contains "=A1*B1". Instead of doing both of those and then combining with an ampersand in a third cell, you can just do it in a single cell containing "=A1+B1 & A1*B1".

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[remeng]

Sorry, cannot combine the formulas to do this. They are not like values, but one set needs to follow the second so a third formula can be used.

 

[Lilliabeth]

I still don't have any confidence that I understand what you are trying to do, so you might want to post some sample results.

Or, maybe this is it:
=IF(OR(A1="",B1=""),"",A1&B1)

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[SkipVought]

Maybe a concrete example of the values in column A and column B and what you expect to see in column C, rather than everyone trying to guess what's in your head.

Skip,

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

 

[AnotherHiggins]

Wait - so you're going to have three columns, each with 32K formulas sitting there just in case you ever have that many items returned?

Have a look at Data > Import External Data. Much more efficient.

I'm with Lilliabeth - I'm not sure what you're talking about or why the offered suggestions won't work for you.

How about giving us the formulas in Columns A & B?

Tell us what you have and what you want instead of providing a riddle and then just saying, "Nope, won't work" without any explanation.



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

I agree, that it is EXTREMELY inefficient to have formulas that are essentially UNUSED.

I'd ditto MS Query, Data > Import External Data!

Skip,

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

 

[remeng]

To answer some questions: Yes it is very likely that I will need close to 32,000 rows in one column (when combined I will not exceed 64,000 rows, it will be close but never max out).

Column A Formula:

=IF(ISNUMBER(G1),IF(G1<HPR1,G1+SS,""),IF(NOT(G1=""),IF(ROW(G1)=1,0,SS),""))

Column B Formula:

=IF(ISNUMBER(H1),IF(H1>(-1*HNR1),H1-SS,""),IF(NOT(H1=""),(-1*SS),""))

Column A Outputs:

0
0.085
0.17
0.255
0.34
0.425
""
""
""

Column B Outputs:

-0.085
-0.17
-0.255
-0.34
-0.425
""
""
""

Combined Columns:

-0.085
-0.17
-0.255
-0.34
-0.425
0
0.085
0.17
0.255
0.34
0.425

 

[AnotherHiggins]

OK, I'm still confused. What do you mean by "combine"? Lill' and I were both thinking concatenate, but that isn't the case. Are you adding them? Some other mathematical function?

The third set of numbers, labeled, "Combined Columns" - will all of that be in a single column, say column C?

I don't get how 0.085 in column A and -0.17 in column B "combine" to wind up with -0.17 (each of these are the second number in their respective sets, I'm assuming that equates to Row 2).

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[remeng]

yes, the cobined column would be column C. A+B = C. So because A2:A32000 have the Column A Formula, and B2:B32000 has the B Formula, Column C is all numerical outputs for Columns A and B without the output values that equal "".

Column C:

-0.085 |
-0.17 |
-0.255 | B
-0.34 |
-0.425 |

0 |
0.085 |
0.17 |
0.255 | A
0.34 |
0.425 |

Hope this helps.

 

[remeng]

Just to make myself even more clear, I need to take Column A and add it to the bottom of Column B to produce column C. Column C will then have a separate formula applied to it.

 

[Lilliabeth]

Perhaps:
=B1 drag down real far
then in the next available cell in Col C, =A1 drag down real far

Then Copy, paste values
Then filter out blanks

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[mintjulep]

Why not simply a filter?

 

[remeng]

lilliabeth, love the answer, but because of the following operations and this being a automation tool, I don't think operators are going to like scrolling down 32,000 cells to find the end value.

mintjulep, I tried the filter operation with a macro and it bombed big time. Thank you for the idea.

I also tried to use paste special with values selected and for some reason excel places blanks into the cells. When you click on the cells there isn't a value or anything there.

Still need some help. BTW is there a value that I can use instead of "" to gain a true blank cell?

 

[Lilliabeth]

I don't think operators are going to like scrolling down 32,000 cells to find the end value

That is why paste values then filter out blanks.

BTW is there a value that I can use instead of "" to gain a true blank cell?

Are you wanting a formula to put in a cell to make that cell blank?

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[Lilliabeth]

I also tried to use paste special with values selected and for some reason excel places blanks into the cells. When you click on the cells there isn't a value or anything there.

It changes the "" formula results into blanks to be filtered out. I thought this is what you want. The values from B and A but without the ""'s.



Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[jdhilljr]

Will this work?

=SUM(IF(ISNUMBER(A1),A1,0)+IF(ISNUMBER(B1),B1,0))


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

Jim