Hi,
I have multiple crosstab workbooks like this, each representing ACV data for a particular Category-Country e.g. Foods-US. The workbooks filenames are e.g.
etc.
i can loop thru the folder and import all these workbooks (each containing a single sheet of crosstab data), but i am finding it difficulty to analyse this huge crosstab data as i have never worked with crosstabs before.
i tried Unpivoting it or UNION ALL, but there are > 166 "Week ending dd/mm/yy" columns, depending upon how many years data is contained in that workbook! Also in some workbooks some weeks may be missing (no data for that week). Below is a snapshot of it imported into Access as a table:
Crosstab data imported into Access for Analysis
if you see the 1st column, it has values separated by "-". i want to split this column into 3 NEW columns using the "-" as delimiter, e.g.
ASDA DIET - LINE EXTENSION - 2012/03/02
will go into 3 columns viz.,
<tbody>
</tbody>
The resultant table should somewhat look like this, where their ACV values should show as column headings and their values as their Counts, for each BRAND or TYPE for that Year e.g 2010:
Output table
could someone who has worked with such Crosstabs help quickly?
I have multiple crosstab workbooks like this, each representing ACV data for a particular Category-Country e.g. Foods-US. The workbooks filenames are e.g.
- INO-Food-US.xlsx
- INO-Pet Food-US.xlsx
- INO-CCare-US.xlsx
- INO-CCare-UK.xlsx
- INO-Food-UK
etc.
i can loop thru the folder and import all these workbooks (each containing a single sheet of crosstab data), but i am finding it difficulty to analyse this huge crosstab data as i have never worked with crosstabs before.
i tried Unpivoting it or UNION ALL, but there are > 166 "Week ending dd/mm/yy" columns, depending upon how many years data is contained in that workbook! Also in some workbooks some weeks may be missing (no data for that week). Below is a snapshot of it imported into Access as a table:
Crosstab data imported into Access for Analysis
if you see the 1st column, it has values separated by "-". i want to split this column into 3 NEW columns using the "-" as delimiter, e.g.
ASDA DIET - LINE EXTENSION - 2012/03/02
will go into 3 columns viz.,
| BRAND | TYPE | RECDATE |
| ASDA DIET | LINE EXTENSION | 2012/03/02 |
<tbody>
</tbody>
- RECDATE is un-important.
- but even if the TYPE is kept i.e. a single column (instead of 3 columns), that will also help to aggregate just on the TYPE column.
- I want to loop thru all these workbooks and pull this data into a recordset, one at a time. (this is done)
- then run a query on this recordset to count the 1st occurrence of ACV value in each BRAND/TYPE row.
- Then get a count of the 1st OCCURRENCE OF ACV values for each BRAND or TYPE in a YEAR.
- if NA is shown it means "No Data Available" or that BRAND/TYPE "Did not Exist" till those dates, where NA was shown in their columns.
The resultant table should somewhat look like this, where their ACV values should show as column headings and their values as their Counts, for each BRAND or TYPE for that Year e.g 2010:
Output table
could someone who has worked with such Crosstabs help quickly?
