Merge different cell & O/P in first cell

EC_08

New Member
Joined
Sep 4, 2013
Messages
39
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 AColumn BColumn C
IdAddressO/P
OT001 AABCD
B
C
D
PTT001FFG
G

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

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

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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?
 
Upvote 0
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 AColumn BColumn C
IdAddressO/P
OT001AABCD
B
C
D
PTT001FFG
G
COQ002JJ

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
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
 
Upvote 0
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....

IdAddressFormula O/PActual O/P
OT001AABCDABCDEGhI
B
C
D
E
G
h
I

<colgroup><col span="3"><col></colgroup><tbody>
</tbody>
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Thanks....

Peter_SSs & Rick Rothstein ......

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

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

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

Which adblocker are you using?

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
Back
Top