Excel: Text to columns, fill down and remove duplicates

[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

[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.

[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.

[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

[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.