transpose based on repeating cell values

Hello,

I have 3 columns of data (A1-C1), record, study and institution(all numeric). When an institution has multiple studies, the institution # is repeated on as many rows as there are different studies. Also, each study has a unique recordID which is in another column.
So if institution 5 has 3 studies there would be three rows of data where 5 would appear in all three rows under institution and 3 different numbers would appear in the record and study columns.

My goal is change this so that everything is on one line- institution listed once, preceded by multiple columns to list study and record.

example:
record study institution
11657 35633 5
11464 35678 5
11954 35567 5
41687 21456 43
41587 21657 43
41356 21756 43
41807 21865 43
41678 21576 43

into
record record record record record study study study study study institution
11657 11464 11954 . . 35633 35678 35567 . . 5
41687 41587 41356 41807 41678 21456 21657 21756 21865 21576 43

To do this I would need to transpose record and study from rows to columns where institution is repeated. The data is from line A2 through A1761. There are up to 9 studies for one individual institution, so there will have to be 9 columns for record and 9 columns for study.

Bonus point, I have Column D, E, F, G which are just like institution in that they are repeated in subsequent rows for different studies. They are repeated exactly the same as institution, so if institution is repeated, column D through G will also be repeated (D and E are names and F G are dates) I would like them to also appear (right after institution).

i.e.

institution first last sent received
5 john doe 5/17/2009 5/19/2009
5 john doe 5/17/2009 5/19/2009
5 john doe 5/17/2009 5/19/2009
43 jane flugelhorn 5/16/2009 5/21/2009
43 jane flugelhorn 5/16/2009 5/21/2009
43 jane flugelhorn 5/16/2009 5/21/2009
43 jane flugelhorn 5/16/2009 5/21/2009
43 jane flugelhorn 5/16/2009 5/21/2009

becomes
5 john doe 5/17/2009 5/19/2009
43 jane flugelhorn 5/16/2009 5/21/2009

so what I would love to have all on one line:
record record record record record study study study study study institution name last name date sent date received
11657 11464 11954 35633 35678 35567 5 john doe 5/17/2009 5/19/2009

Thanks for the help!
mcdermott2

I question for you. Why do you want to do this?

I started looking at this and decided it was going to be too fiddly with formula and that I'd knock up some simple VB code for you ... then I got a bit carried away.

No doubt by the time I post this somebody will have told you to use pivot tables, but enjoy anyway ...

Please Login or Register  to view this content.

You DID get carried away, didn't you

Yeah, but structured code, properly defined constants, no variable re-use AND comments ... I feel like I've earned my brownie points

Wow, this is amazing. Thank you for the help! It is working perfectly. Institution ended up not uniquely identifying each name, so I made an if statement based on last name which did what I needed and replaced that with institution number.

Thanks again!

(yes, you certainly have received major brownie points in my book)