Transform using a formula

Hi all. I've been struggling with this a while. Running around in circles now. Effectively I have flat data populated by a data entry system. So the source has rows added or deleted over time. It is a people record and in particular, it records which programmes a person is allocate too. Each Portfolio and Progamme is related. So in the attached example, a Portfolio 1 entry would relate to Programme 1 entry. Portfolio 2, to Programme 2, and Portfolio 3 to Programme 3. So effectively a person can be on up to three assignments. I need to report these assignments but at the Portfolio and Programme level. I've tried pivoting on it, INDEX and MATCH etc. I confess to coming up to my knowledge level on this...

In the example, I effectively want to transform the rows into columns so I can collate the data into something like this, which I can then sort by Portfolio etc.

Portfolio Programme Last Name
Portfolio A Programme A Jones
Portfolio A Programme B Jones
Portfolio T Programme D Jones
Portfolio A Programme B Wilson
Portfolio C Programme C Doe
Portfolio B Programme B Doe
Portfolio B Programme E Franklin
Portfolio T Programme T Pepper
Portfolio A Programme C Stevens
Portfolio B Programme B Stevens
Portfolio S Programme G Perez
Portfolio A Programme A Smith
Portfolio B Programme B Smith

Put this formula in S2:

=COUNTA(P2:R2)

It should automatically copy down, and gives a count of the number of records that each row of your table should generate. Then you can put zero in cell U1 (important), and this formula in U2:

=S2+U1

Copy this down to U9, as it is outside the extent of your table, and this will give a cumulative count of the number of records.

Then you can use these formulae in the cells stated:

A15: =IFERROR(INDEX(M:O,MATCH(ROWS($1:1)-1,U:U)+1,ROWS($1:1)-INDEX(U:U,MATCH(ROWS($1:1)-1,U:U))),"")

B15: =IFERROR(INDEX(P:S,MATCH(ROWS($1:1)-1,U:U)+1,ROWS($1:1)-INDEX(U:U,MATCH(ROWS($1:1)-1,U:U))),"")

C15: =IFERROR(INDEX(B:B,MATCH(ROWS($1:1)-1,U:U)+1),"")

Copy these 3 formulae down as far as you need to (until you start to get blanks, although the number at the bottom of column U tells you how many rows you will need).

Hope this helps.

Pete

Wow. A thing of beauty! Works perfectly. I was close but would never have thought of the order of the indexing and matching! I've managed to extend it to encompass many other fields in my real sheet and avoid the dreaded COUNTA that includes blank cells with formulas! You sir, are a gentlemen! Many thanks. Very elegant for what I needed. Cheers, Tony.

Glad to help - thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete