# Merge different cell & O/P in first cell

#### EC_08

##### New Member
Hi,

I have 15000 raw data as per below , Column A contain different id there home address split in column b (A,B,C,D) i want output in first cell of C column (ABCD) agints each id.

 Column A Column B Column C Id Address O/P OT001 A ABCD B C D PTT001 F FG G

<colgroup><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col width="64" span="3" style="width:48pt"></colgroup><tbody>
</tbody>

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
1. Will there always be at least two rows for each id, or could there be a single row?

2. Is a macro approach okay?

1. Will there always be at least two rows for each id, or could there be a single row?

2. Is a macro approach okay?

1> no may be address in column B , only single raw or may be split in 3 to 4 raw
2> Macro is ok but i want formula base solution ( if possible).

 Column A Column B Column C Id Address O/P OT001 A ABCD B C D PTT001 F FG G COQ002 J J

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

OK, if a formula is preferred and there is a maximum of 4 rows per ID, then try this formula copied down.

Excel Workbook
ABC
1IdAddressO/P
2OT001AABCD
3B
4C
5D
6PTT001FFG
7G
8ABCD001XX
9WXYZ001PPQ
10Q
Sheet1

ok Done, But my problem is I don't know my address in column B split in how many cell .

In your formula is my address split in 5 to 7 or may be in 10 cell then what to do....

 Id Address Formula O/P Actual O/P OT001 A ABCD ABCDEGhI B C D E G h I

<colgroup><col span="3"><col></colgroup><tbody>
</tbody>

With the variability your are describing, I do not see how a formula solution is possible, so here is a macro that should do what you want...
Code:
``````Sub CombineAddresses()
Dim X As Variant, LastRow As Long, Blanks As Range, Ar As Range
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("C2:C" & Rows.Count).Clear
On Error GoTo NoBlanks
Set Blanks = Range("A1:A" & LastRow).SpecialCells(xlBlanks)
For Each Ar In Blanks.Areas
Ar(1).Offset(-1, 2) = Join(Application.Transpose(Ar(1).Offset(-1, 1).Resize(Ar.Count + 1)), "")
X = Ar(1).Offset(-2).Row
Do While Len(Cells(X, "A").Value) > 0 And X > 1
Cells(X, "C").Value = Cells(X, "B").Value
X = X - 1
Loop
Next
If Len(Cells(LastRow, "A").Value) Then
Cells(LastRow, "C").Value = Cells(LastRow, "B").Value
X = LastRow - 1
Do While Len(Cells(X, "A").Value) > 0 And X > 1
Cells(X, "C").Value = Cells(X, "B").Value
X = X - 1
Loop
End If
Exit Sub
NoBlanks:
Range("C2:C" & LastRow).Value = Range("B2:B" & LastRow).Value
End Sub``````

Last edited:
.. my address split in 5 to 7 or may be in 10
I only suggested a formula approach, because you previously stated only 3 to 4.
... only single raw or may be split in 3 to 4 raw

Given the greater number of possible rows per ID, this is my macro attempt - please test in a copy of your workbook.
Rich (BB code):
``````Sub MergeCellValues()
Dim a, b
Dim i As Long, k As Long

a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
ReDim b(1 To UBound(a, 1), 1 To 1)
For i = 1 To UBound(a, 1)
If a(i, 1) = vbNullString Then
b(k, 1) = b(k, 1) & a(i, 2)
Else
k = i
b(k, 1) = a(i, 2)
End If
Next i
Range("C2").Resize(UBound(a, 1)).Value = b
End Sub``````

Given the greater number of possible rows per ID, this is my macro attempt - please test in a copy of your workbook.
Rich (BB code):
``````Sub MergeCellValues()
Dim a, b
Dim i As Long, k As Long

a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
ReDim b(1 To UBound(a, 1), 1 To 1)
For i = 1 To UBound(a, 1)
If a(i, 1) = vbNullString Then
b(k, 1) = b(k, 1) & a(i, 2)
Else
k = i
b(k, 1) = a(i, 2)
End If
Next i
Range("C2").Resize(UBound(a, 1)).Value = b
End Sub``````

That is much better than my attempt.

Thanks....

Peter_SSs & Rick Rothstein ......

its working .. but if possible formula base soluation. pls.

Replies
8
Views
180
Replies
5
Views
88
Replies
0
Views
552
Replies
1
Views
336
Replies
5
Views
458

Threads
1,219,914
Messages
6,150,937
Members
450,994
Latest member
MacOrch

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back