Challenging Transpose (Excel)

I need some help with automating (maybe via macro) a "2-step transpose" function.

Please see attached XLS for details.   I've highlighted the cells that need to be transposed AND realigned.

The rows are indicative of a survey response.   Right now, I have 3 test surveys; however, that number will grow.

Once transposed, the columns will be remain at four.   So, a 4th user would populate cell range F49:J59.

The data shown is cell range F13:J47 (for 3 users) is the desired final product.   The data shown as step #1 (A13:D59) does NOT need to be displayed.   (It's merely included for illustrating the current manual process).

Does anyone know how to automate this process?    I'd prefer to NOT just use cell reference and keep copying them down.   Instead, I'd like to select the cell range of survey participants and then, e.g., run a macro to perform the transpose/realign functions.

Thanks in advance,
EEH
Transpose.xls
ExpExchHelpAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
byundtConnect With a Mentor Commented:
The following formula may be copied down and across to return the properly transposed values:
=IF(SUMPRODUCT((INDEX(Data,1,)=$A13)*INDEX(Data,COUNTIF($A$13:$A13,$A13)+1,))=0,"",INDEX(Data,COUNTIF($A$13:$A13,$A13)+1,MATCH($A13,INDEX(Data,1,),0)+(COLUMNS($B13:B13)-1)*11))

This formula uses a named range Data. In the sample workbook, Data refers to cells A2:AR5 (in other words, it includes both the header labels and the responses).

The formula is shown in action in worksheet "Formula Solution"
TransposeQ28130165.xls
0
 
ExpExchHelpAuthor Commented:
byundt:

As always, you're providing the most excellent solutions!!!

Moving the transpose function to a separate tab should be straight-forward now.

Again, thanks!

EEH
0
All Courses

From novice to tech pro — start learning today.