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

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

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

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.

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

Glenn wrote:
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.


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

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

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
C2:DD6500?


"Glenn" wrote in message
...
Glenn wrote:
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.


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

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

Chris Mitchell wrote:
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
C2:DD6500?




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

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