How do I group and transpose data - macro help needed.

[Chris Mitchell]

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

 

[Glenn]

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.

 

[Glenn]

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.

Actually, you can use the second formula in column C, too.

 

[Chris Mitchell]

Thanks Glen.

Looks OK so far, but I haven't completed it yet.

Do you know of a way I can specify the paste to area rather than selecting
it?

I.E. can I somehow tell Excel to copy contents of C2 to C3:C6500, or
C2D6500?

 

[Glenn]

Thanks Glen.

Looks OK so far, but I haven't completed it yet.

Do you know of a way I can specify the paste to area rather than selecting
it?

I.E. can I somehow tell Excel to copy contents of C2 to C3:C6500, or
C2D6500?

Select and Copy cell C2. Select EDIT / Go To...

In the "Reference:" box, type C2D6500 and click OK. Then Paste.