0

I copy to the clipboard a huge amount of data consisting of many columns from our ERP system to Excel 2010 but I need to get rid of most of the copied columns. However, since it is copied from the system, I cannot specify which columns shall be copied to the clipboard, thus I have to copy all in any case.

Question: Is there any way how to automate the process of getting rid of specific columns? Doing it manually is extremely time consuming.

Possible solution?: I was thinking that there is maybe some way how to specify a header template and when pasting the data, the header template will filter out all columns with headers different than the ones specified but I have no idea how to do that.

pnuts
  • 58,317
  • 11
  • 87
  • 139
JS_Diver
  • 756
  • 7
  • 19
  • You could try to get data directly from [clipboard using VBA](http://stackoverflow.com/questions/9022245/get-text-from-clipboard-using-gettext-avoid-error-on-empty-clipboard). – agold Oct 13 '15 at 09:48
  • Any ERP system is going to have an export feature. If you cannot specify the columns there then specify them on the import of the transition file to Excel. –  Oct 13 '15 at 09:51
  • If you are looking for a simple solution then go for following approach. Accept data from system as it is, create another sheet where you only specify columns which you require and set links to you other sheet. See sample file This sheet with desired data im my case "TRNX_DLY" can be programatically imported to either database like Access or To Master Excel Sheet. – skkakkar Oct 13 '15 at 17:56

2 Answers2

0

Copying from the ERP system to the clipboard is a very ineffective way of handling data. Any system worth its salt should be able to export to a CSV file.

You can then use a variety of techniques to load that CSV file into Excel.

If you need to clean up the data, i.e. get rid of specific columns, you could use Power Query. This is a free add-in from Microsoft for Office 2010 and 2013 and it is built into Excel 2016 in the Data ribbon as "Get and Transform".

The beauty of Power Query is that it records every step you take to transform your data and whip it into the shape you need it to be for your Excel processing. Every step of Power Query will be recorded and can be repeated.

Let your ERP system create a CSV file in a location like S:\ERP\Exports\JSDiver\TheData.csv

You can then point Power Query to open that file, delete columns and do other stuff, like removing unwanted characters, extracting years and months from dates to help with grouping for reports, etc.

Next week/month, let the ERP system dump another file in exactly the same path with exactly the same file name. Open the Excel file with the Power Query and click "Refresh All" and the data from the CSV file will be read, shaped, transformed, just as you did manually the first time.

This may take a while to sink in.

Power Query is the best thing that has happened to Excel in the last 5 years. If you work with data, Power Query is an invaluable tool.

teylyn
  • 34,374
  • 4
  • 53
  • 73
0

One easy solution would be to set up 2 worksheets. 1 sheet where you paste in the raw data from your ERP. A 2nd sheet that has a simple formula that grabs the columns you want. Something like:

=Sheet2!A1     =Sheet2!C1    =Sheet2!D1
=Sheet2!A2     =Sheet2!C2    =Sheet2!D2

That would pull back columns A, C, and D. Then you can copy and PasteSpecial Values to your final destination.

Just use that as a template every time you need to do it.

Byron
  • 399
  • 2
  • 12