CODEWITHSUNDEEP
excel
Ari
Ari

Reputation: 6159

Interlacing two columns together in excel

I'm trying to figure out how to interlace/merge two columns together:

how can I merge two columns of data into one like the following:

Name   Age    Age_1
========================
A       18       
B              45
C       31       
D       15         
E              62

into this

Name   Age    
============
A       18       
B       45      
C       31       
D       15         
E       62

In my case I have like maybe 3 - 4 similar columns and 200 rows

Upvotes: 0

Views: 1313

Answers (5)

prietosanti
prietosanti

Reputation: 327

You can use the CONCATENATE function

enter image description here

Edited

If you just only want to allow "Numbers" you can replace your function with this one:

=IFERROR(VALUE(CONCATENATE(R3:U3)),"")

enter image description here

IFERROR(): IFERROR(value, [value_if_error])

VALUE(): Converts a date/time/number string into a number

CONCATENATE(): CONCATENATE(string1, [string2, ...])

I hope I've been able to help you

Upvotes: 3

Karthick Ganesan
Karthick Ganesan

Reputation: 385

One of the simpler options is to,

  1. Select the data range in either of the 2 columns like below,

    fillupBlanksWithDataFromAdjacentColumn-1

  2. Post selecting the data range in one of the columns, select blank cells by pressing F5 fn key and then selecting 'Blanks' as shown in the image below,

    fillupBlanksWithDataFromAdjacentColumn-select-blank-cells

    This would be the resulting selection in your case,

    fillupBlanksWithDataFromAdjacentColumn-select-blank-cells-result

  3. You will now have to enter a formula (While all blank cells in the target column are selected by completing the previous step, the formula you enter now will initially be with reference to the cell which is currently in focus. The current cell in focus can be known from the Address bar)

    Start typing the formula by pressing the "=" sign as shown in the image below,

    fillupBlanksWithDataFromAdjacentColumn-enter-formula-in-blank-cell

    Now select the adjacent cell which contains the value that needs to be copied as shown in the image below

    fillupBlanksWithDataFromAdjacentColumn-enter-formula-in-blank-cell-select-copy-source-cell

  4. Once selected as shown in the above image, press Ctrl + Enter. Voila!

    fillupBlanksWithDataFromAdjacentColumn-enter-formula-in-blank-cell-select-paste-down-to-all-by-Ctrl+Enter

Hope this helps. Should be easier, as you try this in more real time scenarios.

Upvotes: 1

user11121185
user11121185

Reputation:

One option would be to merge the B:E cells in each row where B is blank then immediately unmerge them. This will have the effect of capturing the first value within C:E into column B. Range.SpecialCells can be used to reduce the work to rows where column B is blank.

dim blnk as range

with worksheets("sheet1")

    with .range(.cells(2, "B"), .cells(.rows.count,"A").end(xlup).offset(0, 1))

        for each blnk in .specialcells(xlcelltypeblanks)

            blnk.resize(1, 4).merge
            blnk.unmerge

        next blnk

    end with

end with

Upvotes: 0

Ricardo Diaz
Ricardo Diaz

Reputation: 5696

If you can add the resulting column, try this:

enter image description here

This would be the formula in cell D3 which you can copy downwards and customize the B3:C3 cells to include the other columns:

=TEXTJOIN("";TRUE;B3:C3)

Upvotes: 3

urdearboy
urdearboy

Reputation: 14580

VBA Solution

To implement: Open VBE > On Project Explorer > ThisWorkbook > Paste Code > Click Play (Green button up top)

Option Explicit

Sub Merger()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim Target As Range, LR As Long

LR = ws.Range("B" & ws.Rows.Count).End(xlUp).Row

For Each Target In ws.Range("B2:B" & LR)
    If Target = "" Then
        Target.Value = Target.Offset(0, 1).Value
    End If
Next Target

'If you want to delete Column C after
ws.Range("C1").EntireColumn.Delete

End Sub

Non-VBA Solution

Create a helper column (Column D) and place the following equation in D2

'If your columns may contain text
=IF(B2 = "", C2, B2)

'If your columns only contains positive numbers
=MAX(B2, C2)

Once you drop the equation down to the bottom, you will have a new column that is merged. You can paste as values and just delete your other columns or just reference your new column and leave the other two there for reference

Upvotes: 3

Related Questions