Transpose horizontal to vertical

[akalien]

Hi,

I have a list with 9 different persons (A2:A10) and each person has 7 different areas in column B to H

Now I want to have the horizontal areas transposed vertically in one column and showing for each area the name of the person in charge.

Many thanks in advance

[Pete_UK]

You could put this formula (say in K2):

=INDEX(A:A,INT((ROWS($1:1)-1)/7)+2)

and this one in L2:

=INDEX(B:H,INT((ROWS($1:1)-1)/7)+2,MOD(ROWS($1:1)-1,7)+1)

Note that you may need to use semicolons ( ; ) instead of commas ( , ) in the formulae, depending on your regional settings. Then you can copy the formulae down as far as you need them.

Hope this helps.

Pete

[WideBoyDixon]

If the Areas are really numbers then you could retrieve them using SMALL like this in J2 downwards:

Please Login or Register  to view this content.

If they could be non-numeric then you might need something like this:

Please Login or Register  to view this content.

then K2 becomes:

Please Login or Register  to view this content.

See attached.

WBD

[akalien]

Many thanks, that's what I was looking for!!!

What if, for any reason, the number of areas is not 7 but between 1 and 7?

Many thanks for this

[Pete_UK]

Do you mean a variable number for each name, or a fixed number for all names (which might vary from 1 to 7)? It would be better to attach a new file illustrating your new requirements.

Pete

[akalien]

Names are unique and areas could be variable from 1 to 7

[alansidman]

An alternative solution is with Power Query

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[WideBoyDixon]

Are areas always numeric? If they are then see attached.

WBD

[akalien]

Areas are always numeric

[Pete_UK]

I've used a different approach, with helper columns in J and K with these formulae:

J2: =COUNT(B2:H2)

K2: =J2+K1

and with zero in K1 (important). These two formulae count the number of records for each name in column J, and column K just gives a cumulative count.

Then I've used these formulae:

M2: =INDEX(A:A,MATCH(ROWS($1:1)-1,K:K)+1)

N2: =INDEX($B$2:$H$10,MATCH(ROWS($1:1)-1,K:K),MATCH(ROWS($1:1)-1,K:K)+COUNTIF(M$2:M2,M2)-MATCH(M2,A:A,0)+1)

N2 has a Custom Format of 000, and then the formulae in M2:N2 are copied down to give the complete set of data.

Hope this helps.

Pete

EDIT: Sorry for the delay - I had a long phone call mid-way through.

[akalien]

Perfect aaa+++

[Pete_UK]

You have had a number of people helping you, so it is not clear to whom you are addressing your comment, but thanks for marking the thread as Solved.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete