Transpose range of columns into rows, then repeat below that row

Question

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.

Answer 1

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.