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

[tarone]

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

 

[Scott]

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

 

[tarone]

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.

 

[tarone]

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

 

[Scott]

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

 

[tarone]

Thanks, I have successfully use it.

But I have one more issue

I have two files

file a
Mike 18
John 76
Peter 56
Dad 46
Mom 34

file b
Mike 18
Nancy
Dad 45
Ricardo 74
Hose 41
Mom
68C 12
015 43

I want to make one file and remove duplicates
Any formula for that.

Thanks again

 

[lstar50]

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]

Sorry... I wasn't very clear on that. It's not a formula to delete...
it's a formula to mark what needs to be deleted. The deletion process
has to be handled separately.

Generally, after I've marked all my rows as Delete/Keep, I use the
AutoFilter (Data -> Filter -> AutoFilter) and filter for "Delete".
Then I select all those rows and manually delete them.

Scott

 

[Scott]

There are lots of ways... I tend to use a different method each time
depending on my mood.

One way:

Put all the lines in a blank worksheet in Columns A & B (It's best if
you have a header row, ie. A1 says Name, A2 says Age (or whatever).
Then, sort them. Then use a similar marking formula to above in Column
C, starting in C2:

=IF(AND(A2=A1,B2=B1),"Mark for Delete","") and copy down.

Then use the AutoFilter, Filter for "Mark for Delete", select all the
rows and delete them. Then copy columns A & B where you need them.

Another way:

Put all the lines in a blank worksheet in Columns A & B (It's best if
you have a header row, ie. A1 says Name, A2 says Age (or whatever).
At this point, the method changes. Select Data -> Filter ->
AdvancedFilter. (This works quite a bit differently than the
AutoFilter).

Specify the following criteria for the Advanced Filter:
1) Check the radio button beside "Copy to another location"
2) List Range should be your full range, ie. A1:B5000 (or whatever)
3) Copy to should be D1.
4) Check the box "Unique records only".
5) Hit OK.

At that point, copy the data in Column D & E to wherever you need them.


There are other ways, but these are the two I use the most to delete
duplicated lines.

Scott

 

[tarone]

Thanks for your response.

The reason why this deletion process is not working becuase I have not
same values in col C. So I have deleted the whole c colmn and applied
following formula to this file

If you see following file, you can notice the difference

Available File
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

I want to have following file

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

The deletion process give me wrong result as follows

1 92821
2 Mike
3 yes
4 Tomorow
5 Micael
6 base

Any advice

 

[Scott]

Insert a column before column A.

Put this formula in A2: =IF(B2=B1,"Delete","") and drag down. If you
put this formula in A1, you'll get the result you got before, so make
sure it's in A2.

Then do the filter and delete the extra column.

Sorry about the confusion... sometimes i don't think things through.

Scott

 

[tarone]

Thanks

For some reason both are not working if i have blank cell in col 2

Name Age
Ahmed 34
Ahmed Mark for Delete
Ahmed (THIS SHOULD ALSO BE MARKED)>>
amin 35
di 23
jo 89
khey 56
khey (THIS SHOULD ALSO BE
MARKED)>>
shak 12 Mark for Delete
shak 12
zahid 78

Any advice
thanks again

 

[lancastergeneral]

i need little help
i have small table as below. when i enter a date on one cell. progra
compare that date with date in coloum 1, if found same date than cop
matching colums row in to another sheet. if matching date not foun
then give error msg.

DATE[/B] * P1* *P2* *P3* *P4* P
12/12/2006 1 2 3 4 5
12/13/2006 2 3 4 5 1
12/14/2006 3 4 5 1 2
12/15/2006 4 5 1 2 3
12/16/2006 5 1 2 3