Merge different cell & O/P in first cell

[EC_08]

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>

 

[Peter_SSs]

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?

 

[EC_08]

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>

 

[Peter_SSs]

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

Excel Workbook
	A	B	C
1	Id	Address	O/P
2	OT001	A	ABCD
3		B
4		C
5		D
6	PTT001	F	FG
7		G
8	ABCD001	X	X
9	WXYZ001	P	PQ
10		Q
Sheet1

 

[EC_08]

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>

 

[Rick Rothstein]

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...

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

 

[Peter_SSs]

.. 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

 

[Peter_SSs]

Given the greater number of possible rows per ID, this is my macro attempt - please test in a copy of your workbook.

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

 

[Rick Rothstein]

Given the greater number of possible rows per ID, this is my macro attempt - please test in a copy of your workbook.

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.

 

[EC_08]

Thanks....

Peter_SSs & Rick Rothstein ......

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