How to Dim and Win

[amerifax]

Copy a formula from J to J281977. Right now I hold the shift key while I take the scroll bar and drag it all down to get as close to the position as possible. From there, I use the up-and-down arrow trying to get to the cell while I am still holding the shift key down and then click on the cell then release and do my Control V (Paste).

I have three side-by-side cells, where I need to do the same propagation. Every day is a new day and the amount of records changes.

I also have absolutely no experience with a dim file. I wonder if there be anybody that might be willing to show me what it will take to solve this problem and at the same time. I get experience on how a dim file can work for me. I wouldn't even know how to execute it. Sad as it seems. I am fairly proficient with a MacroExpress Pro, macro builder, which uses variables fairly extensively. There was a day when I used macro's extensively in a program called Quattro Pro. I also used a program my Lotus called Symphony. I really had some slick macro's. But with our forums such as this one those macros I used would not have existed.

This is the cleanup I'm ting to accomplish:
D2 - Phone: 262-255-7430
Result 262-255-7430

E2 - Listed Since: 01/1970
Result 01/1970

F2 - Latitude: 43.756260
Result 43.756260

G2 - Lonhitude: -81.973920

I need it to run to the end and stop after doing the 281977 rows. Every time I would run it I would have the ability to change the bottom row. Everything else stays the same.

Here's hoping.
Bob

 

[Peter_SSs]

Well, I'm afraid I have not been able to get a good grip on all that so I'll just try for one bit to start. Assuming CityStZip in column C and columns D:E empty ready to receive the relevant data, try this in a copy of your workbook.

It assumes there are no blank cells among the data in column C. If that is not the case, please advise.

This code would be instead of the formulas you were previously using in these columns.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CityZip()<br>    <SPAN style="color:#00007F">Dim</SPAN> a, cz<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, L <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Range("C1", Range("C" & Rows.Count).End(xlUp)).Resize(, 3)<br>        a = .Value<br>        L = <SPAN style="color:#00007F">UBound</SPAN>(a, 1)<br>        <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> L<br>            cz = Split(a(i, 1), ", WI ")<br>            a(i, 2) = cz(0)<br>            a(i, 3) = cz(1)<br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        .Value = a<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[amerifax]

Looks great. Know all I need is to get a book on vba. That should be a good start.

Bob

 

[Joe4]

Bob,

It looks like that you have post names, addresses, and phone numbers of real people here. Is that the case?

We have issues here in the past where people find their information posted, without their knowledge, and are furious. Also, if this is a work-related thing, your employer may also have some strict guidelines about publicly posted data from their system.

If that is the case, please let us know, and we will remove the parts of the posts that show that data.

 

[Peter_SSs]

Good point Joe.

I have now removed some such data from post #14 since Bob had suggested its removal already (in post #16). That removes the most obovious data that is directly in this thread. However, there is still the issue of the data in the linked image in post #18 and probably the linked file from post #16 (I haven't looked at the file). I suggest we await some feedback from Bob on that.

 

[amerifax-heather]

Peter,

That code worked very well, as I figured. And Yes Column C does always have a value.

Yes, these are the first steps we are doing. This one to split out the City and Zip and the other code you helped with to remove the labels from Phone, Listed Since, Lat and Lon. The original data starts with the labels in those columns and we add the columns for City and Zip to accept the new results.

We are checking the phone for duplicates to find where a duplicate exists but they are expected to be there in some cases. And we need to know where they are.

Where we are combining columns A-C & F (sorry it was originally A-D until we added the city and zip fields) it is strictly for the purpose of finding Exact Duplicates across those 4 Columns. When duplicates are found across the 4 columns we only need to keep one of the records.

We would also like to check and see where there are duplicates in the Lat and Lon fields combined. We expect there to be duplicates in this instance also, we just want to know where they are.

As far as removing the posted link from post 16, that is fine, I completely understand the reasons behind not wanting to dowload files, so you can remove that. I have removed the file from my server.

Thank you so much for the help so far. I hope this better explains what we are trying to do.

Heather
Amerifax

 

[Peter_SSs]

I'm not sure if it will be a performance issue with such a large amount of data but wherever you want to check for duplicates in a column (including a concatenated column) have you tried Excel's built-in (from Excel 2007 on) Conditional Formatting for duplicates?

Select the column by clicking its column label.
Home ribbon|Conditional Formatting|Highlight Cells Rules|Duplicate values...|OK

If you want, you can then use AutoFilter on the column to 'Filter by color' if you just want to view the duplicates.

 

[amerifax]

Bob - Working Late for Heather.

It worked perfect in this case, no need to filter.