Replies

thank you – I will have to look closely at some of the suggestions but just to clarify,

the processing is being done in csv – I do not need or want to convert the file to excel – in fact once the file has been amended with respect to any incorrect address details, the next step of processing, uploading the file into another program has to be in csv format. I guess my misunderstanding is that I do open the file via excel and make changes that way, though I save back as csv.

Using input boxes would be an idea but I am wary that there are too many options/variations with respect to addresses:

5 High St = normal residential address

3-5 High St = normal residential address

3/5 High St – Unit/Apartment (proper way to enter it)

Unit 3, 5 High St – Unit and how some customers will enter it

Villa 3, 5 High St (Retirement Village)

PO Box 53 ( other option)

plus a number of other variations.

I am somewhat realistic in not expecting to get a perfect file unless subscribing to a full address validation program, so I probably will have to continue having some cleanup work each day before uploading the amended file, just want to reduce the work and avoid the most common/obvious issues like dates.

Thanks guys, it’s working now. This was not done via ribbon but the save as feature and I now understand how to get rid of it. Still, for me at least the trick is in the save as dialog to use the Tools feature with drop down arrow, otherwise the option to save without password does not come up. I would consider this to be well hidden.  Having used xls for 20 years I never checked that button when doing a save before.

Gone to Plaid –  I understand and as mentioned in my query, I am using zip and encryption already but in this case I received a file that is password protected.

Doriel,  when I follow your path I can see the option to Protect Workbook but when clicking on the little drop down arrow,  I don’t see any option to unprotect.

When asking the sender, he told me to save the file via save as, choose your destination and use Tools/General Options/ then remove password and save. This did the trick. I would have thought there is an easier way to remove a password.  I am using Office Home & Business 2016

 

1 user thanked author for this post.

doriel

Kirsty,

I am pretty sure the filter was there before.  Just checked the real spreadsheet and I had removed the filter (unrelated to this issue) since posting  and you absolutely correct,  when adding back the filter now the outcome is normal.  Thank you for your help.

Hi Paul,

I have done some “testing” before and only noticed issues when I made mistakes with sorting, e.g. not paying attention to highlighting the correct / full range and thus messing up my spreadsheets.  As my spreadsheets often are quite large and some mistakes are not immediately apparent, I am a bit paranoid.  So at the moment I often do things in two steps, just to be on the “safe” side and I was hoping there is a clear rule that I could adhere to.

I am not sure what you mean by “underlying data may change position depending on the application” .  If this means filter and sorting are applied as needed and everything is back to normal once those commands are revoked, then that’s fine with me. If however some of the “position change” is “permanent”, then I would have a problem.

I think Google has one of the best spam filters around. I hardly ever notice spam in my gmail box and the messages in the spam folder get deleted after 30 days or so. Once in a while a message gets placed incorrectly into spam but those cases are rather rare.

Microsoft (msn/hotmail/live) on the other hand gets it wrong quite often and deletes spam after 10 days.

Zeddy,

excellent work – I now can manage to get my sheets updated easily following your clear instructions. I really appreciate your help.  Thank you very much

Hi Guys,

just stumbled across this thread and I love it. Will work myself slowly through from the top and listen to all these suggestions over time – who knows I might find something I composed a long time ago.

4 users thanked author for this post.

Fred, Myst, migongo, OscarCP

Zeddy,

thank you but I am still struggling.

I was originally hoping for some formula that I could copy and paste and adapt as necessary, obviously I would need to understand how the command is created.  As you are using vba, all the magic is running in the background, so I am not sure how to adapt this. I tried patiently copy the formula I can see in the “match now” column adapted to my real cell locations but do fail.  Could I use the original match formula without the Step 1 and 2 buttons?  How do I determine Lookup Array and match type? Your formula seems to look only at column A (item no) and then match type 0.  What does the 0 stand for. How does the formula go for Column C as the relevant one?

Looking at your second more elaborate offer, a similar issue occurs for me in that my sample file was not exactly like the real file. To make my sample less confusing, I cut out some rows.  Now either by trying cut/paste of the real info into the sample file or by adding columns to match the real layout, something goes wrong.  Not sure if this is related to the error I am getting from excel “the range you are pasting contains formulas that cannot be pasted…the formulas will be pasted as values…”  While your vba reports success, the visible outcome is wrong.

Sorry to be such a pain.

 

Hi Zeddy,

thanks again for your help.  I have looked at this trying to replicate with my worksheet but hope you can excuse my ignorance.  I can follow your overall logic and can see the match formula when clicking the Step 1 button but I am not sure if I do this correctly.

Apart from the extra tab with the pricing info new, do I need to add two further columns for match now and 1 unit and will these be hidden during normal use of the spreadsheet. Ie I am using them only when doing updates on pricing and still have to copy / paste value the output from column Q to column G so that my other columns can still operate?

Zeddy,

thanks for that – I will study this and integrate it into my workflow. Much appreciated.

[beethoven]

Excellent – both initial suggestions work like a charm. So instead of having to modify each cell, I can apply the formula once and the last suggestion re avoiding errors where no trimming is necessary is even better (though a bit harder for me to understand) . All this will save a lot of time.   I really appreciate your help.

• This reply was modified 4 years, 9 months ago by beethoven.

Haha, very funny  – I am glad to assure you that I am not working in either of these industries but do see a bit of a catch22 for getting fully involved in the vba wizardry.  I need all these spells now when I could put them to use in my daily work but don’t have the time to fully explore. Once I retire I feel keen on learning much more in this area but alas, won’t have the practical use anymore.

1 user thanked author for this post.

zeddy

Thanks Zeddy, – I have never used VBA, so that is totally new to me.

That is very helpful and I saved the commands for use in the future – many thanks

thank you – we will have to do a bit of trial and error to see if we can get it right.

Cheers

Beethoven