SPLITING A COLUMN OF DATA CLASSIFIED BY 2 VARIABLES INTO A 2 VARIABLE MATRIX

[lmcmanus]

Hi,

I am summarizing data generated by GIS. For each country (total of 148 countries), I have 8 variables that are classified by elevation (9 classes) and by distance from shore (9 classes). The data output is shown by the BEFORE worksheet.

I wish to split the column of variable data into matrix form where the columns are the 9 distance classes, and the rows are the 9 elevation classes, for each of the 8 variables.

I would greatly appreciate getting a VBA code to do the matrix transposition. I am new to VBA. I have an excel calculation template to do the summaries thereafter.

Your help is greatly appreciated and will try to learn basic VBA coding to be more efficient.

Many thanks,

Liana

1281912820

[Paul_Hossler]

It's easy enough to do with a pivot table (attached) either each country x variables (148 x 8) or if the 148 countries in in a single list or a combination

I've found it works better if all the countries are in a single list

After you have your 148x8 = 1,184 matrices, what do you do with them and how do you do it?

BTW, the title "SPLITTING A COLUMN OF DATA CLASSIFIED BY 2 VARIABLES INTO A 2 VARIABLE MATRIX" doesn't seem to be in agreement with the question in the text

[lmcmanus]

It's easy enough to do with a pivot table (attached) either each country x variables (148 x 8) or if the 148 countries in in a single list or a combination

I've found it works better if all the countries are in a single list

After you have your 148x8 = 1,184 matrices, what do you do with them and how do you do it?

BTW, the title "SPLITTING A COLUMN OF DATA CLASSIFIED BY 2 VARIABLES INTO A 2 VARIABLE MATRIX" doesn't seem to be in agreement with the question in the text

Thanks for your reply, Paul. From the raw data which contains a subset of countries because of the GIS is done regionally, I parcel out the data into country raw worksheets such as the BEFORE worksheet I attached previously. I mentioned that I have 8 variables with values listed in 8 columns (population, cropland area, pastureland area, primary vegetated area, secondary vegetated area, urban area, ice/water area, total area. These are accompanied by the ISO3alpha country code column, the elevation code column (9 classes) , and the distance from shore column (9 distances).

I need to have each variable in a matrix form such that the values are arranged by elevation among rows and the distance among columns. These are shown in the AFTER worksheet. Since I am making these summaries as the GIS analysis generates the raw files, I wanted to implement the code on workbooks with individual country worksheets arranged regionally.

I use an excel template to generate summaries of cumulative totals by elevation, and then by distance to shore for each of the nine variables. A country worksheet with summary is attached here. I use the "all worksheet highlight" function to implement the summary template.

I hope I have describe the desired matrix format of the data.

Many thanks,
Liana

1282212822

[lmcmanus]

Hi, Paul,

Will learn how to manipulate the pivot function. It seems like the way to go.

Many thanks,

Liana

[lmcmanus]

Hi Paul,

I am using the pivot table function following the templates you produced. I just have to make sure that I include the zero values in the worksheet so that the distance by elevation tables are all symmetric. Many thanks for this. Alongside the macro templates, I am able to do the summaries and QA/QC of the data at the same time.
Much obliged,

Liana