Solved

Challenging Transpose (Excel)

Posted on 2013-05-16
2
350 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
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…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

691 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