View Full Version : [SOLVED:] combine contents from a column
rafi_07max
11-15-2010, 06:05 AM
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
11-15-2010, 08:12 AM
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
11-15-2010, 08:31 AM
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
11-15-2010, 07:01 PM
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
11-15-2010, 10:10 PM
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
11-16-2010, 05:02 AM
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
11-18-2010, 05:42 AM
Sorry for the late reply..thanks to both of you, my program is working fine :)
rafi_07max
12-14-2010, 07:24 AM
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
12-14-2010, 07:53 AM
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
12-14-2010, 06:26 PM
That worked too. Thanks p45cal for the advise :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.