Transpose data from one sheet to another automatically

**mchambersusmc** · 07-23-2015, 06:11 PM

Hi I am not sure if this is the right place for this post so I'm sorry if not. I have a lot of data that I need to put into one worksheet and then transpose it to another. It is getting tedious to do this over and over. I want to be able to place data in the first worksheet and have it automatically placed in the correct column on another worksheet. I have attached my workbook to this. What I need is to do is move data from row 1-11 and place it in its designated column in the transpose worksheet under the data column in each number.
Please help I am pulling whats left of my hair out.
Thanks

**jjhayes** · 07-23-2015, 07:06 PM

I did the following:

1) Created named ranges for the various years, like the screenshot below

2) Unmerged the system number headings on the Transposed Sheet (Rows 1, 54, etc.)

3) Added the formula below in the data column for each system ("1", "2', etc.) on the Transposed Sheet. It uses an index/match and Indirect based off the named ranges which tie to the years in columns B, G and K on the Transposed Sheet.

Please Login or Register  to view this content.

Note that the formula above needs to be modified for each group of three systems to point to the correct row (Row 1, 54, etc.)

I don't know what is happening on the "dfg" column on the Transposed Sheet, so can't help there.

**mchambersusmc** · 07-23-2015, 07:31 PM

jjhayes,
Thanks for the response. Sorry I didn't explain that the dfg column is fine the way it is. I know how to fix that I got to populate the data column. When I used your formula it is moving the data from each date on the data sheet and placing it on the transpose sheet in each date. I need to take Jan- Dec numbers on the data sheet for #1 and place it in the data column on the transpose sheet under section number 1. I'm not very good at explaining this so I highlighted in red on the attached workbook. I really need what is red on the data sheet to be placed on the red in the transposed sheet.
I really appreciate it

**FDibbins** · 07-23-2015, 07:31 PM

Here is another option...
=INDEX('Data Sheet'!$B:$M,MATCH(DATE(YEAR(B3),1,1),'Data Sheet'!$B:$B,0)+B$1,MONTH('Transposed Sheet'!$B3))
copied down

You will need to adjust the bolder part for the tables below (just change it to $B54, for instance), then, again, copy down and across

**mchambersusmc** · 07-23-2015, 07:48 PM

FDibbins,
Thank you so much. You just made my life a lot easier. I couldn't get the lower tables to work but I can just move those tables to the top of my work sheet.
Again thank you

**FDibbins** · 07-23-2015, 07:52 PM

You could move those other tables up, it would make for less editing

If you did not want to do that, then copy my formula from C3 (if thats where you put it) to C56, and make this change...
=INDEX('Data Sheet'!$B:$M,MATCH(DATE(YEAR(B3),1,1),'Data Sheet'!$B:$B,0)+B$1,MONTH('Transposed Sheet'!$B3))
to
=INDEX('Data Sheet'!$B:$M,MATCH(DATE(YEAR(B3),1,1),'Data Sheet'!$B:$B,0)+B$54,MONTH('Transposed Sheet'!$B3))

**mchambersusmc** · 07-23-2015, 07:59 PM

Oh wow ok I changed it to B$56 instead of 54!! OK it works perfectly now.
Thanks!

**FDibbins** · 07-23-2015, 08:03 PM

haha, At one point, I did that as well

**mchambersusmc** · 07-23-2015, 08:11 PM

FDibbins,
Is there anything I need to change to that formula if I need to put it in another workbook with different sheet names? I realize I have to change the sheet names in the formula. I did that and I am getting #VALUE!. This other workbook is set up the exact way except names are different.

**FDibbins** · 07-23-2015, 08:26 PM

If the layout is identical, then changing the sheet names should work. Did you change all 3 sheet refs?

=INDEX('Data Sheet'!$B:$M,MATCH(DATE(YEAR(B3),1,1),'Data Sheet'!$B:$B,0)+B$1,MONTH('Transposed Sheet'!$B3))
(also, did you include the ' at the start of the sheet name and '! at the end of the sheet name?)

Thanks for the feedback, too

**mchambersusmc** · 07-23-2015, 08:34 PM

Yeah I am pretty sure I changed them correctly. I attached the current workbook hopefully you have some more insight for me!

**mchambersusmc** · 07-23-2015, 09:34 PM

So looking at it I can get it to work if I change the title in cell B1 from Fitchville ... to 1. Is there anyway that I could possible get around that?
=INDEX('Data Sheet'!$B:$M,MATCH(DATE(YEAR(B3),1,1),'Data Sheet'!$B:$B,0)+B$1,MONTH('Transposed Sheet'!$B3))
This is the portion of the formula that corresponds with it.

**FDibbins** · 07-24-2015, 01:01 AM

Sorry for the delay, RL interfered

If you can modify the headings on sheet 2 so that they match those on sheet 1...
Fitchville Substation (No. 8301)
to just...
Fitchville
(or change sheet1 to match sheet2, either way), then you can use this instead...
=INDEX('Billing Statements kW'!$B:$M,MATCH(DATE(YEAR(B3),1,1),'Billing Statements kW'!$B:$B,0)+MATCH(B$1,'Billing Statements kW'!$A$3:$A$13,0),MONTH('KW Station Summary'!$B3))

This assumes that all the row headings in sheet 1 will be the same for each set of years

(this whole thing could be made far simpler if you had everything on sheet1 in 1 big table, with say, dates going down and names going across the top - something like this...

	R	S	T	U	V	W	X	Y	Z	AA
2		Fitchville	New London	Ashland	Jeromesville	Coulter	Steuben	New Mifflin	S. Greenwich	Boughtonville
3	Jan-12	3,670	5,891	5,441	2,785	2,279	4,247	2,366	1,715	1,927
4	Feb-12	3,424	5,593	4,971	2,543	2,223	3,984	2,074	1,585	1,833
5	Mar-12	3,274	5,351	4,402	2,363	2,013	3,667	1,823	1,754	1,744
6	Apr-12	2,765	4,549	3,749	1,960	1,752	3,139	1,535	1,206	1,517
7	May-12	2,974	5,503	4,161	2,085	2,534	3,900	1,496	1,377	1,727
8	Jun-12	3,425	6,518	4,863	2,604	2,732	4,561	1,736	1,634	1,990
9	Jul-12	3,897	6,674	5,194	2,775	3,038	4,983	1,947	1,792	2,145
10	Aug-12	3,607	6,093	4,566	2,441	2,691	4,231	1,659	1,503	1,954
11	Sep-12	2,999	5,724	4,150	2,202	2,269	3,720	1,518	1,373	1,716
12	Oct-12	3,128	4,986	4,206	2,196	1,921	3,538	1,676	1,371	1,744
13	Nov-12	3,107	5,092	4,443	2,317	1,955	3,638	1,750	1,432	1,755
14	Dec-12	3,331	5,441	4,956	2,486	2,240	4,154	2,008	1,732	1,955
15	Jan-13	3,884	6,291	5,697	2,940	2,417	4,596	2,371	1,887	2,131
16	Feb-13	3,609	6,003	5,357	2,836	2,324	4,206	2,186	1,761	2,042

the formula would then just be...
=INDEX('Billing Statements kW (2)'!$S$3:$AB$38,MATCH('KW Station Summary'!$B3,'Billing Statements kW (2)'!$R$3:$R$38,0),MATCH('KW Station Summary'!B$1,'Billing Statements kW (2)'!$S$2:$AC$2,0))
instead of...
=INDEX('Billing Statements kW'!$B:$M,MATCH(DATE(YEAR(B3),1,1),'Billing Statements kW'!$B:$B,0)+MATCH(B$1,'Billing Statements kW'!$A$3:$A$13,0),MONTH('KW Station Summary'!$B3))

**FDibbins** · 07-24-2015, 01:01 AM

deleted my duplicate post

**mchambersusmc** · 07-24-2015, 09:21 AM

Ok that is amazingly helpful. Thank you so much.

**mchambersusmc** · 07-24-2015, 09:32 AM

If it would let me give you more feedback I would. I really appreciate the time you took to help me out. Thank you

**FDibbins** · 07-24-2015, 09:56 AM

IM just happy we were able to get you where you wanted to be, thank you

Transpose data from one sheet to another automatically

LinkBack

Thread Tools

Rate This Thread

Display

Transpose data from one sheet to another automatically

Re: Transpose data from one sheet to another automatically

Re: Transpose data from one sheet to another automatically

Re: Transpose data from one sheet to another automatically

Re: Transpose data from one sheet to another automatically

Re: Transpose data from one sheet to another automatically

Re: Transpose data from one sheet to another automatically

Re: Transpose data from one sheet to another automatically

Re: Transpose data from one sheet to another automatically

Re: Transpose data from one sheet to another automatically

Re: Transpose data from one sheet to another automatically

Re: Transpose data from one sheet to another automatically

Re: Transpose data from one sheet to another automatically

Re: Transpose data from one sheet to another automatically

Re: Transpose data from one sheet to another automatically

Re: Transpose data from one sheet to another automatically

Re: Transpose data from one sheet to another automatically

Thread Information

Users Browsing this Thread

Similar Threads

Transpose Data automatically

[SOLVED] Automatically transpose the data in sheet 1 rows into columns in sheet 2.

Automatically transpose when pasting data in excel

Automatically transpose when pasting data in excel

[SOLVED] Automatically transpose when pasting data in excel

Automatically transpose when pasting data in excel

[SOLVED] Automatically transpose when pasting data in excel

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions