Automate keying in a small amount of data from a large csv.file into an excel spreadsheet

Hi All,

I am very new to VBA programming, and cabn see I will need to invest a lot of time to get to a decent level, but the first problem I have is sorting daily information into order.

Basically every day we get a large csv file that has numerous columns, some have the information we need, many colums have information that is not used and just needs to be discarded. Next I need to choose the data that I do need and then flow it in data order so my spreadsheet looks like


Data Y, Data A, Data B etc (ie data is not in alphabetical order but is in the order we need to see the information)

23 Sep 2012
24 Sep 2012
etc

Once I have this information I can save at least an hour of re-keying time per day.

Can anyone help ?

Originally Posted by Tinbendr

You don't provide enough detailed information for us to help you. Provide a dummy sample CSV file and a sheet of the expected results.

# ----------------------------------------
# PL
# Location
# 22266445
# ----------------------------------------

Country / Territory Visits Pages / Visit Avg. Visit Duration % New Visits Bounce Rate
United Kingdom 17,952 1.5 00:16:23 22.37% 4.28%
United States 4,263 1.32 00:12:36 23.08% 3.71%
Canada 638 1.63 00:16:59 30.56% 5.02%
Italy 416 1.42 00:13:01 18.99% 10.10%
Australia 395 1.24 00:15:20 24.81% 4.30%
Germany 260 1.31 00:08:03 21.54% 7.69%
France 246 1.28 00:18:31 18.70% 7.32%
Ireland 216 1.66 00:17:47 23.61% 6.02%
Philippines 191 1.19 00:08:56 27.75% 22.51%
Greece 167 1.2 00:14:12 19.16% 4.79%
24,774 1.45 00:15:19 22.46% 4.88%

Day Visits
8/22/12 4,165
8/22/12 3,954
8/22/12 3,760
8/22/12 3,622
8/22/12 3,905
8/22/12 4,250
8/22/12 3,786
27,442



Expected Results - When re-keyed would look something like this



Total USA UK Canada Czech Republic France Germany Greece Ireland Italy Poland Portugal Phillippines Australia Spain Turkey


16/08/2012
17/08/2012
18/08/2012
19/08/2012
20/08/2012
21/08/2012
22/08/2012 24,744 4,263 17,952 638 - - 246 260 167 216 416 - - 191 395 - -

the above was copied from an excel file does this help ?

Hi David and Thanks for taking the time to look at this.

The answer to your questions are :

- Just to give you background we are looking at the data downloaded from Google Analytics as visitor data from countries, each day the countries may differ, on one day we may have visits and on other days no visits. We may well have a number of visits from small countries giving just a few visits. After we have populated our top 15 countries (that are all on fixed headers), we can move onto the next day, and so on. There is a lot of other data such as unique visits, time of visit etc. - we usually ignore this data, or discard it.


The date is taken from the start of the report and it is in the form # 20120822-20120822 for the 22nd August 2012, not from the data block

The numbers dont just occur once, you see a typical pattern emerge, but if you for example advertise in a new country for a few days, you would then expect to see numbers from that country increase, and so this is what you would measure on the Google Analytics report, and you can see then if your advertising spend is effective.

We can choose which countries we want to summarise, and they will be keyed in Row 1 of the spreadsheet for each column from around column G onwards ie G1 USA, H1 UK, I1 Portugal etc.
We can vary at anytime which countries we want to see summarised, and then we ignore the remainder of the data.

I hope this clarifies, but if not please com back and I will try to help. I understand you are not supposed to download excel spreadsheets, but if this is not the case, I will send you an excel spreadsheet if this helps

Regards

NoBol