Traspose Column data into Row

Hi,
I want to transpose column data in to row. I have seen many posts (using 
INDEX or OFFSET functions) but all these solution assume a fixed block of 
data to be transposed, My problem is that the data i have not only has 
variable blocks to be transposed but also has some duplicate 
headings(headings are duplicate but the data in front of each heading has 
different value. Below is the example of data
Col A                         Col B                       Col C
PRODUCT	COST COMPONENT	COST$
A	Raw Mat	                                10
A	D L	                                         8
A	EQ	                                         4
A	IDL	                                         2
A	This Level Cost	               22
A	Raw Mat	                                 3
A	DL	                                         2
A	IDL	                                         1
A	Total Cost	                       28
B	Raw Mat	                               15
B	D L	                                        5
B	This Level Cost	               20
B	Raw Mat	                                5
B	DL	                                        2
B	Total Cost	                       27
For each product, I want to transpose Cost Component and Cost$ in the order 
listed above while keeping the duplicate Cost Components with the respective 
values.
Any Idea??
Thanks in advance
Zia 

0
Utf
1/6/2010 8:09:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
1110 Views

Similar Articles

[PageSpeed] 43

Just to further add, I have 50,000 rows of data with information for each 
product in different sets of block

"Zia" wrote:

> Hi,
> I want to transpose column data in to row. I have seen many posts (using 
> INDEX or OFFSET functions) but all these solution assume a fixed block of 
> data to be transposed, My problem is that the data i have not only has 
> variable blocks to be transposed but also has some duplicate 
> headings(headings are duplicate but the data in front of each heading has 
> different value. Below is the example of data
> Col A                         Col B                       Col C
> PRODUCT	COST COMPONENT	COST$
> A	Raw Mat	                                10
> A	D L	                                         8
> A	EQ	                                         4
> A	IDL	                                         2
> A	This Level Cost	               22
> A	Raw Mat	                                 3
> A	DL	                                         2
> A	IDL	                                         1
> A	Total Cost	                       28
> B	Raw Mat	                               15
> B	D L	                                        5
> B	This Level Cost	               20
> B	Raw Mat	                                5
> B	DL	                                        2
> B	Total Cost	                       27
> For each product, I want to transpose Cost Component and Cost$ in the order 
> listed above while keeping the duplicate Cost Components with the respective 
> values.
> Any Idea??
> Thanks in advance
> Zia 
> 
0
Utf
1/6/2010 8:14:01 AM
In EXCEL 2007:-

 - select the cells that you wish to transpose by highlighting them (in your 
example cells A 2 to C 16 inclusive if you start the data at cell A 1) 

 - Ctrl-C to copy the data 

 - go to the area of the Worksheet where you wish the transposition to be 
placed (for example I chose cell A 22) 

 - Home group / Paste / Transpose

The data will now be transposed into the area that you selected. 

If my comments have helped please hit Yes. 

Thanks. 



"Zia" wrote:

> Hi,
> I want to transpose column data in to row. I have seen many posts (using 
> INDEX or OFFSET functions) but all these solution assume a fixed block of 
> data to be transposed, My problem is that the data i have not only has 
> variable blocks to be transposed but also has some duplicate 
> headings(headings are duplicate but the data in front of each heading has 
> different value. Below is the example of data
> Col A                         Col B                       Col C
> PRODUCT	COST COMPONENT	COST$
> A	Raw Mat	                                10
> A	D L	                                         8
> A	EQ	                                         4
> A	IDL	                                         2
> A	This Level Cost	               22
> A	Raw Mat	                                 3
> A	DL	                                         2
> A	IDL	                                         1
> A	Total Cost	                       28
> B	Raw Mat	                               15
> B	D L	                                        5
> B	This Level Cost	               20
> B	Raw Mat	                                5
> B	DL	                                        2
> B	Total Cost	                       27
> For each product, I want to transpose Cost Component and Cost$ in the order 
> listed above while keeping the duplicate Cost Components with the respective 
> values.
> Any Idea??
> Thanks in advance
> Zia 
> 
0
Utf
1/6/2010 10:04:01 AM
Excel 2007 PivotTable
Transposes, subtotals, charts and more.
Generic business model for all.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/01_06_10.xlsm
0
Herbert
1/6/2010 11:40:51 PM
Reply:

Similar Artilces:

Data Consolidation
I have 20+ worksheets in a workbook. Each sheet is a timesheet for an individual and each sheet can have up to eight cost codes which will be the same on some sheets but will not be in the same position on all sheets. How do I label or set up a range so I can consolidate all of these cost codes on a seperate sheet to give me the total hours for each cost code? Could you for example do the following: Set up your new sheet with a table with all the cost codes in a column on the left, and all the people at the top. Then put a sumif, or sumproduct formulae so that it adds up all the hours ...

Not able to sort whole rows
Hi, I have a protected sheet with password say "1122" In the protection window I have checked the box of "Sort" I selected whole lines from 14 to 40 and tried the: Data|Sort and had the error message: "The cell or chart you are trying to change is protected and therefore read-only..." Any sugestion how to solve this using a macro or VBA code for a button click. I need to sort according to column F (which is hidden). Second: What code do I need to hide 2 groups of columns: DR:FQ and G:AB Unprotect, do the sort then reprotect. Sub foo() ActiveSheet.Unprot...

find which column has the maximum value
I have to check each row for the maximum value in that row. But instead of writing the maximum value of that row, I have to write the column number of that maximum value. The very first row of my dataset goes from 1 to 100, indicating 100 columns, and is there only to number the columns. So, the column number has to be picked from that row. For instance, in row 20, the maximum value is 10, and it is at column 56. How can I as output of a formula (or conditional formatting) get as answer 56? ta Try this =MATCH(MAX(20:20),20:20,0) -- HTH Bob Phillips (remove nothere from email addr...

adding trendline into data-column
After adding a trendline (moving average), I woudl like to add the values of the trendline into a column..How can I do that?? The chart trendline option is for a simple moving average, e.g. with period 3, the plotted point is the average of the current and previous two points. Just use the average function and copy the formula down. For an exponentially weighted moving average (does not seem to be among the chart options) see http://groups.google.com/groups?selm=409CD102.6050009%40no_e-mail.com Jerry Inge Jonckheere wrote: > After adding a trendline (moving average), I woudl like...

insert a row on ALL sheets
I have a spreadsheet with 12 sheets in it, one for each month of the year. Is there a way to insert a row on all 12 sheets at once or do I have to go into each sheet and insert the row one at a time? In other words, do I have to do it 12 times or is there a way to do it only once and have it affect all 12 sheets? Thanks, BW "bw" <anonymous@discussions.microsoft.com> wrote in message news:0e7301c3a9f0$6e399ae0$a301280a@phx.gbl... > I have a spreadsheet with 12 sheets in it, one for each > month of the year. Is there a way to insert a row on all > 12 sheets at onc...

Synchronizing Outlook Data
Hi, I'm reposting my question; hopefully someone will have a response that will work. I'm trying to synch my notebook PC with my desktop PC to share Outlook information. Is it possible to do this from within Outlook without buying a third-party addon? I have two copies of Outlook 2002, one on my desktop and the other on my notebook PC. I need to be able to use the calander, tasks, contacts, etc on both PCs and I cannot find any information on how to do this. Did I waste my money in buying another copy of Outlook 2002 to run on my desktop? Any help would be greatly appre...

data duplication check ?
Hi all, I have this code which runs a macro to add a complete list of all data for 'today' to be entered into a sepeate worksheet which is called the database. Is there a way, if so how, that the code can be changed so that if the 'same' data is entered twice a pop up message box is shown to alert the user of this and stop this happening. The code I have is Sub add_Anydays_jobs() With ActiveSheet.Range("A8:N34") Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset( _ 1, 0).Resize(.Rows.Count, .Columns.Count)....

Automatically moving data #2
I was at a halloween party dressed up as a 'killer bunny' One of the guests there told me to imbed "if statements within vlookup but not to use more than 8 if statments. Make any sense to you guys and gals? :confused -- multipla ----------------------------------------------------------------------- multiplan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1591 View this thread: http://www.excelforum.com/showthread.php?threadid=27391 > I was at a halloween party dressed up as a 'killer bunny' hey, I was at the party too, cloaked as t...

Duplicate Data
One main form with two subforms (linked by ProjectID), first subform is for data entry, the second just allows the user to view what has been saved from the previous subform. The subform allows users to report on a month basis by selecting a month in a combo box [PeriodID]. I would like to add some code in the Befire Update event that checks the PeriodID for a match before it is saved. Can someone help me with the code please, I have tried various DLookup snippets of code from this site, but nothing is working as the code does not really suit my purpose. regards Maybe t...

Display cells with data validation
Using 2003 - Is there a way of displaying cells that contains data validation restrictions? I've received a spreadsheet from someone with lots of restrictions on it and want to see which cells are affected. Thnks Anita "Anita" <Anita@discussions.microsoft.com> wrote in message news:19A5FB74-EF19-40C3-9632-EA0FB7F9D8B0@microsoft.com... > Using 2003 - Is there a way of displaying cells that contains data > validation > restrictions? I've received a spreadsheet from someone with lots of > restrictions on it and want to see which cells are affected. >...

Data file conversion M05 to M04
I am trying to go back from M05 to using M04. Is there utility that converts my M05 data file to M04 format? When you converted to M2005 it made a backup of your M2004 files as *.m12 and told you where it put it (usually same place as .mny). Rename this as *.mny and open with M2004. You will of course have to catch up all transactions since you converted. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. For wishes or suggestions see http://register.microsoft.com/mswish/suggestion.asp or for UK wishes ...

Holding the view of column one
Good morning, I want to hold the view of column one while working on other columns. What is the best approach to my need? Thank you, Mark Windows>Freeze panes -- Kind Regards, Niek Otten Microsoft MVP - Excel "Mark S." <Mark S.@discussions.microsoft.com> wrote in message news:0DDD2DBD-1BF5-4D11-AA96-86368E7BA8E6@microsoft.com... > Good morning, > > I want to hold the view of column one while working on other columns. What > is the best approach to my need? > > Thank you, Mark ...

How to access siebel data
How to access siebel data from vc++ -- Sivaswami Jeganathan ...

row reference
i would like to sum a series of numbers in columnB and put in cell C1. which rows to sum depends on hard inputs in cells A1 (13) and A2 (29). in other words, i want to sum the numbers from B13 to B29 in this case. i know i can do this by setting C1 to: =sum(b13:b29), but the rows to sum will be changing frequently. next time A1 and A2 might be 9 and 36, respectively, thus summing cells B9:B36. i do not want to manually change the formula in C1 every time i change A1 and A2. i also do not want to create a macro. any thoughts? thanks, mike allen A couple of ways =SUM(INDEX(B:B,A1):IND...

Store row number after a find
Following a find command I want to store the row number to a variablke and use that to move to a columb on that row hi, what column? same column? different column? Regards FSt1 "Alan" wrote: > Following a find command I want to store the row number to a variablke and > use that to move to a columb on that row Hello, Any column on that row. Real problem is storing the 'found' row number to a variable "FSt1" wrote: > hi, > what column? same column? different column? > > Regards > FSt1 > > "Alan" wrote: > > > ...

Data validation #23
Hi, I am using the Data Validation feature. I have specified a short list, but cannot select a blank cell entry even though I have the 'ignore blank' box selected?? Any ideas? Don- Press the Delete key to make a "blank" entry. That should not be blocked by Data Validation. -- Jim Rech Excel MVP "Don Niall" <donniall@aol.com> wrote in message news:1a43201c44e38$c68e5bd0$a401280a@phx.gbl... | Hi, | I am using the Data Validation feature. I have specified a | short list, but cannot select a blank cell entry even | though I have the 'ignore blank...

freezing columns labels while sorting columns
... I need to sort names in columns, but while doing that my columns labels are also being sorted. How to avoid that? Excel 2003 In Sort Dialogue Box you can see an option button "Header Row" under "My Data Range As" click it. Excel 2007 In Sort Dialogue Box you can see a check box "My data has headers" in the Right corner near to Close Button in the Sort Dialogue box. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Stan" wrote: > .. I need to sort names in columns, ...

remove rows that have any cells values strikethrough to a blank Sheet2
Hi, I am looking for help with a code that is able to cut or delete al rows that has any cell values strikethrough in any column in th currently open worksheet, and paste those rows in the blank Sheet2 fro row 2 downwards. Sheet2 must also have the Header names being copie over to Row 1. The open worksheet has cells strikethrough in all the characters, no just some characters. After the rows have been removed, the open worksheet should not contain blank rows in between. The Header names in row 1 must be i tact. Any suggestions is much appreciated. Thanks in advance ------------------...

Formula
There isa command to allow a formula to refer to a number in a cell to adjust the column that is used in the formula. BUT I CAN"T FIND IT! If I want the formula to be using cell A11, I want to have 11 in a cell and have it refer to whatever to A11. If I have 12 in the cell then I want it to refer to A12 instead. Sorry, I am not explaining the scenario very well. Help. MD -- mdalby ------------------------------------------------------------------------ mdalby's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7055 View this thread: http://www.excelfor...

Comparing data within 2 lists
I have been asked to compare lists from my 2 main systems. They believe that the information of them is not consistent. How would I compare the 2 lists in Excel since there are over 30 thousand records on them. Information: List 1 List 2 Building no. Color Building no. Color Building 1 Blue Building 2 Red Building 2 Red Building 4 Yellow Building 3 Yellow Building 5 Orange Building 4 ...

Matching data from one table and copy them to another
Hi, I have two Excel tables. Table 1 contains list of animals and their description in two columns. Table 2 contains list of animals and results of a test. Not all animals from Table 1 mach the animals in Table 2, but most of them do. I have to link those two tables in a way that test result from table 2 appears in Table 1 Column 3 but of course I must use animal ID column as a reference to match data. How can I do this? It's always a good idea to reveal ranges... That said... Table1 = A2:B100 Table2 = F2:G200 In D2 enter & copy down: =MATCH(A2,$F$2:$F$100,0) In C2 enter &am...

How to combine data from 2 separate workbooks onto 1 worksheet
I am planning a meeting for 100 attendees. Our database contains a unique ID for each attendee as well as their name, mailing address, phone number, etc. I have another database that I received from our Travel Department containing the airline information (arrival date, arrival time, flight number, arrival airport, etc). How can I merge the 2 databases together into 1 database without having to copy/paste each attendee's information individually. Both databases have the unique ID for each attendee - is there a way to have Excel "find" the unique ID and then ad...

eliminating unwanted data
Column a has data, column b has data. I want ap1 to equal q1 if a1 equals b1. Actually I am looking for a way to eliminate rows if data in one column appears in a second column. Thank you Your question is confusing. Do you simply want to delete rows where the value in column A equals value in column B? I don't see the relevance of making ap1 to equal q1 if you are deleting the row anyway where you have 2 equal values. Might as well compare A and B and delete. Anyway the following is a sample of deleting rows based on a condition. It firstly sets the interior ...

Excel Column References
My spread sheets have spontaneously switched from giving column refs as letters to numbers. Instead of A, B, C etc they now show 1,2,3.......... Why has this happened and, more to the point, how can I correct it? Thanks in advance for advice Nic <Tools> <Options> <General> tab, *Uncheck* R1C1 reference style. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Nic Siddle" <NicSiddle@nsiddle.freeserve.co.uk> wrote in message news...

Excell, why oh why can't you just move rows up and down !!!!!
You know, back before excell, when it was multiplan on the original Mac's, you could easily move rows and columns, inserting them INBETWEEEEEN other rows and colums. Twenty years later you still havn't corrected the idiotic proceedure required to move rows and columns in Excel. When you drag or paste a row or column it should default to slipping in INBETWEEEEEN the others. You could even have a cute little bar appear to indicate the difference between inserting INBETWEEEEEEN things and overwriting them (like Multiplan had). I have looked and looked for years and can'...