Dear Forum Members,
I want to transpose a table and fill empty cells with responses as can be seen in the attached image.
So the steps that need to be performed are
1. Transpose Answers by ID and put them into a column
2. Take the Question from Row 1 and put them in the next column, do this for each ID
3. Take the ID, put it into the next row and copy it for every single question&answer set.
Can you help me with this?
Kindest regards
Screen Shot 2017-11-20 at 13.09.05.png
For your example layout, in A9 use
=IFERROR(INDEX($A$3:$A$5,INT((ROW(A1)-1)/COUNTA($1:$1)+1)),"")
In B9, use
=IF(A9="","",INDEX($1:$1,1,MOD((ROW(A1)-1),COUNTA($1:$1))+2))
In C9, use
=IF(A9="","",OFFSET($B$3,INT((ROW(A1)-1)/COUNTA($1:$1)),MOD((ROW(A1)-1),COUNTA($1:$1))))
and copy down until the formulas return blanks.
Bernie Deitrick
Excel MVP 2000-2010
Or try this without the use of OFFSET
In A9, B9 and C9 respectively, and copy down
Formula:
Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
Where A1 is =COUNTA($2:$2)
See the file
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks