Is this asking too much of a macro

[dovaka]

Im trying to get these tasks accomplished in excel but I think I am asking to much of a macro. I know some of these things are easy but others are kicking my butt. But im sure someone out there knows better then me about it. I tried to explain it as well as I could but feel free to ask any follow up questions.

sample file
https://www.dropbox.com/s/km28pniwi5al79p/mr excel.csv?dl=0

[FONT=&quot]1. Have it save all these columns and delete all others.[/FONT]
[FONT=&quot]Contact ID, Contact Type, Source, Primary FirstName, Primary LastName, Company, Primary Birthday, Email Address, Home Phone, Bus Phone, Mobile Phone, Contact Notes, House Number, Street, Street Designator, Suite No, City, State, Zip, [/FONT]

[FONT=&quot]2. merge the data from “House Number, Street, Street Designator” into a single cell called “address[/FONT]

[FONT=&quot]3. There are places where there is a line break or carriage return in a single cell. These need to be removed and converted to something like a * so that the cell ends up being one single line[/FONT]

[FONT=&quot]4. In Field “Zip” please force a 5 digit number. Where we are all our zip codes start with a 0 zip excel removes it. Set it so that if the zip is a 4 digit number that it will put a 0 at the beginning[/FONT]

[FONT=&quot]5. Field “Contact ID” is the identifier of each person. The number in this field is duplicated several times for some people because of the way the export handles certain notes for each user. Most of the data is the same between two rows with the same ID, the main row has data in the “Contact, Source” fields and the other rows that have the same “Contact ID” are the notes. I only need the addresses from the secondary rows moved into the “Notes” field of the primary row[/FONT]

 

[mole999]

record each step, then add it together. First view it should all be achievable

 

[Fluff]

This should get you started

Sub CleanUpData()

   Dim usdRws As Long
   
   usdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

   Range("D:D,G:N,P:P,S:W,Y:Z,AB:AJ,AL:AT,AW:CI,CK:CK,CN:CN,CP:CQ,CS:CS,CV:IK").Delete
   With Range("P2:P" & usdRws)
      .Value = Evaluate(.Offset(, -2).Address & " & "" "" & " & .Offset(, -1).Address & "& "" "" & " & .Address)
      .Value = Application.trim(.Value)
   End With
   Range("P1").Value = "Address"
   Range("N:O").Delete
   Range("R:R").NumberFormat = "00000"
End Sub

Can you point out where some of the line breaks are?
On point 5 the contact notes already contain the address

 

[dovaka]

This should get you started

Sub CleanUpData()

   Dim usdRws As Long
   
   usdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

   Range("D:D,G:N,P:P,S:W,Y:Z,AB:AJ,AL:AT,AW:CI,CK:CK,CN:CN,CP:CQ,CS:CS,CV:IK").Delete
   With Range("P2:P" & usdRws)
      .Value = Evaluate(.Offset(, -2).Address & " & "" "" & " & .Offset(, -1).Address & "& "" "" & " & .Address)
      .Value = Application.trim(.Value)
   End With
   Range("P1").Value = "Address"
   Range("N:O").Delete
   Range("R:R").NumberFormat = "00000"
End Sub

Can you point out where some of the line breaks are?
On point 5 the contact notes already contain the address

There mostly in the same spots, columns AV, BH, BI, BK, BL have them but only AV survives the first step. There are a few other columns but I think AV is the only one that has them that doesnt get deleted.

 

[Fluff]

In order to find what the character is, so that it can be replaced, I need to know a specific place in one of the cells where it exists.

 

[dovaka]

4/av

 

[dovaka]

but there is no character its just a line break/carriage return

 

[Fluff]

It's still a "character" even though you cant see it.

 

[dovaka]

On point 5 the contact notes already contain the address

I missed this part. Each subsequent entry that has the same ID number is a different address. Thats the only thing that im trying to preserve

 

[Fluff]

Think it might be a character 13. Try this

Sub CleanUpData()

   Dim usdRws As Long
   
   usdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

   Range("D:D,G:N,P:P,S:W,Y:Z,AB:AJ,AL:AT,AW:CI,CK:CK,CN:CN,CP:CQ,CS:CS,CV:IK").Delete
   With Range("P2:P" & usdRws)
      .Value = Evaluate(.Offset(, -2).Address & " & "" "" & " & .Offset(, -1).Address & "& "" "" & " & .Address)
      .Value = Application.trim(.Value)
   End With
   Range("P1").Value = "Address"
   Range("N:O").Delete
   Range("R:R").NumberFormat = "00000"
   Cells.Replace Chr(13), " ", xlPart, , , , False, False
End Sub