ExcelBanter - Merge data of cells based on same ( value row wise)

I have a small issue with excel formula

=IF(AND(A2=A1,C2=C1),"Delete","")

I have different values in Col C.

What is the formula if i want to delete unwanted rows

COL A COLB
1 John Kim, Capcano Rd , 92821
1 Capcano Rd
1 92821
2 Peter, Mike
2 Mike
3 Mason Ko, 898934, california, Excellent, Yes
3 898934
3 california
3 Excellent
3 Yes
4 Tomorow
5 Welcome, Michael
5 Micael
6 Jonny, Based
6 based

COL A COLB
1 John Kim, Capcano Rd , 92821
2 Peter, Mike
3 Mason Ko, 898934, california, Excellent, Yes
4 Tomorow
5 Welcome, Michael
6 Jonny, Based

regards

Scott wrote:
It does have some limits, and some details I probably should have
mentioned, esp. if this your first try.

First, I strongly recommend (I should have before, but didn't think of
it) that you work with a copy of your original file, given you don't
have a lot of experience with macros.

Second, this starts at the top of Column A, and goes down the column
UNTIL it runs into a blank cell. So if you have a blank cell partway
down, this macro will stop running at that point. -- If there are
blank lines partway down, this function can be modified for different
criteria, just say so.

Third, it can be modified to delete rows, or alternatively mark the 4th
column so that you can put an autofilter on to delete the rows.

If you want to do this yourself, you could probably put this formula
starting in D2: =IF(AND(A2=A1,C2=C1),"Delete","") and copy it down the
whole column. Then (I usually Paste Special - Values) filter for the
rows with "Delete" in them, and delete them. Then you can delete your
extra column.

It is possible to put it in the macro as well. However, the macro to
delete the duplicate rows would run independent of the macro to combine
rows, so it's not important for them to be put together. If you want a
macro to do it, say so.

Hopefully that describes the areas where the issues cropped up.

Scott

wrote:
Problem,

this file is not working properly and give incomplete results on the
actual file. I guess it has some limitation which does not give correct
results.

Any advice

wrote:
Awesome!!!

Although I have zero info on macro/vb, but i have successfully run
this.
Is there any limit if i have more than 100 rows with same values.

One more favor i need.
How can I delete the whole row from which i copied the data.
or what if i want to change my original file to following

COL A COLB COL C
1 John Kim, Capcano Rd , 92821 89
2 Peter, Mike 2
3 Mason Ko, 898934, california, Excellent, Yes 23

Thanks for your help.

Scott wrote:
Sub Test()
Dim WS As Worksheet
Dim i As Long
Dim j As Long

Set WS = Worksheets("Sheet1")

i = 1
While WS.Cells(i, 1) < ""
Dim Temp As String
Temp = WS.Cells(i, 2)
j = i
While (WS.Cells(i, 1) = WS.Cells(j + 1, 1)) And _
(WS.Cells(i, 3) = WS.Cells(j + 1, 3))
j = j + 1
Temp = Temp & ", " & WS.Cells(j, 2)
Wend
WS.Cells(i, 2) = Temp
i = j + 1
Wend
End Sub

Scott
wrote:
Hi,

I have a problem,

I have an excel file . I want to merge those feild of COLOUMN B
which have same number in COLOUMN A.

E.G

COL A COLB COL C
1 John Kim 89
1 Capcano Rd 89
1 92821 89
2 Peter 2
2 Mike 2
3 Mason Ko 23
3 898934 23
3 california 23
3 Excellent 23
3 Yes 23

I want to convert this file to

COL A COLB COL C
1 John Kim, Capcano Rd , 92821 89
1 Capcano Rd 89
1 92821 89
2 Peter, Mike 2
2 Mike 2
3 Mason Ko, 898934, california, Excellent, Yes 23
3 898934 23
3 california 23
3 Excellent 23
3 Yes 23

Basically i want to merge data in col b, as long as it has same value
in col a.

If you can help me that would be great. I am computer operator and
have to do it hundred time manually.

Amin
TZE