help with repetitive data entry

forestfanstill

Hi I have to copy data from one place to another. I have resized the two parts so can see both on screen at once and was wondering if there is any way of automating the process as have over 3000 data points to place from one point to another.

an example is left half of my screen has data from national statistics office and i need to pull some of them and copy to relevant place on excel that is open on right half of screen.

Thank you in advance

Jivesinger

What data are you trying to use?
I'd imagine most data from ONS has a variant published in a format which could be imported directly into Excel.

Tom99

[FONT=Verdana, sans-serif]You could probably do it using a excel macro VBA command sendkeys etc. You would need to be familiar with programming in VBA though, since you could not record the actions required.

[/FONT] [FONT=Verdana, sans-serif]I have used this method in the past to load thousands of entries listed in a spreadsheet into a different application.

Also if the website you are copying from is set out like a table you can just copy that part of the website and paste it into excel.
[/FONT]

Heedtheadvice

So, we know you want to copy into Excel (so we know it's capabilities)
but you do not say what format the ONS data is. That information could make the task very simple indeed.

As an example if it were part of a simple web page display or other format that was by and large test that you could copy then it is easy to paste into Excel. Usually no need for VBA as there are lots of ways to 'clean up' or remove unwanted data or reformat into rows & cells ( tables) to enable data manipulation of analysis.

Post details or give a link to an example and we can then help further.

forestfanstill

The data to be copied to excel is from the quarterly GDP figures for each business sector (https://www.ons.gov.uk/economy/grossdomesticproductgdp/timeseries/l2kl/pn2) is one of the data sets. it can be downloaded in xls format and i know they could then be cut and pasted in their whole but i have to do all quarters on seperate spreadsheets with one sheet covering all 4 quarters so each entery ends up being used twice. I then have to do the same on government borrowing and spending and check if any one secter alone could be used as a guide for growth and spending or is it only with all combined if at all any trend can be reached.

Tom99

[FONT=Verdana, sans-serif]Well you could download each business sector data set to .xls, then using a new sheet paste the results for each business sector into different columns. So Ag & Fish in Col A/B Next in Col C/D and so on.

[/FONT] [FONT=Verdana, sans-serif]Then in the sheet you want to put your lookup onto use the vlookup function. An example here:-

[/FONT] [FONT=Verdana, sans-serif]https://www.techonthenet.com/excel/formulas/vlookup.php[/FONT]

[FONT=Verdana, sans-serif]Some extra info would be helpful to assist further suggestions:-[/FONT]

• [FONT=Verdana, sans-serif]How frequently do you have to update your data.[/FONT]
• [FONT=Verdana, sans-serif]How many different tables would you need to download each time[/FONT]
• [FONT=Verdana, sans-serif]Do you [/FONT][FONT=Verdana, sans-serif]only[/FONT][FONT=Verdana, sans-serif] need the latest quarters figure from each table. E.g. each table stays the same but each quarter a new figure is added at the bottom.[/FONT]
• [FONT=Verdana, sans-serif]Is there a way to download all the tables you need in one go? I can see you can download each table individually but could not see a function to download more than one table in one go.[/FONT]

[FONT=Verdana, sans-serif]
I think you problem splits into two:-

[/FONT] [FONT=Verdana, sans-serif]Getting the latest tables into excel, then using the functions within excel to pull out the data your need in the format you need it.[/FONT]

Heedtheadvice

^^^ similar response to the above.

You seem to be a novice at using data. No insult intended but you have asked a question that is not what you are trying to achieve and assumed a method!

I take it that you wish to do analysis of data in Excel that is already available to you in Excel from the down loads such as quarter on quarter or year on year figures or comparisons? A little more detail will help us to suggest the easy solutions, that is, what is your final aim that you have outlined.

Rather than cut and paste from one sheet to another the destination sheet can just be a pivot table with data from downloaded sheets being the source or sources. It is that kind of process they are best at and it avoids cutting and pasting.

Whichever method is used needs to cater for stats updates by the ONS or is it a one off activity?

To me is you are trying to find trends then there are standard qualitative methods for doing such things and what you seem to be proposing might not be the best or correct way to do it but I could be wrong!

Have you got an example of the end result you are trying to achieve with the data, that which you are inspecting to look for trends? If so that would be useful to see (or even very similar data presentation), but if you have not you could knock together dummy data. That will help people to jump from the data to the presentation rather than do a few at several steps in the process.