combine contents from a column

[rafi_07max]

http://www.iimmgg.com/image/7733575e...3403a9c54cbd80
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/35137055...792a45925aa07b

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/28c34024...b0779f0c1cc002

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/1454d896...ac73b10279b614

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
Attachment 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/28c34024...b0779f0c1cc002

[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/28c34024...b0779f0c1cc002

See comments in code in the attached.
Attachment 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
Attachment 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