Changing Column Data into Row Data

**bapswarrior** · 05-12-2016, 05:00 PM

Hey Genius Community

I have a ton of data (attached a sample) that is driven by formula. The way I have the data set up is I would have A,B,C,D..... in column A and on the top row I have YTD, 1 year, 3 year and 5 year..... and so on column headers. I want to be able to pivot and slice data and therefore want data to be in the following format (example file: "Desired Output) A,B,C,D in col A and YTD, 1year, 3 year and 5 year in col B and each associated value to be in each row corresponding accordingly. I want this in such that when original data changes so should that output. I have tried using the pivot trick and double clicking on grand total and then pivoting off that but it doesn't work.

Please help. TIA

**Pete_UK** · 05-12-2016, 05:08 PM

Do you want the new format on another sheet, or to the right of the data that you currently have?

Pete

**bapswarrior** · 05-12-2016, 05:15 PM

Another sheet is preferred. I have a lot of data...

**Pete_UK** · 05-12-2016, 05:23 PM

Okay, assuming you have suitable headings in row 1 of Sheet2, you can use these formulae in the cells stated:

A2: =INDEX(Sheet1!A:A,INT((ROWS($1:1)-1)/4)+3)

B2: =INDEX(Sheet1!$B$2:$E$2,MOD(ROWS($1:1)-1,4)+1)

C2: =INDEX(Sheet1!$B$3:$E$11,INT((ROWS($1:1)-1)/4)+1,MOD(ROWS($1:1)-1,4)+1)

The 11 in the final formula (shown in blue) is to suit your sample data - change this to suit your real data, then you can copy these formulae down as far as you need to, and they will respond immediately to changes in the original data.

Hope this helps.

Pete

**bapswarrior** · 05-13-2016, 03:21 PM

at Pete_UK - anyway to explain the C2 formula?

**Pete_UK** · 05-13-2016, 04:58 PM

The syntax for INDEX is:

INDEX(table,row,column)

It will return the element of the table from the appropriate row and column values. The row term is thus:

INT((ROWS($1:1)-1)/4)+1

and when this is copied down the ROWS($1:1) becomes ROWS($1:2), then ROWS($1:3) and so on in successive rows. These terms will return 1, then 2, then 3 etc., and you can see that 1 is subtracted from those values each time (resulting in 0, 1, 2 etc.) and the formula takes the integer value of that number divided by 4, so we will get 0, 0, 0, 0, 1, 1, 1, 1, 2, 2, and so on, to which 1 is added, so we will get data from the first row of the table four times, and then from the second row of the table four times, and so on.

The column term is given by:

MOD(ROWS($1:1)-1,4)+1

and MOD returns the remainder after division, so the ROWS($1:1)-1 will return 0, 1, 2, 3, 4, 5 etc. when the formula is copied down. The MOD(...,4) will return 0, 1, 2, 3, 0, 1, 2, 3, 0, 1, 2, and so on, and we add one on to those values, so we are getting data from the first column of the table, then the second column, then the third, then the fourth, and then back to the first column, etc. as the formula is copied down.

Hope this helps.

Pete

P.S. If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Changing Column Data into Row Data

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Changing Column Data into Row Data

Re: Changing Column Data into Row Data

Re: Changing Column Data into Row Data

Re: Changing Column Data into Row Data

Re: Changing Column Data into Row Data

Re: Changing Column Data into Row Data

Thread Information

Users Browsing this Thread

Similar Threads

Send emails in outlook from a column of addresses while grabbing row data/changing data

[SOLVED] Another formula for changing data in column A if data in column B matches

Renaming/changing data in a column ...??

Excel 2007 : Changing all Data in a Certain Column

Changing the data in a column

Changing entire column data

Changing data in a column

Bookmarks

Bookmarks

Posting Permissions