combine contents from a column

[rafi_07max]

http://www.iimmgg.com/image/7733575e22d33596233403a9c54cbd80
If we look at the image above, we can see that in column C “rp22” has been repeated 3 times and "q7" is also repeated 3 times.
What I want to do is to detect all the repeated contents in column C and combine them into one line.

So for e.g. for the content “rp22”, in the spreadsheet it should look like the image below,
http://www.iimmgg.com/image/35137055a3aceafa82792a45925aa07b

When combining, if one of the cells is empty, replace the empty cell content with “?”. Look at the image below
http://www.iimmgg.com/image/28c340242dd189d9beb0779f0c1cc002

I want to do this for the entire column C

And finally the image below shows how the worksheet should look like after the macro run successfully,
http://www.iimmgg.com/image/1454d896fa38c72594ac73b10279b614

I have also attached a sample worksheet
sheet 1 contains the initial texts
sheet 2 contains how the final outcome should look like
sheet 3 contain a button where the macro should be written
4901

[mohanvijay]

Try this

I attached solved file



Dim yess, yess2 As Boolean
Dim fi_ro As Long


yess = False
yess2 = False
fi_ro = 1

to_ro = Sheets(1).Cells(Rows.Count, 3).End(xlUp).Row

For i = 1 To to_ro

ch1 = LCase(Trim(Sheets(1).Cells(i, 3).Value))

For ii = 1 To to_ro

ch2 = LCase(Trim(Sheets(1).Cells(ii, 3).Value))

If ch1 = ch2 And i <> ii Then

yess = True
Exit For

End If

Next ii

If yess = True Then

For ch_al = 1 To to_ro

chs1 = LCase(Trim(Sheets(2).Cells(ch_al, 3).Value))
If chs1 = ch1 Then yess2 = True

Next ch_al

If yess2 = False Then

For filll = 1 To to_ro

fil_ro = LCase(Trim(Sheets(1).Cells(filll, 3).Value))

If ch1 = fil_ro Then

filcol2 = Sheets(1).Cells(filll, 2).Value

If Trim(Sheets(1).Cells(filll, 4).Value) <> "" Then
filcol4 = filcol4 & Sheets(1).Cells(filll, 4).Value & ","
Else
filcol4 = filcol4 & Sheets(1).Cells(filll, 4).Value
End If

If Trim(Sheets(1).Cells(filll, 5).Value) <> "" Then
filcol5 = Sheets(1).Cells(filll, 5).Value
End If

chkfil6 = Trim(Sheets(1).Cells(filll, 6).Value)
If chkfil6 = "" Then
filcol6 = filcol6 & "?" & ","
Else
filcol6 = filcol6 & Sheets(1).Cells(filll, 6).Value & ","
End If

End If

Next filll

Sheets(2).Cells(fi_ro, 2).Value = filcol2
Sheets(2).Cells(fi_ro, 3).Value = ch1
Sheets(2).Cells(fi_ro, 5).Value = filcol5
Sheets(2).Cells(fi_ro, 4).Value = Left(filcol4, Len(filcol4) - 1)
Sheets(2).Cells(fi_ro, 6).Value = Left(filcol6, Len(filcol6) - 1)

fi_ro = fi_ro + 1

End If

ElseIf yess = False Then

Sheets(2).Cells(fi_ro, 1).Value = Sheets(1).Cells(i, 1).Value
Sheets(2).Cells(fi_ro, 2).Value = Sheets(1).Cells(i, 2).Value
Sheets(2).Cells(fi_ro, 3).Value = ch1
Sheets(2).Cells(fi_ro, 4).Value = Sheets(1).Cells(i, 4).Value
Sheets(2).Cells(fi_ro, 5).Value = Sheets(1).Cells(i, 5).Value
Sheets(2).Cells(fi_ro, 6).Value = Sheets(1).Cells(i, 6).Value

fi_ro = fi_ro + 1

End If

filcol2 = ""
filcol4 = ""
filcol5 = ""
filcol6 = ""
yess = False
yess2 = False


Next i

[p45cal]

Not quite the same result as your sheet2 you have some question marks missing from D2,E2,D5 and E5 but I think it's what you want:

Private Sub CommandButton1_Click()
With Sheets("Sheet1")
Set xxx = Intersect(.UsedRange, .Columns(3))
' xxx.Select
For i = xxx.Cells.Count To 2 Step -1
Set mycell = xxx(i)
' mycell.Select
If Not IsEmpty(mycell) Then
If mycell.Value = mycell.Offset(-1).Value Then
For j = 1 To 3
If IsEmpty(mycell.Offset(, j).Value) Then mycell.Offset(, j).Value = "?"
mycell.Offset(-1, j).Value = mycell.Offset(-1, j).Value & IIf(IsEmpty(mycell.Offset(-1, j).Value), "?, ", ", ") & mycell.Offset(, j).Value
Next j
mycell.EntireRow.Delete
Else
For j = 1 To 3
If IsEmpty(mycell.Offset(, j).Value) Then mycell.Offset(, j).Value = "?"
Next j
End If
End If
If i = 2 Then 'this just put's question marks in the top row:
For j = 1 To 3
If IsEmpty(mycell.Offset(-1, j).Value) Then mycell.Offset(-1, j).Value = "?"
Next j
End If
Next i
End With
End Sub

Columns A and B just end up containing what the topmost row of a set contained.
It looks for rows with adjacent values in column C the same, so if there is another, say, rp22 in the sheet but not in the neighbouring rows of the existing rp22 rows it won't be included (sort first to overcome this).
For consistency, it adds question marks to rows without duplicates where there were blank cells.

[rafi_07max]

Thanks mohanvijay for your help. Your codes are working fine but it is not working properly if a content in column C is repeated more than 3 times. For e.g. the content "rp22" instead of repeating 3 times if it repeated 8 times than when combining it is not extracting all the information. Is it possible for you solve this problem?

Thanks p45cal for your help, but the way mohanvijay did was how i wanted my worksheet to look like. I apologize if my if my explanation was not clear. Actually your codes also working fine for me, just that is it possible for you to remove all the "?" in column D and E.. I only want "?" for the empty cells in column F. Take a look at the image below
http://www.iimmgg.com/image/28c340242dd189d9beb0779f0c1cc002

[mohanvijay]

As your requirement i write it now i attached workbook

in that "rp22" & "q7" repeated 8 times and the code does well if u need something else please post workbook that what u want and show where it didn't combining and where you want combine as detailed as your first post

[p45cal]

Thanks p45cal for your help, but the way mohanvijay did was how i wanted my worksheet to look like. I apologize if my if my explanation was not clear. Actually your codes also working fine for me, just that is it possible for you to remove all the "?" in column D and E.. I only want "?" for the empty cells in column F. Take a look at the image below
http://www.iimmgg.com/image/28c340242dd189d9beb0779f0c1cc002 See comments in code in the attached.
4911

[rafi_07max]

Sorry for the late reply..thanks to both of you, my program is working fine :)

[rafi_07max]

Hi p45cal,
I’m using your codes. Is it possible for you to do a slight editing to the codes.
I will explain what I need to do.

Your codes only works if the repeated contents in column C are in sequence.
Take a look at the image below:
http://www.imagehousing.com/imageupload.php?id=610812

But I want the codes to work even when the repeated contents in column C is not in sequence. Take a look at the image below:
http://www.imagehousing.com/imageupload.php?id=610811

I have attached a sample workbook with your codes and also sheet 1 contains the data that shows in the image
Sheet 2 shows how the data will look like after combining
5088

[p45cal]

Why not just sort the sheet first?
It shouldn't be difficult to add code to do that, but how should it be sorted? Sure, column C should be sorted, but should it have another (or two?) columns as primary sorting too?

[rafi_07max]

That worked too. Thanks p45cal for the advise :)