Solved

Challenging Transpose (Excel)

Posted on 2013-05-16
2
323 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 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39171925
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
ID: 39172046
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

861 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

26 Experts available now in Live!

Get 1:1 Help Now