1

Here's how my data looks now:

EDIT: I forgot to add that my need to transpose is recurring - every nth columns, I need to start to transpose again. In this example, I have it with every 2 columns but my actual dataset is every 3 columns.

Username    Product 1    Product 2    Service 1    Service 2   
Person 1    Name1        Name2        Name3        Name4
Person 2    Name5        Name6        Name7        Name8

And this is what I want my data to look like:

Username    Product  Service    (What I've come up with, formula-wise, for PRODUCT:)
Person 1    Name1    Name 3     OFFSET([Person 1],0,ROW($A$1))
Person 1    Name2    Name 4     OFFSET([Person 1],0,ROW($A$1)+1)
Person 2    Name5    Name 7     OFFSET([Person 2],0,ROW($A$1))
Person 2    Name6    Name 8     OFFSET([Person 2],0,ROW($A$1)+1)

I've don't know how to use VBA or macros, and have a lot of data so I'm also not interested in manually transposing this. Is there a formula that can work here?

I've been going crazy trying to figure out how OFFSET can work. Right now, I have a formula seen in the table above. Inside the [Person 1] and [Person 2] areas is actually an INDEX-MATCH, so that the OFFSET starts on the correct row and updates as it moves to the next person. I then have been manually copy and pasting the next set of two rows. If this sounds overly complicated, it is and worse - it's not working.

I'd be hugely appreciative of any OFFSET experts who can help me, or point me in the right direction if this has been answered before. I've been looking around, but am not sure if I'm using the correct search terms.

Kelley Hamrick
  • 197
  • 1
  • 2
  • 13
  • This called a reverse pivot and Excel has tools to do this, see: http://stackoverflow.com/questions/20541905/convert-matrix-to-3-column-table-reverse-pivot-unpivot-flatten-normal – Scott Craner Mar 14 '17 at 18:33
  • Thanks! I haven't heard that term before but it sounds like my problem :) Will look into the article you linked – Kelley Hamrick Mar 14 '17 at 18:35
  • 1
    One more note: OFFSET is a volatile function and should be avoided in large quatities as it will recalculate every time excel recalculates, While the INDEX formula like the ones given by @Jeeped are not volatile and will only recalculate when the data to which they refer changes. – Scott Craner Mar 14 '17 at 18:46
  • Thanks for letting me know Scott. I'm very comfortable with INDEX MATCH but haven't used it with other iterations - guess I should've looked into that first before researching OFFSET – Kelley Hamrick Mar 14 '17 at 18:53

2 Answers2

2

Put these two formulas in F2:G2,

=INDEX(A:A,INT((ROW(1:1)-1)/3)+2)
=INDEX(B:D, INT((ROW(1:1)-1)/3)+2,MOD(ROW(1:1)-1, 3)+1)

Fill down.

enter image description here

-2

A. This can be done simply by selecting the rows and columns.

B. Copy the selected cells either by right clicking the selection.

C. Select the first cell of the destination range into which you want to copy the transposed data.

D. Paste the copied data using Paste Special, go to Transpose feature.

E. You can find this by right clicking the destination cell and choose Paste Special from the context menu, then select Transpose.

This should work for you. Let me know how it went.

BritenGGrosi
  • 1
  • 1
  • 1
    This will not give the output the OP desired it will simply transpose rows to column and columns to rows. That is not the output desired. – Scott Craner Mar 14 '17 at 18:40