merge Columns

[xltest]

Hi,

I need to merge 2 columns.
Column A have users name and column B has users + passwords
I know how to split the fields - but what i am trying to do is
to merge column A into column B without deleting the content of column B.
Any suggestions?

Appreciate your help.

 

[boblarson]

Does this have to be done programmatically or can it be done manually?

If manually, you can just use the CONCATENATE formula to concatenate column A with Column B and then autofill down. Then select column C and copy, then paste special (values only) into column B.

 

[xltest]

Does this have to be done programmatically or can it be done manually?

If manually, you can just use the CONCATENATE formula to concatenate column A with Column B and then autofill down. Then select column C and copy, then paste special (values only) into column B.

This can be done manually - i dont know how to do it i am xl guy...

 

[boblarson]

I didn't explicitly say, but the concatenation would occur in Column C (hence the part that I wrote about copying column C to Column B) and then once done you could delete column C.

 

[xltest]

Let me explain again - ok i can use CONCATENATE formula - i know how - but the problem is that i wish to keep the records of column A as well as of column B - what happen with the CONCATENATE formula is that it is deleting the B fields when i am trying to use it.
Imagine whati am doing - column A has fields value - column B has fields value - i need to run a fomula that will merger column A into colum B without deleting column B because it is thousands of records...

Thanks

 

[boblarson]

Okay, this is what I think you are trying to do (let me know if I am incorrect):

1. You have a column A with values
2. You have a column B with values
3. You need them concatenated together, but need to retain column A in an unchanged state and column B will wind up being the concatenated value of A and B together. (for example you have column A with the words "Temporary Listing" and in column B you have "2502" and you want column a to stay at "Temporary Listing" and column B to become "Temporary Listing 2502."

If that is correct, then as I said, use the concatenate formula in a DIFFERENT COLUMN (NOT A or B, but maybe column C - or whatever column is right next to the data but doesn't have data currently in it) and then use the AutoFill (double-clicking on the little black square if you put your mouse over the currently highlighted cell(the one with your first concatenation in it) to fill the values all the way down the column.
Next, copy that column by selecting the entire column and clicking the copy button. Then, select column B and go to Edit > Paste Special and select VALUES from the checkboxes available and click OK. It will then paste the concatenated values over Column B and the result will be that Column B will have the concatenated values. You can then delete the column you used the concatenate formula in.

 

[xltest]

Well let me explain simple what i need to do:
1- Cloumn A has data
2- Column B has data
I want to copy the data from column A to column B where in column B the value is Null (i.e. empty field) so for example - in column A i have "Jhon" and in column B the correspond filed is empty - i want to copy from field to filed but without deleting the B content (because some fields in B has content) and it is more than 60k records so i want a formula (if there is?) that will help me to copy from A to B
Hope this make things clear.

Appriciate your help

 

[shades]

You will need another column, say C, to use the formula.

In C2 put this formula:

=A2&B2

If you need a space between them, then

=A2&" "&B2

Only other way to achieve it without the use of a third column is to use VBA.
________
Mflb

 

[john471]

xltest,

I realise you probably believe you are being clear, but your situation/explanation(s) still leave me a little confused.

Here is what I think I understand:

1) You are working in Excel (and not MS Access)
2) You have 60K + rows of data on which you want to expediently perform a one-off manipulation.
3) The data of interest/use is in two columns, A & B.
4) Some rows have data in both column A and column B.
5) Some rows have data in column A only (column B is empty).
6) All rows have data in column A.
7) For rows that do have data in column B, you do not want to change any data in that row.
8) For rows that have column B empty you would like to duplicate the value from Column A into column B
9) Column B does not contain any formulas (only values or blank/empty cells).

Are ALL of the above statements correct ? If any is not correct, please advise, which one(s), and what the correction would be.

If they are ALL correct, the following should work:-

A) Pick an empty column. I'll assume you've picked C.

B) In Column C place the following formula

=IF(OR(ISBLANK(B2),TRIM((CLEAN(B2)))=""),A2,B2)

if you were certain that the target cells in B are empty and don't, for example, contain spaces or carriage retruns or other spurious non-printing characters, a slightly simpler formula would work - the above formula caters for the possibility of spaces, carriage returns, etc. being present which may appear like an empty cell, when it is not in fact empty. If such characters might exist and retaining them is desireable, then use the below formula instead.

=IF(ISBLANK(B1),A1,B1)

C) Copy it and paste it down the entire column (using whatever method you find most convenient)

D) Select Column C and Copy it

E) Select column B

F) From the edit menu, use "Paste Special" and choose "Values" to paste the result of the formula into column B.

G) Delete column C

Hope This helps

Regards

John.

 

[boblarson]

and if that explanation doesn't do it for you I guess we'll have to shoot for screenshots next.

 

[xltest]

Thanks a lot!!! That works perfect!

Thanks a lot guys!!! That works perfect!

 

[xltest]

Thanks a lot!!! That works perfect!

xltest,

I realise you probably believe you are being clear, but your situation/explanation(s) still leave me a little confused.

Here is what I think I understand:

1) You are working in Excel (and not MS Access)
2) You have 60K + rows of data on which you want to expediently perform a one-off manipulation.
3) The data of interest/use is in two columns, A & B.
4) Some rows have data in both column A and column B.
5) Some rows have data in column A only (column B is empty).
6) All rows have data in column A.
7) For rows that do have data in column B, you do not want to change any data in that row.
8) For rows that have column B empty you would like to duplicate the value from Column A into column B
9) Column B does not contain any formulas (only values or blank/empty cells).

Are ALL of the above statements correct ? If any is not correct, please advise, which one(s), and what the correction would be.

If they are ALL correct, the following should work:-

A) Pick an empty column. I'll assume you've picked C.

B) In Column C place the following formula

=IF(OR(ISBLANK(B2),TRIM((CLEAN(B2)))=""),A2,B2)

if you were certain that the target cells in B are empty and don't, for example, contain spaces or carriage retruns or other spurious non-printing characters, a slightly simpler formula would work - the above formula caters for the possibility of spaces, carriage returns, etc. being present which may appear like an empty cell, when it is not in fact empty. If such characters might exist and retaining them is desireable, then use the below formula instead.

=IF(ISBLANK(B1),A1,B1)

C) Copy it and paste it down the entire column (using whatever method you find most convenient)

D) Select Column C and Copy it

E) Select column B

F) From the edit menu, use "Paste Special" and choose "Values" to paste the result of the formula into column B.

G) Delete column C

Hope This helps

Regards

John.

Thanks a lot!!! That works perfect!

 

[john471]

You are welcome, xltest. Thank you for taking the time to reply back.

 

[aquila87]

Dear Access gurus I have a similar problem with Access (I'm new for using it) - I have exported an excel sheet into Access, which I need to make as a report. The information in Excel file is: first 60 rows merged cells with text, after that 5 rows table and another 20 rows with merged cells again with text. I need to do the same in Access (merge the rows with text). Can somebody help me with it?
Thank you so much in advance!