Transposing three columns into one row after manipulating data in columns
 

I am a newbie that has searched for this particular application. I
have found and played with the macro that transposes columns to rows.
However, I have not been able to find anything that comes close to
this. I am asking help form the experts.

My raw data is presented in three columns. All three columns have an
equal amount of rows.

I would like to transpose this information: Here is the raw data:

Name Station Hours
wx1 Port 4
wx1 starboard 1
wx1 shores 16
wx1 huron 6
wx3 erie 1
wx3 port 4
wx4 shores 15
wx4 ohio 2
wx4 port 22
wx2 superior 3
wx2 mackinw 5

the names are always grouped. the order may not be consecutive, e.g.
wx1 may come after wx4, etc.
the number of stations associated with each name can vary. The only
record group delineator is the name.

The order that the stations are to be listed as associated with each
name is determined by the number of hours in a descending order:

e.g.

Name Station1, Station2, Station3, Station4, etc.

The finished tranposition should look like this:

Columns:

Name Station1, Station 2, Station3, Station4
wx1 shores port starboard
wx3 port erie
wx4 port shores ohio
wx2 mackinaw superior

Note for wx1, shores had the most hours, then port, then starboard.

THis data is to be copied to another sheet in the workbook.

I thank all of you in advance.

Transposing three columns into one row after manipulating data in
 

Assuming your data in sheet 1 and your citeria and formula in sheet 2
Sheet2 A2 contain wx1
Sheet2 A3 contain wx3
and so on...

Formula in sheet 2 B1

=IF(ISERR(SMALL(IF(Sheet1!$A$2:$A$12=Sheet2!$A2,RO W(INDIRECT("1:"&ROWS(Sheet1!$B$2:$B$12)))),COLUMNS ($A:A))),"",INDEX(Sheet1!$B$2:$B$12,SMALL(IF(Sheet 1!$A$2:$A$12=Sheet2!$A2,ROW(INDIRECT("1:"&ROWS(She et1!$B$2:$B$12)))),COLUMNS($A:A))))

ctrl+shift+enter (not just enter)
copy across and down as far as need



"digitaldon" wrote:

I am a newbie that has searched for this particular application. I
have found and played with the macro that transposes columns to rows.
However, I have not been able to find anything that comes close to
this. I am asking help form the experts.

My raw data is presented in three columns. All three columns have an
equal amount of rows.

I would like to transpose this information: Here is the raw data:

Name Station Hours
wx1 Port 4
wx1 starboard 1
wx1 shores 16
wx1 huron 6
wx3 erie 1
wx3 port 4
wx4 shores 15
wx4 ohio 2
wx4 port 22
wx2 superior 3
wx2 mackinw 5

the names are always grouped. the order may not be consecutive, e.g.
wx1 may come after wx4, etc.
the number of stations associated with each name can vary. The only
record group delineator is the name.

The order that the stations are to be listed as associated with each
name is determined by the number of hours in a descending order:

e.g.

Name Station1, Station2, Station3, Station4, etc.

The finished tranposition should look like this:

Columns:

Name Station1, Station 2, Station3, Station4
wx1 shores port starboard
wx3 port erie
wx4 port shores ohio
wx2 mackinaw superior

Note for wx1, shores had the most hours, then port, then starboard.

THis data is to be copied to another sheet in the workbook.

I thank all of you in advance.