Solved

Challenging Transpose (Excel)

Posted on 2013-05-16
2
309 Views
Last Modified: 2013-05-16
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
0
Comment
Question by:ExpExchHelp
2 Comments
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
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
 

Author Comment

by:ExpExchHelp
Comment Utility
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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now