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