Login | Register
The Engineer's Place for News and Discussion®

Previous in Forum: Quantify Welding Tasks   Next in Forum: Memory Information
Close

Comments Format:






Change Mode
Close

Subscribe to Discussion:

CR4 allows you to "subscribe" to a discussion
so that you can be notified of new comments to
the discussion via email.

Close

Rating Vote:







Add Vote
11 comments
Subscribe Rate this Comments: Nested
cxcxcx0505
Associate

Join Date: Mar 2012
Posts: 27

[Excel] Moving Data from Rows to Columns

03/29/2012 9:10 PM

I have excel spreadsheet that have many rows and columns like below

Project Job No. Item ...

Shell 11/123/SS HE123

Petronas 11/124/SS HE124

...

...

I want to create another link that looks like below. Any change made to above will also change to below.

Project:Shell

Job No.:11/123/SS

Item :HE123

Project:Petronas

Job No.:11/124/SS

Item :HE124

There are about 200++ rows in my spreadhsheet. I know that I can do it by inside the value of the cell like below. But, it is very time wasting. Is there any other method that I can use? I cant do it by select the rows and drag it below.

Project: =B2

Job No.: =C2

Item : =D2

Project: =B3

Job No.: =C3

Item : =D3
Register to Reply
Interested in this topic? By joining CR4 you can "subscribe" to
this discussion and receive notification when new comments are added.

Comments rated to be Good Answers:

These comments received enough positive ratings to make them "good answers".

Comments rated to be "almost" Good Answers:

Check out these comments that don't yet have enough votes to be "official" good answers and, if you agree with them, rate them!
abele
Associate

Join Date: Sep 2011
Posts: 29
Good Answers: 1
#1

Re: [Excel] Moving Data from Rows to Columns

03/30/2012 12:43 AM
Sr. no.ProjectJob no.Item
1shell11/123/ssHE 123
2petronas11/124/ssHE 124

Take a New Sheet

Type Project Name (Manual Entry)shell
Job NO.=+LOOKUP("C10",B4:D5,C4:C5)
Item=+LOOKUP("C10",B4:D5,D4:D5)
Register to Reply
cxcxcx0505
Associate

Join Date: Mar 2012
Posts: 27
#6
In reply to #1

Re: [Excel] Moving Data from Rows to Columns

03/30/2012 10:21 PM

what is the meaning of the symbol "+" at the front of lookup?

And, what is the "C10", which is the first parameter mean?

Thanks.
Register to Reply
2
nick name
Guru

Join Date: Mar 2007
Location: City of Light
Posts: 3311
Good Answers: 126
#2

Re: [Excel] Moving Data from Rows to Columns

03/30/2012 8:03 AM

Try with the command copy and paste special with "transpose".

It copies the matrix as column 1 to row 1 and so on.
Register to Reply Good Answer (Score 2)
No more Good Answers. Go to first "Almost" Good Answer
phoenix911
Guru
Hobbies - CNC - New Member Hobbies - DIY Welding - New Member Engineering Fields - Electromechanical Engineering - New Member

Join Date: Aug 2007
Posts: 8728
Good Answers: 100
#3
In reply to #2

Re: [Excel] Moving Data from Rows to Columns

03/30/2012 8:34 AM

Excellent reply.

__________________
phoenix911
Register to Reply
Doorman
Guru
Hobbies - DIY Welding - New Member, but planning to be an Old Member Popular Science - Weaponology - New Member

Join Date: Apr 2009
Location: Fargo, America, USA
Posts: 5167
Good Answers: 189
#4
In reply to #2

Re: [Excel] Moving Data from Rows to Columns

03/30/2012 10:56 AM

Agree with p911: GA.

__________________
Semper Ubi Sub Ubi
Register to Reply
cxcxcx0505
Associate

Join Date: Mar 2012
Posts: 27
#5
In reply to #2

Re: [Excel] Moving Data from Rows to Columns

03/30/2012 8:08 PM

but I need the dynamic link that any change to the above will also cause change to below. If I just copy and transpose, it will just have one time change.
Register to Reply
nick name
Guru

Join Date: Mar 2007
Location: City of Light
Posts: 3311
Good Answers: 126
#7
In reply to #5

Re: [Excel] Moving Data from Rows to Columns

03/31/2012 4:50 AM

As far as I know the transpose is valid for the cell with its links and functions. Have you made a trial which did not work or is it only a presumption?

Make a trial with a small matrix for instance 5x8 and see what happens.

Then if it does not work try another way.
Register to Reply
Data
Power-User
Canada - Member - New Member Engineering Fields - Control Engineering - New Member Engineering Fields - Chemical Engineering - New Member Technical Fields - Technical Writing - New Member

Join Date: May 2006
Location: Saskatchewan, Canada
Posts: 103
Good Answers: 2
#11
In reply to #5

Re: [Excel] Moving Data from Rows to Columns

04/02/2012 12:02 PM

Have you tried using the TRANSPOSE function in a formula? For example, if the data as you have given it is in cells A1:C3, you could enter the array formula =TRANSPOSE(A$1:C$1,E1:E3) into the array E1:E3, and =TRANSPOSE(A$1:C$1,E4:E6) into the array E4:E6, etc. to get the repeated labels down the first column. Then you would enter =TRANSPOSE(A2:C2,F1:F3) into array F1:F3, and =TRANSPOSE(A3:C3,F4:F6) into array F4:F6 etc. to get the data. This would be a lot of work the first time, since with the array formulas I couldn't figure out a way to "copy down" correctly, but once you got it all entered, it would be dynamic as you require. (These are only very brief instructions - I would suggest that you use the Excel Help to make sure that you get the details correct, especially if you are not very familiar with array formulas.)

I hope this helps!

__________________
The most exciting phrase to hear in science, the one that heralds new discoveries, is not "Eureka" but rather "Hmmmm... that's funny". - Isaac Asimov
Register to Reply
phph001
Power-User

Join Date: Oct 2009
Posts: 381
Good Answers: 18
#8

Re: [Excel] Moving Data from Rows to Columns

03/31/2012 8:33 AM

This could be a job for a pivot table:

http://chandoo.org/wp/2009/08/19/excel-pivot-tables-tutorial/
Register to Reply
plutokrat
Member

Join Date: Jan 2012
Location: Gulf Coast--Alabama
Posts: 6
#9

Re: [Excel] Moving Data from Rows to Columns

03/31/2012 12:26 PM

OMG its so obvious. Shoot Bill G an email!!!!
Register to Reply
PaddyM1
Participant

Join Date: Apr 2012
Posts: 1
#10

Re: [Excel] Moving Data from Rows to Columns

04/02/2012 5:26 AM

The easiest way I could find was to add a column numbering each project as follows:

No.

Project

Job No

Item

1Shell11/123/SSHE123
2Petronas11/124/SSHE124

Then add a second sheet (which I called "Lookups" to the workbook looking up the first sheet, as follows:

1=VLOOKUP($A1,Sheet1!$A:$E,2,FALSE)
1=VLOOKUP($A2,Sheet1!$A:$E,3,FALSE)
1=VLOOKUP($A3,Sheet1!$A:$E,4,FALSE)
1
=A1+1=VLOOKUP($A5,Sheet1!$A:$E,2,FALSE)
=A5=VLOOKUP($A6,Sheet1!$A:$E,3,FALSE)
=A6=VLOOKUP($A7,Sheet1!$A:$E,4,FALSE)
=A7
=A8+1=VLOOKUP($A9,Sheet1!$A:$E,2,FALSE)
=A9=VLOOKUP($A10,Sheet1!$A:$E,3,FALSE)
=A10=VLOOKUP($A11,Sheet1!$A:$E,4,FALSE)
=A11

A simple "copy down" will allow you to create a "template" for as many rows as you are ever likely to need, and you can then add a 3rd sheet (which I called "Outputs")to show a "tidy" set of results using the following formulae (again copied down):

=IF(ISNA(Lookups!B1),0,IF(ISBLANK(Lookups!B1),0,"Project"))=IF(ISNA(Lookups!B1),"",Lookups!B1)
=IF(ISNA(Lookups!B2),0,IF(ISBLANK(Lookups!B2),0,"Job No.:"))=IF(ISNA(Lookups!B2),"",Lookups!B2)
=IF(ISNA(Lookups!B3),0,IF(ISBLANK(Lookups!B3),0,"Item"))=IF(ISNA(Lookups!B3),"",Lookups!B3)
=IF(ISNA(Lookups!B4),0,IF(ISBLANK(Lookups!B4),0,""))=IF(ISNA(Lookups!B4),"",Lookups!B4)
=IF(ISNA(Lookups!B5),0,IF(ISBLANK(Lookups!B5),0,"Project"))=IF(ISNA(Lookups!B5),"",Lookups!B5)
=IF(ISNA(Lookups!B6),0,IF(ISBLANK(Lookups!B6),0,"Job No.:"))=IF(ISNA(Lookups!B6),"",Lookups!B6)
=IF(ISNA(Lookups!B7),0,IF(ISBLANK(Lookups!B7),0,"Item"))=IF(ISNA(Lookups!B7),"",Lookups!B7)
=IF(ISNA(Lookups!B8),0,IF(ISBLANK(Lookups!B8),0,""))=IF(ISNA(Lookups!B8),"",Lookups!B8)

It's not the prettiest, but easy to set up and shows exactly what you need.
Register to Reply Score 1 for Good Answer
No more "Almost" Good Answers.
Register to Reply 11 comments
Interested in this topic? By joining CR4 you can "subscribe" to
this discussion and receive notification when new comments are added.

Comments rated to be Good Answers:

These comments received enough positive ratings to make them "good answers".

Comments rated to be "almost" Good Answers:

Check out these comments that don't yet have enough votes to be "official" good answers and, if you agree with them, rate them!
Copy to Clipboard

Users who posted comments:

abele (1); cxcxcx0505 (2); Data (1); Doorman (1); nick name (2); PaddyM1 (1); phoenix911 (1); phph001 (1); plutokrat (1)

Previous in Forum: Quantify Welding Tasks   Next in Forum: Memory Information


"ABILITY is what you are capable of doing. MOTIVATION determines what you do. ATTITUDE determines how well you do it." -- Lou Holtz

All times are displayed in US/Eastern (EDT) (Register to change time zone)

Got Something to Say?
Ask a Question
Start a Discussion

Search this Forum
Software & Programming:
Go

Search all of CR4
Go

CR4 Sections
Aerospace
BioMech & BioMed
Chemical & Material Science
Civil Engineering
Communications & Electronics
Education and Engineering Careers
Electrical Engineering
General
Instrumentation
Manufacturing
Mechanical Engineering
New Technologies & Research
Software & Programming
Sustainable Engineering
Transportation

Site Directories
Forum Directory
Blog Directory
User Group Directory
RSS Feeds
CR4 Store

CR4 Rules of Conduct
CR4 FAQ
Site Glossary

Who's Online (140 right now)
Show Members