Convert Rows data

Hi there,
I have the following sample data:
PARTNUMBER            QTY	RATe   PRUN         VALID_FROM_VALID_TO
ZWR1		0.1095	USD	1          20050512  20050526
XREZ12            	0.1095	USD	1          20050527  99991231

is it possible to put this data in the following:

20050512  20050526                            20050527  99991231
ZWR1                                                 XREZ12
0.1095                                                0.1095
USD                                                    USD
1                                                        1

So basically make the existing Row heading as Column heading, Please advise 
if this can be done. Been trying to do with pivot but can't seem to get it to 
work.

Really appreciate any help.
Thank YOU,
J
0
Juan (55)
7/20/2005 10:01:06 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
913 Views

Similar Articles

[PageSpeed] 35

One interp, and play (assume you want it dynamic) ..

Assuming sample table is in Sheet1,
in A1:E3, data from row2 down,
where E1:E3 houses:

VALID_FROM_VALID_TO
20050512 20050526
20050527 99991231

In a new Sheet2
-----------
Put in A2:

=OFFSET(Sheet1!$E$1,COLUMNS($A$1:A1),)

Copy A2 across as many cols as there are data rows in col E in Sheet1.

For the sample data which is 2 rows, copy A2 across to B2.

Note that the max transposable data rows from Sheet1 will be 256, i.e. the
max # of columns available per sheet

Put in A3:
=OFFSET(Sheet1!$A$1,COLUMNS($A$1:A1),ROWS($A$1:A1)-1)

Copy A3 across as many cols as was done for A2,
fill down by by another 3 rows

For the sample data, copy A3 across to B3, fill down to B6
You should get the desired result in A3:B6

--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
"Juan" <Juan@discussions.microsoft.com> wrote in message
news:EE13788C-F34E-4C86-A7E9-A08BA232CC6D@microsoft.com...
> Hi there,
> I have the following sample data:
> PARTNUMBER            QTY RATe   PRUN         VALID_FROM_VALID_TO
> ZWR1 0.1095 USD 1          20050512  20050526
> XREZ12            0.1095 USD 1          20050527  99991231
>
> is it possible to put this data in the following:
>
> 20050512  20050526                            20050527  99991231
> ZWR1                                                 XREZ12
> 0.1095                                                0.1095
> USD                                                    USD
> 1                                                        1
>
> So basically make the existing Row heading as Column heading, Please
advise
> if this can be done. Been trying to do with pivot but can't seem to get it
to
> work.
>
> Really appreciate any help.
> Thank YOU,
> J


0
demechanik (4694)
7/21/2005 6:33:30 AM
Hello Max,
sorry for the late reply. Just wanted to say thanks for your help. This 
should do the job.

Really appreciated.

juan

"Max" wrote:

> One interp, and play (assume you want it dynamic) ..
> 
> Assuming sample table is in Sheet1,
> in A1:E3, data from row2 down,
> where E1:E3 houses:
> 
> VALID_FROM_VALID_TO
> 20050512 20050526
> 20050527 99991231
> 
> In a new Sheet2
> -----------
> Put in A2:
> 
> =OFFSET(Sheet1!$E$1,COLUMNS($A$1:A1),)
> 
> Copy A2 across as many cols as there are data rows in col E in Sheet1.
> 
> For the sample data which is 2 rows, copy A2 across to B2.
> 
> Note that the max transposable data rows from Sheet1 will be 256, i.e. the
> max # of columns available per sheet
> 
> Put in A3:
> =OFFSET(Sheet1!$A$1,COLUMNS($A$1:A1),ROWS($A$1:A1)-1)
> 
> Copy A3 across as many cols as was done for A2,
> fill down by by another 3 rows
> 
> For the sample data, copy A3 across to B3, fill down to B6
> You should get the desired result in A3:B6
> 
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8,  1° 22' N  103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Juan" <Juan@discussions.microsoft.com> wrote in message
> news:EE13788C-F34E-4C86-A7E9-A08BA232CC6D@microsoft.com...
> > Hi there,
> > I have the following sample data:
> > PARTNUMBER            QTY RATe   PRUN         VALID_FROM_VALID_TO
> > ZWR1 0.1095 USD 1          20050512  20050526
> > XREZ12            0.1095 USD 1          20050527  99991231
> >
> > is it possible to put this data in the following:
> >
> > 20050512  20050526                            20050527  99991231
> > ZWR1                                                 XREZ12
> > 0.1095                                                0.1095
> > USD                                                    USD
> > 1                                                        1
> >
> > So basically make the existing Row heading as Column heading, Please
> advise
> > if this can be done. Been trying to do with pivot but can't seem to get it
> to
> > work.
> >
> > Really appreciate any help.
> > Thank YOU,
> > J
> 
> 
> 
0
Juan (55)
7/22/2005 8:23:02 PM
Pleased to hear that !
Thanks for the feedback
--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
"Juan" <Juan@discussions.microsoft.com> wrote in message
news:F09B6CAC-4EB3-4911-9976-A6515CE58282@microsoft.com...
> Hello Max,
> sorry for the late reply. Just wanted to say thanks for your help. This
> should do the job.
>
> Really appreciated.
>
> juan


0
demechanik (4694)
7/22/2005 11:51:34 PM
Reply:

Similar Artilces:

array data type
Hi, I'm trying to take a column of integers and input them into an arra which I can then use for a loop instead of using this format: If (Worksheets("NeeleyNedPRE").Cells(pRow, pCol).Value = 0) Then For some reason this doesn't work because the column selected al resets to zero rather than running the loop correctly and I think tha an arrray might be the only option. What the easiest way to solve thi problem? -Andre -- Message posted from http://www.ExcelForum.com anjem < wrote: > Hi, I'm trying to take a column of integers and input them into an array > whi...

Moving data from a string
I have a rather complex problem, at least it is for me. I have a column full of addresses. 1234 common way, city, state zip I want to take the column and split it into at least 2. Such that the street address and the CSZ are in seperate columns. A B 1234 common Way, city, state zip I have zero experience with basic and almost none with excel so can someone please help me? Thanks On Thu, 2 Oct 2008 19:47:58 -0700 (PDT), Lacertadeus@gmail.com wrote: >I have a rather complex problem, at least it is for me. > >I have a column full of addr...

eConnect : Inserting Project data into Great Plains
Hi, I am trying to push Project data into Great Plains using eConnect. Project data contains cost categories associated with it. There is approx 450 cost categories that needs to be pushed into Great Plains along with Project data. When i try to do it it gives me an error message as "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction". If the volume of cost categories is less (approx 200) then it works fine but when the volume increases to approx 450 it gives the above error. Does anyone has any idea what could be the re...

What are the performance enhancements to Crm 4 in the data tier
Microsoft released the Microsoft Dynamics CRM 4.0 Performance and Scalability White Papers: http://www.microsoft.com/downloads/details.aspx?FamilyId=5852B14A-394C-4898-8374-CAF5E6479EB0&displaylang=en Does anyone know of similar papers that indicate similar studies for Crm 3? Ultimately I collecting information to justify upgrading from 3 to 4 and our current bottleneck is the sql server. Hi Rob, CRM 4.0 offers a number of advantages to CRM 3.0 when it comes to performance and scalability. For example, support for 64-bit servers, server role separation, cluster database server supp...

Disruption of order of data series in 100% stacked charts
I have built a 100% stacked chart based on 4 yearly series. My problem is the series are not displayed in the same order in every stack. In one of the stacks, one data point jumps up from the bottom to the center, like so: a a a b d b c b c d c d 2001 2002 2003 (d jumps up in year 2002) I would like the series to be aligned and so to appear in the same order in every stack. Tweaking the series order setting only serves to displace the problem. Thanks for any suggestion. Could you also post some typical data? - Jon ------- Jon Peltier, Microsoft ...

How do I limit the visible rows in Excel?
I want to hide everything in a worksheet except for the table into which I am asking others for input. Columns are easy, but how can I hide ALL the rows below my chart? I could colour them out in black, that would lead to printing issues. Help please! Tom, One way I like to restrict access is by defining a scroll area thus: Sub DefineScrollArea() ActiveSheet.ScrollArea = "MyCellRange" End Sub Once you run this macro, you can only move the cursor to a cell within this range. You can also use cell addressses instead of the "MyCellrange" range name Sub DefineSc...

How to define a "from this row to the bottom of the spreadsheet" range?
Hello. In a spreadsheet, I have a formula which refers to the values of a column ('C', for instance). The first line of that column is a title, so I currently use a range that begins with 'C2'. There is no total at the bottom of the column. All cells that follow the last value are blank. The problem is that the number of values on that column grows periodically. As of now, I am using an arbitrary high value to make up the range in the formula, e.g. 'C2:C1000'. The question is: how can I specify that range without using a magic number? In other words, how does o...

multiple columns and row table and finding the answer
Looking for the correct Function I have a table that looks sort of like this A B C D E F G H I J A 14 14 11 10 9 8 7 6 5 4 B 2 14 14 11 10 9 8 7 6 5 C 3 2 14 14 11 10 9 8 7 6 D E Etc Etc Etc I want to right a formula that will return the correct value in the table. So if I enter A A then 14 if it is B A then 14 but if A B then 2. Any idea's where I can start? hI, I have assumed the top left cell of your table is A1 and extends...

Remove . (period) from merged data
I have a Word 2003 doc that merges data from our database, including a ref no which contains a period as part of its format (e.g. 10.001). We now need to remove the period from the ref no once merged in the Word doc, as another company who receive the document (shipping company) are having problems with it. They need it to be shown as 10001. We cannot/do not want to re-format the ref no in the database itself, as it serves a useful purpose for us. Is there a way to "code out" the period once the ref has arrived in the Word doc, perhaps by using a numeric picture switch o...

Converting email body and attachement to a PDF before sending
Howdy; I have an application that automatically creates email with the body of the email containing text/data formatted with HTML. Before sending the email, I would like to convert these to a PDF and then send the emails to the recipient. I am hoping there is some sort of VBA script that will allow me to streamline with as few steps as possible. Geoff. Office includes no components to convert documents to PDF files. I'm = sure there are tools out there that are programmable -- you could even = program the PDF makers that work as printer drivers if you create the = original document i...

Cannot edit Excel data in a PowerPoint
I'm not sure which discussion group this applies to, so I'm posting it in both Excel and PowerPoint. I just started having problems with any Excel workbooks embedded into a PowerPoint presentation. It is a bit difficult to describe but I'll give it my best shot. Whenever I double-click an Excel object to edit the data, it opens up like normal and gives me all of the MS Excel options I normally get. But what is very VERY strange is that when I try to select a single cell (or row/column), it will take me into the MS Excel program and not allow me to edit anything ...

Unmatching row numbers
Okay, I'm trying to use this formula to tell me if there is a matching number in another worksheet IF(Sheet1!$E$1:$E$65536 = "Value from Sheet2 (D3)","Found","") But Excell will only tell me it is "found" when the row numbers match, how can I overcome this setback. Thanks Again, ~E~ ------------------------------------------------- Dear Creperum You dont mention if the row numbers need to match (eg sheet1 d1=sheet2 e1=Sheet3 e1 or whether you only want sheet1 a1 to look at both ranges to see if the value is present. depending on which way you w...

How to put this data sorted by character in each cell
Hi! I don=B4t know if this can be done or not. I have this data in column A: Anne Ashton Angel Blister Bolton Bright Cotton Cash I need that data to be put on the column that starts by the first character of each cell and have to be sorted alphabetically. Like this Column A Column B Column C Angel Blister Cash Anne Bolton Cotton Ashton Bright I don=B4t know if this is possibly with formulas and functions Thank you so much!! I've assumed that your data is in A1 to A8 of Sheet1, and that you want the sorted output to appear in the correct c...

BUG -- SUM after importing data
Hi, I've been trying to find some info on this bug but so far nothing has worked. I have copied a table from the web and am now trying to do so simple SUM functions. But it's not working. I have tried to import the data after exporting them (in case HTML is an issue), but nothing. I have tried some copy and paste special but didn't work. So please help. I have Excel 2000 on XP. Email me at oliviermoratin@yahoo.com Thanks again Hi try the following: - copy an empty cell - select your imported data - goto 'edit - Paste Special' and choose 'Add' "Olivier"...

such next free rows
Hello i need help to finalyse the vba code. the vba code is working but i need to find the first empty line and paste the data from the (excel base.xls). can someone help me Thanks Dim Wk As Workbook Set Wk = Workbooks.Open(Filename:="C:\Databasere_validierung.xls") Windows("excel base.xls").Activate Range("B1:B75").Select Selection.Copy Windows("Databasere_validierung.xls").Activate Range("A4").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, Ski...

new data-entry form
Hello, I have a form that is set to data-entry "True" so that the user can only enter new records, and not view or change previous entered records. I have about 10 fields, on the form and would like the first two fields that when selected on the first entry, become the default for all subsequent entries until the form is closed. Is this possible? Would it be better to have a form pop up before starting to enter data that would have the user make the selections for those two fields first, and once selected, would become the default selection for those two same fields on the ...

Lost all address data except names
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3343203857_596815 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit I am running OS X 10.4.11 Entourage version 11.4.0. I am losing all contact information in my address book EXCEPT first and last names; email address, phone numbers, mailing addresses, etc are gone. This is the second time I have lost this information. It all began shortly after syncing my new iPhone. I have a backup copy howev...

Copy PO w/Project Accounting Data
My client just tried to copy a purchase order and received the following message "You are not allowed to select a purchase order that contains project accounting records." Is there a way to allow copying of a PO with Project Accounting data? Thanks, Jocelyn ...

Selecting rows from various sheets #2
Thanks for the reply Frank, but unfortunately it's a bit over my head. Something simpler would be better. I've managed to create a column that has unique values of all the identifiers. So now all I need to do, is compare the unique identifier with the other sheets and extract the data from the other columns on each sheet. Cheers Shav -- shav ------------------------------------------------------------------------ shav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11980 View this thread: http://www.excelforum.com/showthread.php?threadid=266657 Hi w...

Can I convert a publisher file to a Adobe PDF? #2
Can I convert a publisher file to a Adobe PDF? I hear an echo.................. -- JoAnn Paules MVP Microsoft [Publisher] "Mel" <Mel@discussions.microsoft.com> wrote in message news:A31BB3DC-8E27-41FB-9CAA-632F58580608@microsoft.com... > Can I convert a publisher file to a Adobe PDF? --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.788 / Virus Database: 533 - Release Date: 11/1/2004 It's a good idea to wait at least a day after posting. News Groups are "world wide" and the individua...

Extracting Delimited Data from an Online Source
I have survey data that is being put into a .db file and is delimited with a "|" symbol. I normally just copy the data from the website, copy it into a text file, then open it with excel and indicate the delimiter. Is there a way to have excel read this data from the website automatically and display the data? I also do a little manipulation of the data as far as moving one cell to a different position and inserting a cell somewhere else. I can do that later but getting it in automatically would save me a little time. Thanks Zach -- OlYeller21 -------------------------------...

Flexibly specifying data series in charts
I have tables with many columns. I want to create a chart that will allow me to specify which columns to use for x- and y-axes, preferably with two pull-down menus. Basically, I want to avoid the present situation where I create a new chart every time I want to observe the correlation between two variables. Another refinement: Sometimes one column will indicate a category for data points (e.g., column title could be element name; categories: oxygen, hydrogen, aluminum, calcium, etc.). Can I make the charts to plot one or more categories as separate data series? (Again, preferably wi...

sql server data to ms word using xml
hi i was given a task to display sql server data in ms word using xml. Im currently doing research to accomplish the task. What i have in mind is to create an xml template (or schema?) to load the sql server data first.Then add the xml template in the ms word add-in option. Is it possible? Can anyone point me to the right direction? Thanks My approach to this for the last few years (since Office XP) has been to save a Word document formatted they way it should end up - as XML. And then rename it to a XSL and put in iterators to populate the data from the XML queried out of SQL Serve...

c# and "Select ID,Data FROM [Data$]"
I'm going thru some C# code that iconnects to an excel object. I've never seen a slq construct like this. Would someone please decipher the "[Data$]" part? Thank you. "Select ID,Data FROM [Data$]" (used in a constructor like: new OleDbDataAdapter(query, connection)) > I'm going thru some C# code that iconnects to an excel object. I've > never seen a slq construct like this. Would someone please decipher > the "[Data$]" part? Thank you. [Data$] is the name of the sheet within the Excel workbook. See http://support.mic...

FRx row format account labels
Hello: When an FRx row format is created by using "Edit...Add Rows from Chart of Accounts" and so forth, how does FRx determine the name of the account to place within the row? What I'm getting at is that a row format that is based on the natural account segment can represent several accounts--for example, an account that represents several departments--can be "divided" into different departments based on the segment of the account. So, for an account that has as many as ten departments, how does FRx determine the name for the account. The account description ...