Chris Mitchell wrote:
I have a MS Excel table comprising 2 columns and 6500 rows. Column A
contains 366 variables, (VA001 - VA336), and Column B contains 48, (VB01 -
VB48), the AB pairings are unique but not all possible pairings are present.
This table is created monthly and is variable.

I want to be able to sort by Column A then Column B.

I then want to identify all VA001 rows and copy the corresponding Column B
entries then Transpose these to the top VA001 row and delete all VA001 rows
bar the top one with the transposed Column B data so that I end up with a
single row for VA001 that is as many columns wide as is necessary to
accommodate the variable number of corresponding Column B entries.

I want to repeat this for each Column A variable so that I end up with a
table with 366 rows + header with as many columns as is necessary to
accommodate the longest row of former Colum B data however long that might
be.

I then need to apply the same to other similar tables that have different
numbers of variables for A and B.

I can do all of this manually but it is tedious.

What would a macro to do this look like?

Can I use the same macro in both MS Excel and MS Access?

TIA.

Chris



Without a macro...sort by Column A and Column B. Assuming you have headers in
Row 1, put the following in C2 and copy down as needed:

=IF(COUNTIF($A$2:$A2,$A2)=1,B2,"")

And put this in D2 and copy across and down as needed:

=IF(COUNTIF($A$2:$A2,$A2)=1,IF(INDIRECT("A"&ROW()+ COLUMN()-3)=$A2,INDIRECT("B"&ROW()+COLUMN()-3),""),"")

Select columns C:AX (or as far as you needed to go) and COPY / PASTE SPECIAL /
VALUES.

Sort by Column C and delete any rows where there is no entry in C. Then delete
Column B and sort by Column A.