Extracting data from Multiple columns to a Single row

[Andy McGooner]

Hi All,

I have a spread sheet that contains many columns of data which is split into 4 data types per month (Note extract below only shows 3 and the first 3 months);

Feb-14 Mar-14 Apr-14
PM0 PM4 PM6 PM0 PM4 PM6 PM0 PM4 PM6
Total 1 291 716 1 298 722 1 291 721
Total 1 1 262 644 2 265 655 2 262 648

Now i an trying to extract each of the data inputs into one row show as youcan see below;

Feb-14 Mar-14 Apr-14
PM0 - Total 1 1 2 2
PM4 - Total 1 262 265 262
PM6 - Total 1 644 655 648

Now I can do this manually by referencing each cell but as I said there aremultiple columns and worksheets associated with this. I also thought I could maybe do a Vlookup and then maybe drag it across the cells. however it needs to be able to count every 4th row or again its along tedious route to change the formula. Therefore is there a means of doing this or maybe an alternative method which may do this easier.

Anyway any help would be greatly appreciated.

Thanks in advance,

Andy
Excel 2010

 

[Ron Rosenfeld]

Hi All,

I have a spread sheet that contains many columns of data which is split into 4 data types per month (Note extract below only shows 3 and the first 3 months);

Feb-14 Mar-14 Apr-14
PM0 PM4 PM6 PM0 PM4 PM6 PM0 PM4 PM6
Total 1 291 716 1 298 722 1 291 721
Total 1 1 262 644 2 265 655 2 262 648

Now i an trying to extract each of the data inputs into one row show as you can see below;

Feb-14 Mar-14 Apr-14
PM0 - Total 1 1 2 2
PM4 - Total 1 262 265 262
PM6 - Total 1 644 655 648

Now I can do this manually by referencing each cell but as I said there are multiple columns and worksheets associated with this. I also thought I could maybe do a Vlookup and then maybe drag it across the cells. however it needs to be able to count every 4th row or again its along tedious route to change the formula. Therefore is there a means of doing this or maybe an alternative method which may do this easier.

Anyway any help would be greatly appreciated.

Thanks in advance,

Andy
Excel 2010

I'm not sure you've provided enough information.

If your data is absolutely in the format you display, with the cycle of data types repeating sequentially and in the same order; and the Total 1 row being what you want to extract, then

Given:

B5: 1 (Total 1 for PM0 Feb-14)

You could use a formula like:

C12: =OFFSET($B$5,0,(ROWS($1:1)-1)+(COLUMNS($A:A)-1)*3)

Fill down 3, then select C12:C14 and fill right as far as required. The column offset should compute appropriately.

Since you write that you have four data types, you will need to change the multiplier at the end from '3' to '4'.

Put in the headings/labels as appropriate in columns a:b and row 11

But if your data is not exactly as you show, that won't work properly, so you will need to provide more information.

 

[Andy McGooner]

Hi Ron,

Much appreciated that worked a treat, will stick this in my useful code file for future reference. You are a star as this was doing my head in. Anywayagain thanks for your help it was greatly appreciated, hopefully i can return the favour sometime in the future.

Andy

 

[Ron Rosenfeld]

Hi Ron,

Much appreciated that worked a treat, will stick this in my useful code file for future reference. You are a star as this was doing my head in. Anyway again thanks for your help it was greatly appreciated, hopefully i can return the favour sometime in the future.

Andy

Glad to help. Thanks for the feedback.