Hello excel 'top experts' :)

Another challange on my desk (and something to learn about combine data).
Hopely somebody can help me out.

I've got a sheet with column A till CH
The sheet has several duplicates in rows because in the columns M - BJ and BP - CH there is data showed in one of the columns but in a separated row.

For example

Column M N O P
Row1 a
Row2 b
Row3 c
Row4 d

That should be

Column M N O P
Row1 a b c d

But,.... there are also rows that don't need to combine.
For make the difference in yes/no to combine and till what row I made some codes in column A. In column B is a key registered which reflects a combined created unique value to show which rows belong to each other.

Column A B
Row1 M Key
Row2 F Key
Row3 F Key
Row4 B Key
Row5 U Key
Row6 B Key
Row7 B Key
Row8 B Key
Row9 M Key
Row10 F Key
Row11 F Key
Row12 F Key
Row13 B Key

A macro should be very helpfull :)

It should combine in the row with code M all the values registered in the the row with M itself and then together with the values in the below rows where F is the code in column A and in column B the key is the same.

So in row 1 you get the registered data from row1 & row2 & row3
in row9 you get the registered data from row9 & row10 & row11 & row12

This should be done only for columns M till BJ, and BP till CH.

On the end I can delete the rows with F in column A.

regards, Johan

Hi Johan,

Am Sat, 20 Jul 2019 01:03:44 -0700 (PDT) schrieb JS SL:

I've got a sheet with column A till CH
The sheet has several duplicates in rows because in the columns M - BJ and BP - CH there is data showed in one of the columns but in a separated row.

For example

Column M N O P
Row1 a
Row2 b
Row3 c
Row4 d

That should be

Column M N O P
Row1 a b c d

But,.... there are also rows that don't need to combine.
For make the difference in yes/no to combine and till what row I made some codes in column A. In column B is a key registered which reflects a combined created unique value to show which rows belong to each other.

Column A B
Row1 M Key
Row2 F Key
Row3 F Key
Row4 B Key
Row5 U Key
Row6 B Key
Row7 B Key
Row8 B Key
Row9 M Key
Row10 F Key
Row11 F Key
Row12 F Key
Row13 B Key

It should combine in the row with code M all the values registered in the the row with M itself and then together with the values in the below rows where F is the code in column A and in column B the key is the same.

So in row 1 you get the registered data from row1 & row2 & row3
in row9 you get the registered data from row9 & row10 & row11 & row12

This should be done only for columns M till BJ, and BP till CH.

try:

Sub Test()
Dim LRow As Long, i As Long, Last As Long
Dim c As Range, myRng1 As Range, myRng2 As Range
Dim FirstAddr As String

With ActiveSheet
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set c = .Range("A:A").Find("M", LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddr = c.Address
Do
For i = c.Row + 1 To LRow
If .Cells(i, "A") = "F" And .Cells(i, "B") = .Cells(i - 1, "B") Then
Else
Last = i - 1
Exit For
End If
Next
Set myRng1 = .Range(.Cells(c.Row, "M"), .Cells(Last, "BJ"))
Set myRng2 = .Range(.Cells(c.Row, "BP"), .Cells(Last, "CH"))
.Cells(c.Row, "M") = Application.TextJoin("", 1, myRng1)
.Cells(c.Row, "BP") = Application.TextJoin("", 1, myRng2)
Set c = .Range("A:A").FindNext(c)
Loop While Not c Is Nothing And c.Address < FirstAddr
End If
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Hello Claus,

Thanks. I think my explanation was in the good direction but.... not good enough. My misstake :( (sorry).
It combines now the text from the columns into cell M (M till BJ) or BP (BO till CH) instead of it combines for each column separated the text in the same column but then from row with M+key till F+key.

regards, Johan

Hi Johan,

Am Sat, 20 Jul 2019 22:14:34 -0700 (PDT) schrieb JS SL:

Thanks. I think my explanation was in the good direction but.... not good enough. My misstake :( (sorry).
It combines now the text from the columns into cell M (M till BJ) or BP (BO till CH) instead of it combines for each column separated the text in the same column but then from row with M+key till F+key.

then try:

Sub ConcatF()
Dim LRow As Long
Dim myRng1 As Range, myRng2 As Range, myCol As Range, c As Range
Dim myCnt As Integer, i As Integer
Dim FirstAddr As String

With ActiveSheet
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set c = .Range("A1:A" & LRow).Find("M", LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddr = c.Address
Do
myCnt = Application.CountIfs(.Range("B1:B" & LRow), c.Offset(, 1), .Range("A1:A" & LRow), "F") + 1
Set myRng1 = .Cells(c.Row, "M").Resize(myCnt, 49)
Set myRng2 = .Cells(c.Row, "BO").Resize(myCnt, 19)
For Each myCol In myRng1.Columns
.Cells(c.Row, myCol.Column).Value = Application.TextJoin("", 1, myCol)
Next
For Each myCol In myRng2.Columns
.Cells(c.Row, myCol.Column).Value = Application.TextJoin("", 1, myCol)
Next
Set c = .Range("A1:A" & LRow).FindNext(c)
Loop While Not c Is Nothing And c.Address < FirstAddr
End If
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Claus,

Thanks!
Perfect.

regards, Johan