Jump to content
Sign in to follow this  
MrCheese

Excel: Text to columns, fill down and remove duplicates

Recommended Posts

MrCheese

Hi there,

Long time watcher, but this time, I can't find my answer.

So in Excel, I have autoit paste a table of information (dates and numbers) which is pasted in a CSV format into an existing worksheet which has pre existing information (earlier dates and numbers). These dates and numbers are more of a data feed.

So they are pasted into column B after the earlier data, then this is what I need to happen:

* they all need to be selected, and 'text to columns' and then separated based on CSV.

* Then Column G-> T are deleted,

* column A is filled down from the last formula

* all is selected and duplicates removed.

 

Now, I can do this by recording a macro, however, I need to do it across 9 sheets in total which the data is pasted into varying row numbers. Additionally, the data posted tomorrow will be further down the spreadsheet then data pasted today. So, I've been unable to replicate the macro across sheets and data later on.

How can I make this happen? 

Cheers.

Mr Cheese

Share this post


Link to post
Share on other sites
MrCheese

Sorry, I should have made it more clear, the guidance I'm after!

So, basically, should I continue to use macro within excel, if so, how can I make it perform how I want to, with a variation on sheet and row.

Or, can I autoit script the changes that I speak of in some what of an automated way using the excel UDF

Or, should I just do it the long way, with 

send("!+{down}")

sleep(500)

send("!+{right}")

etc.

Thanks.

Share this post


Link to post
Share on other sites
water

You can either use the Excel UDF that comes with AutoIt or my rewrite of the UDF. My UDF works with ranges so I think it will help to solve your problem.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
MrCheese

Ok, so it works with ranges, that change?

Are you referring to _excel_rangefind ?

What other functions would be useful? How do I do fill downs/ remove duplicates, etc?

Thanks

Share this post


Link to post
Share on other sites
water

You would need multiple functions from the UDF.

_Excel_RangeDelete to delete columns

_Excel_RangeWrite to insert data into the Excel sheet

etc.

Best would be to do it step by step.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×