Transponse just some columns in excel

Question

I have a worksheet with columns similar to the below

name | id | contact | category | week 1 | week 2 | week 3 | ... |week 52

What I need to do is transpose the 'week' columns into rows, so I end up with:

name | id | contact | category | week

With an entry for each week as a row in the s/sheet - thus making a long list on rows with the column data for each week.

example current format:
jones | 12345 | simon | electronics | 100 | 120| 130| 110 | ..........150

I have tried the usual excel transpose (via paste) but cannot get the first few columns to stay static, whilst transposing the week columns

Ideally I would like to achieve this within excel, but I can import the data into a mysql database and use that if the solution would be easier that way

Hope this makes sense

[added examples]

Do you only have a single row or are there multiple rows before the transpose? — Mr. Mascaro
multiple rows (about 200 in total) - added examples in description above for clarity — golles
thanks @RichardLeMesurier - took me a while to figure it out but works well - thanks very much for your help. — golles
Good. Its a different way of thinking about the problem, but quite simple when you realise what's being done and how. — Richard Le Mesurier

Richard Le Mesurier Richard Le Mesurier · Accepted Answer · 2014-09-29T15:27:23

I would do the work on a second sheet, which uses the INDIRECT function to do the lookups for you:

http://www.excelfunctions.net/Excel-Indirect-Function.html

Start by setting up some indexes on the new sheet - we will use these to indirectly look up into the original sheet and pull the data across.

I would count up to 52 again and again in column A, starting with a 1 in A2, and using this formula below:

=if(A2=52,1,A2+1)

This would be my count of the weeks per person.

In column B, I would count my people, starting with a 1 in B2, and using this formula:

=if(A3=1,B2+1,B2)

This gives me the row and column offsets to use in the INDIRECT function to fetch the data from your original sheet.

Now the fun part - matching these row and column offsets to your actual data.

Lets assume your original data is in a sheet called "original". This is where we need to look up the data.

We will map the original column A into the new sheet's column C. So C2 can hold this formula:

=indirect("original!R"&($B2+1)&"C1",false)

What you are doing there is looking in the row that you calculated in the B column (formula above), and looking in the first column of that row (i.e. column A) - this is where the Name is stored.

Similarly, the "id", "contact" and "category" columns get mapped to new sheet columns D, E, F, using modifications of that formula:

=indirect("original!R"&($B2+1)&"C2",false)
=indirect("original!R"&($B2+1)&"C3",false)
=indirect("original!R"&($B2+1)&"C4",false)

Only the column offset gets changed in these updates.

To pull the weekly data across, we use a similar formula; the difference is that now we get to use the newly calculated column A, where we counted up from 1 to 52 over and over.

So G2 becomes:

=indirect("original!R"&($B2+1)&"C"&(4+$A2),false)

Copy this all down as far as you need, and hide columns A and B.

Transponse just some columns in excel

1 Answers