Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
merging cells and then deleting rows and duplicate entry
Hi,
the example below shows what i want to do. first last address city zip language john d 125 55 st ny 11111 spanish john d 125 55 st ny 11111 english john d 255 24 st ny 12444 spanish john d 255 24 st ny 12444 english and so on.... with different names and a person having maybe four address or knows 3 languages I want it to end up like this: first last address city zip language john d 1.125 55 st ny 1.11111 spanish,english 2.255 24 st 2.12444 in one row. The excel file is pretty big with many names. I found this code but it needs more so the same address for example would not show twice in a cell. r=1 do until cells(r,1)="" if cells(r+1,1)=cells(r,1) then cells(r,2)=cells(r,2) & "," & cells(r+1,2) rows(r+1).delete else r=r+1 end if loop Any thoughts would be apreciated. thanx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
merging cells and then deleting rows and duplicate entry
assume your table is A-F, add the headings are in row 1
also assume the data is sorted add two more columns key =A2 & "_" & B2 & "_" & E2 languages =IF(G2=G3,H3 & "," &F2,F2) the idea is to generate a unique key - the example uses firat & last name & zip the the laguages formula adds the language if the next rows key is identical. All you need to do is (1) for the languages column, make the formula into values VBA: Range("H:H").Value =Range("H:H").Value (2) loop to remove the unrequired rows VBA: Sub Fini****Off() Dim rw As Long For rw = Range("G1").End(xlDown).Row To 3 Step -1 If Cells(rw, "G") = Cells(rw - 1, "G") Then Rows(rw).Delete End If Next End Sub " wrote: Hi, the example below shows what i want to do. first last address city zip language john d 125 55 st ny 11111 spanish john d 125 55 st ny 11111 english john d 255 24 st ny 12444 spanish john d 255 24 st ny 12444 english and so on.... with different names and a person having maybe four address or knows 3 languages I want it to end up like this: first last address city zip language john d 1.125 55 st ny 1.11111 spanish,english 2.255 24 st 2.12444 in one row. The excel file is pretty big with many names. I found this code but it needs more so the same address for example would not show twice in a cell. r=1 do until cells(r,1)="" if cells(r+1,1)=cells(r,1) then cells(r,2)=cells(r,2) & "," & cells(r+1,2) rows(r+1).delete else r=r+1 end if loop Any thoughts would be apreciated. thanx |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
merging cells and then deleting rows and duplicate entry
Thanks for the feedback, but I'm kind of lost. Should I include the
following procedure? r=1 do until cells(r,1)="" if cells(r+1,1)=cells(r,1) then cells(r,2)=cells(r,2) & "," & cells(r+1,2) rows(r+1).delete else r=r+1 end if loop and also where should i put the following formula? Range("H:H").Value =Range("H:H").Value I think its getting somewhere but i feel sth is missing. Thanks again |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
merging cells and then deleting rows and duplicate entry
Any thoughts pleaseeeeeee?
thanx palikari wrote: Thanks for the feedback, but I'm kind of lost. Should I include the following procedure? r=1 do until cells(r,1)="" if cells(r+1,1)=cells(r,1) then cells(r,2)=cells(r,2) & "," & cells(r+1,2) rows(r+1).delete else r=r+1 end if loop and also where should i put the following formula? Range("H:H").Value =Range("H:H").Value I think its getting somewhere but i feel sth is missing. Thanks again |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging duplicate entries in Excel or deleting the duplicates (Exc | Excel Worksheet Functions | |||
Merging data in multiple rows where the first cell has duplicate d | Excel Discussion (Misc queries) | |||
merging information from partial duplicate rows | Excel Discussion (Misc queries) | |||
Deleting duplicate rows | Excel Discussion (Misc queries) | |||
Merging duplicate rows | Excel Programming |