converting rows and columns


I have a dataset that is not organized the way I want it 
to. The data is organized as follows:

Country  Variable  Year1  Year2  Year3 ...
A           1      10     20     30
A           2      5      6      7
A           3      45     40     35
B           1      4      9      2
B           2      ...
B           3      ...
C           1
C           2

I want to copy this data into another table that is 
organized as follows:

Country  Year   Variable1  Variable2  Variable3
A        year1  ...        ...        ...
A        year2  ...
A        year3  .
B        year1  .
B        year2  .
B        year3  .
C        year1  .
etc etc

I can't do it with VLOOKUP because I would have to look in 
two columns (country and variable name) and then transfer 
the data from the appropriate year.  I would appreciate 
any help!


anonymous (74722)
12/9/2003 10:53:29 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 40

select the data under variable through year?   ctrl-c to 
copy. Go to a blank area on the sheet and paste-special 
(under edit), in the dialog box select the 
checkbox 'transpose'. Erase the old data and move the new 
data into position.

Good luck
>-----Original Message-----
>I have a dataset that is not organized the way I want it 
>to. The data is organized as follows:
>Country  Variable  Year1  Year2  Year3 ...
>A           1      10     20     30
>A           2      5      6      7
>A           3      45     40     35
>B           1      4      9      2
>B           2      ...
>B           3      ...
>C           1
>C           2
>I want to copy this data into another table that is 
>organized as follows:
>Country  Year   Variable1  Variable2  Variable3
>A        year1  ...        ...        ...
>A        year2  ...
>A        year3  .
>B        year1  .
>B        year2  .
>B        year3  .
>C        year1  .
>etc etc
>I can't do it with VLOOKUP because I would have to look 
>two columns (country and variable name) and then transfer 
>the data from the appropriate year.  I would appreciate 
>any help!
anonymous (74722)
12/9/2003 11:19:45 PM

Similar Artilces:

Is there any Excel trick to highlight a bunch of rows and then make it as if I had used the control button to highlight them all?
The Visible cells only control works great for making it possible to ditch the "control" button while highlighting rows. But what if you have within these rows 3 or 4 times when the rows are contiguous? This is happening to me. Each row in my subtotaled spreadsheet needs to be separately highlighted as if I had used the control button on all the rows. I then use a border button on these rows. This works great when the rows are discontinuous ...I'm only highlighting "total" rows on my subtotaled spreadsheet, but one example where there is no disconnect between a tot...

normalizing a column of numbers
How do you normalize a column of numbers using a chart? I know how to do a chart but not how to interpret it for normalizing Mason, Use a helper column where you normalize your numbers using an appropriate formula prior to charting, and then chart that column with your normalizing formulas. HTH, Bernie MS Excel MVP "mason" <> wrote in message > How do you normalize a column of numbers using a chart? I know how to do a chart but not how to interpret it for normalizing. > &...

Can't keep focus on second row of subform
Hi all. Hope you can help me with this question. I have a form with two subforms on. The main subform is in datasheet view (named KTable), while the second subform is in continiuos form view (named KNotes). In the parent form, I have a text box named ID. The text box has the following source: =[KTable].controls("ID") The KNotes subform is linked to this ID text box. This construction will display the related rows of KNotes when I select a new row of KTable. So far everything is OK. The problem is when I want to change some of the values in KNotes. If KTable.form.orderby is ...

Need to select rows to average based on a value in a different column
Below is an example of my spreadsheet. I'd like to find the average number of users for each "Day of the week" hour combination but I'm not even sure where to start. Date Hour DoW # Users 10/27/2003 10 Mon 11 10/27/2003 11 Mon 11 10/27/2003 13 Mon 10 10/27/2003 14 Mon 11 10/27/2003 15 Mon 9 10/27/2003 16 Mon 9 10/28/2003 8 Tue 5 10/28/2003 9 Tue 6 10/28/2003 10 Tue 4 10/28/2003 11 Tue 8 10/28/2003 13 Tue 7 10/28/2003 14 Tue 7 10/28/2003 15 Tue 7 10/28/2003 16 Tue 10 10/29/2003 8 Wed 7 10/29/2003 9 Wed 10 10/29/2003 10 Wed 11 10/...

How I can switch row/column in pivot chart?
Hi, I have the new office 2007, in excel I try to draw pivot chart, I succeded. But I want to switch row into column and column into row. When I try to do that tis button bocomes invisible while I can do in PPT. Please help me!! Regards, Tuba Kesen Umar Pivot charts in Excel 2000 through 2007 only allow you to plot series in columns. However, you could rebuild the pivot table so that your old column fields are located in the rows area and vice versa. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. _______ "kesen...

Adding Text To A Column
Hello, Last one for today; I promise. I have a long column of numbers, such as: 456 567 321 784 etc. The worksheet is formatted as .csv Is there any easy way to convert this column to: 456 MHz 567 MHz 321 MHz etc. And, would they (still) be interpreted as numbers, or would it now go a text format (automatically) ? Thanks for the help, Bob Hi Bob If you want it to stay numbers, do a custom formating ###" MHz" or if you want text, you can do it this way assuming you data starts in A1 in the other cells put =A1&"MHz" and copy down. HTH John "Bob" <rg...

concatenate more column value #2
Hi All Any function can be simply below function , No VBA? Next time, I will try add 26 column value. =IF(TRIM(H2)&TRIM(I2)&TRIM(J2)&TRIM(K2)&TRIM(L2)&TRIM(M2)&TRIM(N2)&TRIM (P2)&TRIM(Q2)&TRIM(R2)&TRIM(S2)&TRIM(T2)<> "", "Y","N") moonhkt Looks like you want to return "Y" if there is a value in any of your cells range and return "N" if only blank values in all cells... if it is what you need try this =IF(COUNTBLANK(H2:T2)<>13,"Y","N") '13 number of cells. ...

XLS in Unbound Object Frame: Only 20 columns?
Hi, I'm having a problem with linking an Excel spreadsheet in a report using the Unbound Object Frame. The spreadsheet is displaying just fine, except for one thing: It only shows 20 columns! It cuts off after column "T", while the real document ends at "AF". At first I thought it was because the "size mode" property of the frame was set to "cut off", but after changing that to "zoom" and also "stretch", I found out that's not it. I tried forcing the frame to pick up the lost rows by setting the "Source Item&quo...

Problem with NetShareGetInfo
All I have the scenario where I need information about a given share. This is my code: // requires lmshare.h, lm.h & netapi32.lib SHARE_INFO_502 * gsi502 = NULL; NET_API_STATUS res = 0; char szPath[_MAX_PATH] = {'\0'}, szFile[MAX_PATH] = {'\0'}; CString strShareType(""); LPWSTR lpszW = new WCHAR[255]; _splitpath( strDirectory, NULL, szPath, szFile, NULL ); LPTSTR lpStr = szFile; int nLen = MultiByteToWideChar(CP_ACP, 0,lpStr, -1, NULL, NULL); MultiByteToWideChar(CP_ACP, 0, lpStr, -1, lpszW, nLen); res = ::NetShareGetInfo( NULL /* LOCALHOST */, lp...

Filtering between two columns
I have to columns that I want to try and filter out at the same time. Not really sure if this is possible. example Column 1 Column 2 770/005 K01001 770/005 K01002 770/005 K01003 770/006 W01001 770/006 K01004 770/007 Z01001 770/007 W01002 What I want to do is pull all of the column 1's that have only K values, then pull column 1's that have mixed k,w values and finally column 1's with mixed w,z values. I have tried to think off an IIF statement but couldn't quite get my head around it. I ca...

Selecting dynamic number of columns into fixed-structure table
I have a table with the following columns (the Pen# columns are all REAL data types and all columns support NULL values): dtDateTime Pen1 Pen2 Pen3 Pen4 Pen5 Pen6 Pen7 Pen8 Pen9 Pen10 I have a stored procedure that returns data similar to the following: dtDateTime TT_S02_20 TT_S02_30 TT_S02_50 ----------------------- ------------- ------------- ------------- 2010-06-26 00:00:06.653 148.1323 115.5447 124.679 2010-06-26 00:00:21.687 148.5214 115.5...

How do I Time stamp individual rows in excel?
I have to use excel to update inventory. I don't know what formula to use. What I need to have is every time a record is added, a seperate column will put a time stamp there. I tried useing the NOW() command, but all the records changed when I added data in a row. Please help. "lost and confused" wrote: > I have to use excel to update inventory. I don't know what formula to use. > What I need to have is every time a record is added, a seperate column will > put a time stamp there. I tried useing the NOW() command, but all the > records changed whe...

Unable to Sort Multiple Columns
Good day all! I've seen this posted in a million other places, but I wasn't able to get my question fully answered. I'm creating a simple sheet to list common TCP/IP service names and their appropriate port numbers. The first column is a description of the service name (which is 4 cells wide) and then I have two sets of 2 merged cells which contain the UDP & TCP port ranges respectively. I want to sort the entire list by the TCP port numbers, but can't because Excel says that the "merged cells must be identically sized." Everyone's solution to this proble...

How do I make each row add up seperatly? Please make it easy...
I have never set up anything on excel before. My job has always had it ready for me. I want to do this to help me figure out how much I will need for my baking ingredients for the holidays. I thought that I could put a row for sugar, and then for everything I am making with sugar type in 3/4 cup and 1 1/2 cups then at the end of all the sugar items have it add how much I will need. Can anyone tell me step by step how to set this up?? Please make it easy I am not computer smart at all. I'm willing to take this project on! Why don't you email what you have to ng@officear...

pasting row to another workbook prompts because same defined name exists in new book
I have a set of weekly spreadsheets, all the same except for the dat that is entered onto them. What we ned to do is cut and paste lines from last weeks workbook int this weeks one. The problem is I have set up data validation on one o the columns based on a list of names, and so now it prompts ever singl time we try to paste into the new workbook, because the same define name 'tradelist' is in the new sheet. is there any way I can stop this prompt somehow because tis driving u mad and taking up time clicking yes or not (when it makes no differenc if we click yes OR no because its e...

converting lotus macro
I am an old lotus guru that is lost in VBA. Can someone convert the following simple lotus macro to VBA? It compares two values and moves one set or the other if not matched to align two series of data. "\b" compares debits, "\c" then compares the credits. 5 minutes work in lotus, years of frustration in excel! Hope there are still some old 123pro's out there who'll understand this. \a {goto}e17~ \b /xi@cellpointer("contents"}=""~{r}/xg/c~ /c~hold~ {right 4}~ /xi@cellpointer("contents")=hold~{down}{left 4}~/xg\b...

Merging/Eliminating Redundant Columns
Hello, I am creating a query from several different tables containing housing data. My final goal is to create one query that contains a single column with the "housing ID number" along with all of the other housing information in each row (address, owner, management...) Due to poor data management, this "housing ID number" is missing from several of my tables. By running a series of queries from all these different tables, a have a datasheet that has three columns with ID numbers. Some rows have all the data, some do not. As an example of my columns: ID1 ID2...

Create summary tab of last entry/row on all tabs in workbook
I am tracking projects in a workbook. I have a tab/worksheet for each project. For a weekly status meeting I create a spreadsheet that has the last entry/row from each tab. Is there a way to program so I do not have to copy/paste from each tab each week? I do add and delete tabs/projects from time to time. PS- not very adept at the technical details so need to respond to me in simple terms : ) You might want to submit your question to an Excel NG since I don't see anything in your question regarding MS Access. IMHO, I would move the workbooks to an Access database rather than...

Re: delete row based on conditions #4
Hello Bob, My requirement as below Assembly Pos Prepart a 100 b a 101 l a 102 k a 191 c a 192 f a 193 y a 200 d a 291 g a 300 m a 370 n a 371 w a 400 i i would like to have some indication against position numbers 100,101,102,193,200,370,371 so that i can delete those rows For a particular position number (n00), if any of the following pos extensions exists n0...

Emailing from Excel based on date selection by row
Hi I need help with this code which should check all filled rows in the sheet and compare the date in D(i) with todays date in "H1" and if = send an email. The email part works OK and the code was taken from a forum . But the parameters MailSubj1 and Mailsubj2 values are not passed to the SendNotesMail subroutine. Can anyone help with this? Here is the code: ------------------------- ooo ------------------------------ Sub checkdate() Dim Ws As Worksheet Dim oRow As Long Dim Mailsubj1 As String Dim Mailsubj2 As String Set Ws = ThisWorkbook.Worksheets(&...

sorting multiple columns
I have multiple addresses on a work sheet. I need to have all the same zip code # together. How do I sort them by zip code so right name and address ends up with right zip code Highlight the range you want to sort before starting it. Regards, Fred "arbe" <> wrote in message >I have multiple addresses on a work sheet. I need to have all the same zip > code # together. How do I sort them by zip code so right name and address > ends up with right zip code Pre-se...

Column to Charts
I have a Column that contains data in Spreadsheet A That data effects the value of a series of cells in Spreadsheet B I need to create a unique chart for each Column cell in Spreadsheet A based upon the results of the data in Spreadsheet B and then have that data inserted via a Mail merge function into a Word document or a report via Crystal Report. Thank you in advance. ...

converting numbers to text
I entered several numbers with leading zeros only to notice after the fact that the leading zero's were being dropped as expected. I then highlighted the numbers went to format and selected text. The numbers all shifted to the left but the leading zeros didn't appear. If I format a cell as text and them enter the number the leading zeros appear. Is there no way to convert the ones that I already entered? I thought for sure that I done this before without a problem. gls858 Hi To convert number to text, use a formula such as =TEXT(A13,"0000000")in a helper column - say B13,...

Performing a multiple column lookup
As a teacher, I would like to design a formula that will lookup data from three separate column and return a grade as a result of the lookup. If student "A" is a "female" and is in the "10th grade" and scored a "75" on a test, what letter grade would that student recieve based on the results of each of these columns?? Hi! Isn't the only criteria that matters the score? Is there a difference between: student "A", female, 10th grade, score 75 student "G", male, 12th grade, score 75 Biff "JDay" <JDay@discussion...

In transfering my money 99 to money 2003 I goofed up in that the file on money 2003 wants to open with notepad. I can open by using "open with" money but then it restores and converts and that annoying. Is there some way I can make this open without conveting etc just by clicking on the short cut icon? -- al The screen that comes up when you right-click, then select "Open With" has a check mark at the bottom to make the program you select the default. -- Michael Gordon MVP "al" <> wrote in message news:7E6D072F-67C3-412...