How do I combine two columns

Question

I must be asking this question wrong, because I haven't found the answer and I can't believe this is a unique situation.

I am working with a sheet that has data extracted as CSV from another system. The data needs to be "cleaned up" before I can do some analysis. One of the issues I have is that the source data has two columns that do not have overlap/common data but that I need to combine into one column. I do not need to do anything more than copy the data from one column into the blank rows of the other.

Note: one important aspect: the order of the data must not change, as I need to put the combined column back into my main data set.

the file has over 600 lines, so manual copy/paste is cumbersome (would be okay if this were a one-time deal, but I need to do this at least monthly).

This is what my data looks like

this is what I want

What I've done to "workaround" an automatic solution is

1. Copy the columns into a new sheet
2. Add a third column which I then add values 1, 2, 3, 4, ...
3. I then resort the data, first by column A then by Column B
4. I then copy the contents from Column B to the bottom of Column A
5. I then resort based on my numeric values and copy the data back into the main sheet.

I cannot find a function or a way to automate this in Excel. Is it possible?

TIA!

Answer 1

Re:  combine columns

An alternative method...
1.  select the data in column A

2.  use 'Go To Special' to select blanks

3.  right-click a blank cell

4.  choose 'Delete' and 'Shift Cells Left'  [Edited]

'---
NLtL

https://1drv.ms/u/s!Au8Lyt79SOuhiXoNjAh-_-zLi49O

(the free 'Professional Compare' workbook has a "Clean Data" utility)

Answer 2

I am working with a sheet that has data extracted as CSV from another system.

I cannot find a function or a way to automate this in Excel. Is it possible?

That is really simple if you use Power Query to import the CSV file, because in Power Query we have a function to merge columns.

Andreas.

Answer 3

HI KarenGwynn,

I go through the post carefully, a simple solution could be combining the two column using formula and a helper column. In column C, put the formula in C1 and drag down it to auto fill the formula to other rows:

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

You should get combined column in helper column C, please note if a row contains nothing in both A and B column, you will get ZERO value in C column, if you prefer BLANK, you may change a display option for the worksheet at File>Options>Advanced>Display options for this worksheet>uncheck Show a zero in Cells that have zero value

Hope these can help.

Regards,

Clark

Answer 4

HI KarenGwynn,

Have you checked information above? Welcome to post back if you still need assistance.

Regards,

Clark