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

 

[amerifax]

This is our first VBA and it went so smoothly...I can't thank you all enough for your help. And thank you for taking the time to explain the VBA/Macro window and how to use it.

I am starting to get the hang of this a bit...

Bob

 

[amerifax]

>>Norie<< That would work. But the VBA that Peter suggested was absolutely tops. It run 30,000 in way less that 2 minutes and the end results was only values in the cells, no formula to get read of.

 

[Peter_SSs]

OK...I hope I am not pushing my luck but...I have a few other questions.

I have the following other data calculations or replacements. Because of the amount of data, and the formulas, anytime I do something on the sheet, it takes forever.

How can I work the following formulas into a Macro?

I use the following formulas to pull the City and Zip out of a Column that is formatted as City, State Zip.

City:
=LEFT(C2,SEARCH(", WI",C2)-1)

Zip:
=RIGHT(C2,LEN(C2)-SEARCH(", WI",C2)-5)

Then I run this formula to check the Phone Number field for Duplicates:

=IF(COUNTIF(F:F,F2)>1,"X","")

Then I concatenate the Name, Address, City State Zip and Phone with this:

=CONCATENATE(A2," - ",B2," - ",C2," - ",F2)

I do the concatenate to run this formula again to search for duplicates in all 4 Columns.

=IF(COUNTIF(J:J,J2)>1,"X","")

I know that VBA is more complicated but the end result leaves the data much more clean with way less processing time.

And thank you again for your help with the data cleaning, again the result that was left was way cleaner than having formulas in all the cells.

Bob

If you have a huge amount of data, there may be a better way.

1. Could you identify which cell each of the above formulas is in?

2. Could you confirm whether you have headers in row 1 or if row 1 also contains similar data?

3. Can you confirm that all the addresses contain the state "WI" as implied by your formulas?

4. What do you do with any duplicates yopu find in the phone number column?

5. What do you do with any duplicates across the 4 columns? (Confirm which 4 columns)

6. Could we have 5-10 rows of representative sample data? (Ensure that it is clear which column(s) the data is in.)

 

[amerifax]

Sure can. The Sheets first row is the header. starting with Name at A1
Name,Address,CityStZip, City, Zip, Phn, ListedSince, Lat, Lon, A_D, Dup, Lat-Lon Dup, CalForm, Proper Test (easy)

I use the following formulas to pull the City and Zip out of a Column that is formatted as City, State Zip.

a - City: D2
=LEFT(C2,SEARCH(", WI",C2)-1)

b - Zip: E2
=RIGHT(C2,LEN(C2)-SEARCH(", WI",C2)-5)

c - Then I run this formula to check the Phone Number field for Duplicates: All Records get retained and Field DUP getts "DUP"
=IF(COUNTIF(F:F,F2)>1,"DUP","") K2 Checks F2 for duplicates and puts "DUP" in K2 for F2>=2


d -We concatenate A2,B2,C2,D2 (A-D)Name, Address, City State Zip and Phone
We did this only to get a Dup (A-D) on this combination of values. This a to use a system wide duplication and only one Record should be retained. This duplication is different from Phn since phone number's can have more than i person to a number. We where concidering puting a number in Duo Field as 3 people to phone = 3 in DUP field.
=CONCATENATE(A2," - ",B2," - ",C2," - ",F2) A duplication here would mean all records more than one is not needed

Noe: this concatenate is only needed to verafi duplicate of a broder range to run search formula against.
=IF(COUNTIF(J:J,J2)>1,"A-D","") This formula was to be used in conjunction with directly above(A2,B2,C2,D2 (A-D)

e. Latitude Longitude Duplacation
=IF(COUNTIF(I:J,Not SureF2)>1,"Number Value","") We hope to see id Lat Lon combined as one was a duplication. If so add thenumber of times it duplicates in field Lat-Lon DUP

Our reference to Work Column - Run data, Insert column next to Example-City} Copy Values from fresh data and apply to newly inserted Column, Delte original City Column This step not needed with VBA.
1. Could you identify which cell each of the above formulas is in?
a Work Column - Based on City D
b Work Column - Based on Zip E
c Work Column - Based on Phn F
d Work Column - Based on A-D
e Work Column - Based on Lat H2 & Lon I2

2. Could you confirm whether you have headers in row 1 or if row 1 also contains similar data?
Row one is Headers (See Above)

3. Can you confirm that all the addresses contain the state "WI" as implied by your formulas?
Every record is WI

4. What do you do with any duplicates you find in the phone number column?
Duplication on only Phn should be retained.
Duplication on based on A_D only one needs to be retained. Wish List - One one retained should be the one with LAT & Lon if some don't

5. What do you do with any duplicates across the 4 columns? (Confirm which 4 columns)
A-D they are not needed since they are equal from A-D.

6. Could we have 5-10 rows of representative sample data? (Ensure that it is clear which column(s) the data is in.)
Port Washington justification was my error it is always left justified.
N2 Proper Test (Easy) is not used

Data removed
If your sreen is wide this might work for you. Should I try an attach a file?
Bob

 

[Peter_SSs]

You cannot attach files in this forum, but you can post small screen shots. My signature block has three methods. If you want to try them test in the Test Here forum. That way, if something goes wrong, you won’t be messing up a main forum.

I'm still not sure about your data samples. Are those long lines of data what you start with, or after you have also applied some of your formulas or code?

It appears that your formulas start in column D so I assume your original data is in 3 columns A:C. Is that correct or is your original data just in column A and you manipulate it in some way to populate columns A:C? For a start, I'm just trying to grasp what your original data is like and where it is.

 

[amerifax]

Sorry about second post I'm unable to attach to previous Post. Attached is the sample file.

http://www.home2000.com/SampleforMrExcel.xlsm

Please remove sample data out of last post in interest of a cleaner message.
Bob

 

[Peter_SSs]

I'm sorry but many helpers here, including me, choose not to receive files from other members or download from other sites. There are a number of reasons including ..

- Having done so in the past it often turns out that the workbook/worksheet is quite complex and takes a lot of time to become familiar with. (If so, it may mean the problem is really too complex for a free public forum like this.)

- It tends to take the onus off the 'asker' to distil their problem to something clear and concise and becomes more "Here's my workbook, can you figure out what I'm trying to do and fix it?"

- Due to security issues at work sites, many users are unable to download such files.

- To some extent it defeats the purpose of the forum being a public forum.

If you are unable to use any of the screen shot methods, can you clearly identify, using say the last line of sample data from your last post, exactly what is in column A, column B and column C before any processing has been done?

 

[amerifax]

Sorry about second post I'm unable to attach to previous Post. Attached is the sample file.

Missed the edit time. What else can I do wrong today.

Bob

 

[Peter_SSs]

On the negative side, sorry if I seem 'picky' today , such images are not as good as a screen shot directly in your post since the data cannot be copied to a test sheet to work on. A lot of typng is required.

On the plus side at least I can see a layout.

Which of those columns constitute your original data?

Is this related to the earlier part of this thread where your data apparently contained text like "Phone: ", "Listed since: " etc? I don't see any of that in your image.

 

[amerifax]

Field Marked A-D only used to Find duplicates that get deleted from the file
Our spreadsheet or workbook has nothing added to it - We create a new spread sheet and import a DB IV file. When it comes in to Excel it has only these fields:
Name, Address, CityStZip, Phn, ListedSince, Lat, Lon, Listed Since

Added Fields:
CityStZip, City, Zip, A-d, Dup, Lat-Lon, CalForm, Proper Test (Easy)

These filles are dropped:
CityStZip - After ripped to City, Zip
A-D - after duplication check

Calculation Fields or Processed in other ways (Rip):
Dup is record of all Phone numbers that match more than I other phone number.

CityStZip is ripped to City, State,Zip (CityStZip is no longer needed after rip)
Dup - Result of phone Check
Lat-Lon Dup - Results of Lat & Lon, together, that have a duplication and the total number of mates, how many in a three numeric field.
CalForm - N/A
Proper Test (easy) something we are working on to deal with converting Names to Proper. Dealing with the Mc, and O' of the world.

Proper case we only avoid the Name field at this time.


>>Due to security issues at work sites, many users are unable to download such files.<<
Clearly makes scene and I'm going to consider it on forum. My wife works at Milwaukee Electric Tool and that might be way she is having problems with my site that deals with the New home Industry in Wisconsin
Bob