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
495 Views

Similar Articles

[PageSpeed] 29

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:

Matching values from two columns and making a third column with the results
I have a list of products in an Excel/CSV table I've been given a disk with images for about a 2/3 of the products in the table. Fortunately, the images match the product code, mostly. Thing is, there's 5,000 products, and I'm a bit stuck on how to match the two up side by side. I can make it so I end up with a simple list of the image names, and a simple list of the product names. SO, let's say I had the following columns A is a list of products B is the image name for that product, if it exists (ie: matches) C is a list of images So hopefully, there's some function ...

Combination chart
I'm sure that this is a straight forward problem to solve but this has me pulling my hair out!!! I have a combination chart plotting multiple series against time on the x-axis (using Excel 2003). Most of the series are x-y scatter but I also have one column and one area chart series. The x-y scatter series x values are at hourly intervals over a 2yr period. The column and area categories correspond to daily intervals over exactly the same 2yr period. The problem: the column and area series only plot part-way along the x-axis. The most recent data points plot about 1/5th of th...

Sort columns containing merged cells.
How do I sort an Excel worksheet containing merged cells? Unmerge the cells first *Soapbox alert* Merging cells is the cause of much more pain than gain and most power users & MVPs who post here will urge, encourage, and advise you to merge cells only when absolutely needed. "Ron" wrote: > How do I sort an Excel worksheet containing merged cells? ...

counting rows
I suspect this is relatively easy but I'm not sure how to accomplish this. Is there a way to count the "non blank rows" in a specific column. We need to know the number of rows that have been filled in within each column....thanks You can use the Counta function. =COUNTA(A1:A1000) This counts any cells within the range that have data in them. Hope this helps Judith >-----Original Message----- >I suspect this is relatively easy but I'm not sure how to accomplish this. Is there a way to count the "non blank rows" in a specific column. We need to kno...

Outlook 2003
I've built a new box and want to migrate ALL userdata from my old (machine) Outlook 2003 installation to the new one. How do I do it? Thanks Take a look here, it may help: http://www.slipstick.com/config/backup.htm --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Scooter asked: | I've built a new box and want to migrate ALL userdata from my old | (machine) Outlook 2003 installation to the new one. How do I do it? | | T...

csv-file-lines back to excel rows and columns
Hello, My Excel-version is 2002. I am working with long lists of Attendees for courses and get them in csv-format, so all Data are in long rows with commas inbetween. But I need them in rows and columns, name under name, adress under adress and so on. Is there a Way to convert them from csv to rows and columns? Thank you rosalia You may want to post a sample (not the workbook) of how your data is laid out. And if the data is all in a long column (not row), then is each group delimited by anything (blank row)?? Or is it x rows per group? You'll get different suggestions based on the l...

Can I setup mixed bar chart where last column is stacked?
I have been given a report that need "tweaking". A standard 2D bar chart has several columns that are to be merged together to create a single stacked "Others" entry - whilst maintaining the other columns as single entries. Can this be done? Thanks in advance for any help offered. Regards WS There is an example of this on my website www.stfx.ca/people/bliengme/ExcelTips best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Wobbly Steve" <Wobbly Steve@discussions.microsoft.com> wrote in message news:A5E41F29-1711-452...

Column Name
Hi I am using this code to send data into excel Set rst = CurrentDb.OpenRecordset("SELECT Temp.* FROM Temp;") rst.MoveFirst Do While Not rst.EOF objSht.Cells(iRow, 1).Value = rst!main_id objSht.Cells(iRow, 2).Value = rst!year_id iRow = iRow + 1 rst.MoveNext Loop rst.Close End With But how would I modify my code if I dont know the name of the columns? objSht.Cells(iRow, 1).Value = rst!column1 objSht.Cells(iRow, 1).Value = rst!column2 objSht.Cells(iRow, 1).Value = rst!(1) objSht.Cells(iRow, 1).Value = rst!(2) Thank you all Bre-x In your example, i...

How to delete a set of rows depending on Value
I have two worksheets Worksheet A 27 Columns 1600 Rows. Worksheet B 1 Column 200 Rows I need Worksheet A to look at each cell in Worksheet B, if a cell exists in Worksheet A, then I need the row deleted... Basically I have a list of Grand list of items to do, then a list of items already completed. I need to now remove all entries in the grand list that have been completed. Is this feasible or should I look at using some Unix script. It sounds like you could use VLOOKUP to find out if the value in B exists on A: http://www.officearticles.com/excel/vlookup_formulas_in_microsoft_excel....

Updating a field's data to another field in same table???
I hope I make sense. What and how is the best way to update a field with data from another field from the same table? Everything from Field 1 to Field 2. Thank you in advance for any help you can provide. See: Calculated fields at: http://allenbrowne.com/casu-14.html The article describes how - and, more importantly, when - to store a field that depends on another field. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "TotallyConfused" <...

Show only rows with duplicate values in a column?
I've a spreadsheet with thousands of rows of people (last name, first name, address, etc.) I need to weed out duplicates, and sorting by lastname, firstname and then scanning the firstnames for duplicates that have the same last name is onerous!! Is there a way to have Excel (2003) show only rows in which there are more than one of a given value in a column? So let's say there's only one row with a value of "Jones" in the lastname, it'll now show that row but it'll show the three rows with "Smith" in the column? (Would be really cool if it showed rows ...

Filtering and adding rows based on some condition
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01CAF764.5A2D7800 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all, SQL 2008 sp1 I have the following example, Having problems returning the correct = results. =20 --If USID contains Blue,Green,Yellow then need to add Active --Else need to add 3 lines that contain Blue,Green,Yellow DECLARE @TABLE1 TABLE (USGID INT NOT NULL,PCID INT NOT NULL ) INSERT INTO @TABLE1 VALUES (54675,1),(54675,2),(54675,3),(54675,4),(54675,5),(54682,1) DECLAR...

My data points disappear
I'm trying to create a regulart line graph from a set of data. Everytime I try to change the x-axis to timescale it automatically changes my y-axis data range and my data points disappear. So then I go and change the y-axis data range back to what is was but my data points and line graph won't show. Please help. I suspect it's a mismatch of X values, not Y values. What are the X and Y values in your chart series, and what range are you trying to plot on your time scale axis? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical S...

Column Limit
Is there any way to increase the 255 column limit? Hi Bob You have a Special version then<g> I have 256 Columns -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Bob Phillips" <bob.phillips@tiscali.co.uk> wrote in message news:%23X7pJjYbDHA.1272@TK2MSFTNGP12.phx.gbl... > No, its 255 per sheet, although you can have many worksheets. > > Similarly, you only get 65536 rows. > > -- > > HTH > > Bob Phillips > > "TWALL" <twall@peachtreeconsulting.com> wrote in message > news:000b01c36d87$6681a1d0...

i need to format a column as microseconds ? how do i do this
i need to format a column as microseconds ? how do i do this eg : hh:mm:ss.___ Format>Cells>Custom In the Type box, enter hh:mm:ss.000 -- Kind Regards, Niek Otten Microsoft MVP - Excel "SMT" <SMT@discussions.microsoft.com> wrote in message news:CF0BAF0B-FEC9-46CD-9784-3FDBEBA6729F@microsoft.com... >i need to format a column as microseconds ? how do i do this > eg : hh:mm:ss.___ ...

Can data validation also autocomplete?
I want to know if you can have the entry cell autocomplete when it has drop down bx. Is this possible? Thanks, Vat -- Message posted from http://www.ExcelForum.com Hi unfortunately not with the build-in functionality -- Regards Frank Kabel Frankfurt, Germany > I want to know if you can have the entry cell autocomplete when it > has a drop down bx. Is this possible? > > > Thanks, Vato > > > --- > Message posted from http://www.ExcelForum.com/ No.. -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in t...

Right column spilling onto 2nd page
I bought a template from a vendor and saved it as a new template with my version of excel. The link to that file is: http://www.utahhousevalues.com/lgfiles.cfm. The vendor is not able to help me figure out why the right column is spilling onto a second page. There are 49 total pages and about 25 of them spill the right column onto a second page. They claim each page is identical and it should not do that. I'm using Small Business version of Excel 2000 with XP Professional SP-1 THANKS MUCH Darrell Catmull http://www.utahhousevalues.com Hi Darrell, If the vendor can't tell you w...

split one column to multiple columns
I have data formatted in column A like: Sheldon Barnes <nhr4man@nc.rr.com>, Miriam DURVAMC Mil34ler <miriam....miller@va.gov>, Eddie Barnes <e.barne26s@verizon.net>, Deloris Bell-Johnson <db12345ell67@nc.rr.com>, Brenda Bethea <bbethea@email.un453c.edu>, jessie bowen <jbowen8kdkdkd871@aol.com>, How can i separate into three columns? First Name, Last Name, E-mail Many Thanks in Advance Greg Hi Greg, See if Text To Column under Data will do that for you. Use Space as the Delimiter. It worked for me on a simple test of your dat...

Merging multiple spreadsheets by matching columns
As an Access Newbie I have been at this for two days. I have a Quickbooks inventory speadsheet and Multiple vender price spreadsheets. I would like to merge the "cost" column from the Multiple vendor spreadsheets onto the Quickbooks Inventory speadsheet using "part number" column as the matching field. It seems like a simple process in my head, but be damn if I can figure out how to translate those commands in Access '07 Is the a Template available that would help? ...

Organize multiple columns into one column
I was wondering if there is an easy way to take multiple columns of data and move them into one column (other than copy and paste). I used the 'text to columns' tool, which separated the data into various columns using the comma delimiter. Ultimately, I want to summarize all of the data using a Pivot Table report, and using the list approach has been the only way I know to do this (only way to ensure all data is referenced). Any help would greatly be appreciated. Regards, Tajeri Not sure why you would want to put several columns into 1 but :- 1. Copy a formula down an empt...

can i restrict data entry from certain columns
Is there a way to keep data from being entered in all columns afte column B? The data is scanned by wireless barcode scanner and th scanner can only have one suffix (i.e. tab, space, enter) {can' alternate}. This will help in having to go back and cut and paste al the entered data to the correct position on the spreadsheet -- surgeando ----------------------------------------------------------------------- surgeandoj's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2503 View this thread: http://www.excelforum.com/showthread.php?threadid=38561 Select the c...

Adding columns to "Look Up Records" window #4
In certain places in CRM (specifically highlight a Lead, click New Email, and press the looking glass lookup button to the right of the To field) when I do a Lookup for contacts or accounts, the window "Look Up Records" appears with a list of entities at the top, a search field, and two lists below. The left list is available records and the right is selected records. The Available Records list only displays a single column, the name of the account or contact. I want to display more columns, and scollbars are OK. My specific issue is that I have 4 "Rick Johnson" c...

Updating data point names
I am currently creating scatter plot charts, but in the abscence of data labels on the chart I am unable to distinguish which data point relates to what data. I was hoping not to have to actually insert the data point names on the chart, but to have the Data Point name update so that when you hover with the mouse over the point it comes up with a meaningful name for the data point. Any ideas? Jerry - Presumably the point names come from a worksheet range. There are utilities freely available that can apply the labels from a worksheet range to the points in a chart. Two good ones ar...

countif from two columns
I need to count when day column in Monday and value column is 200, example somthing like: countif ((A:A,"Monday") and (B:B,"200")) This formulae does not work as it is, how must i modify it to make it work? James =sumproduct((a1:a500="Monday")*(b1:b500=200)) Unfortunately you cannot use the complete column selection in this function. Tony >-----Original Message----- >I need to count when day column in Monday and value column >is 200, example somthing like: >countif ((A:A,"Monday") and (B:B,"200")) > >This formulae d...

Chart Data
Hi, In 2003 Excel, you can copy your chart to another sheet within a workbook and edit the data. The new graph will reflect the new data of the new sheet. In 2007 Excel, when you copy the chart, it will still reference to the original data_ you'll have a graph that doesn't match your valueso on the same sheet. Please help. In XL2003 you need to edit data as you state. In XL2007 the same applies; right click chart, use the Select Data item on popup menu to set new data range best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email ...