Creating Panel Data By Converting Several Columns Into One

Jun 20, 2011

My problem: I have downloaded national economic data from IMF with several countries for several years. I need several variables and must extract them individually so I a need a clever solution to my problem.

I currently have data that is sorted like below. The "-" indicates a numerical gdp value for the given country for the given year.;

(FYI I could not get the "-" to align beneath 2001-2004 and year respectively.)

Country 2001 2002 2003 2004
x - - - -
y - - - -
z - - - -

The identifier here is country, and the data is as such structured as cross sectional data, with each yearly account of gdp being a variable. Now, I need this data as panel data, organized as below:

Country year gdp
x 2001 -
x 2002 -
x 2003 -
x 2004 -

I sort of say, need to "flip it". As I have 180 countries and 11 years, and need to do this with 2-3 variables, I need either a formula or a VBA to run.

View 12 Replies


How To Make A Panel Data Graph

Mar 21, 2013

How can I make a panel data graph I have 40+ observations and 500+ individuals. I would like to make an excel graph that can display changes over time for each individual.

View 1 Replies View Related

Return Latest Reported Figure By Firm In Panel Data

Apr 16, 2013

I have data for various firms - such as revenues, employees, etc. - for multiple time periods. There is no uniformity, so for some firms I'll have data for 8 time periods, for others for 1, for others for 30, etc. I have hundreds of columns, but the three relevant ones are: in one column is firm ID, in another column in reporting date, and in another column is number of employees.

The attached sample data will make it easier to understand.

I'd like a summary table that tells me the most recent reported number of employees for each firm, and the date of that reported figure. If that firm never reported any employee figures, then the summary table should still have a row for that firm ID, but blanks in the 'reporting date' and 'employees' columns. So, the summary table should be 3 columns - firm ID, reporting date and employees.

The challenging aspect is that the most recent reported employee figure is not always the most recent date on which we got data from the firm (so I can't just do a sort followed by a 'remove duplicates'). It'll need either a multi-step formula solution or a VBA code (that's my guess).

The attached sample data will make it easier to understand. It has the form of data I have and the desired output form.

Excel help needed.xlsx‎

View 3 Replies View Related

Rearranging Data In Form Of Panel Data?

Apr 18, 2014

Is there any way to rearrange data in this way for the following:

/////////[Ticker A] [Ticker B] [Ticker C]
2010 ///// 0,3 ////// 0,6 /////// 0,9
2011 ///// 0,7 ////// 1,4 /////// 2,1
2012 ///// 1,3 ////// 2,6 /////// 3,9


2010 Ticker A 0,3
2011 Ticker A 0,6
2012 Ticker A 0,9
2010 Ticker B 0,7
2011 Ticker B 1,4
2012 Ticker B 2,1
2010 Ticker C 1,3
2011 Ticker C 2,6
2012 Ticker C 3,9

Worth function, worth macro, what worth everything that's automate this process. Are spreadsheets with hundreds of rows.

View 3 Replies View Related

Converting Data Into Rows And Columns

Jan 12, 2010

I am trying to figure out an efficient way to convert data into Excel. The data is not in a row/column format. The format that I received the data in is as follows (2 columns; column labels repeating for each name in column A, and the corresponding data in column B. See below. Any ideas on how to best to convert this data into column labels across the top and data in rows under each column heading?

Column A Column B
Name John Doe
Address 123 Maple Street
City Any City
State Any State
Zip 12345
First Name John
Last Name Doe
Name Jane Doe
Address 123 Maple Street
City Any City
State Any State
Zip 12345
First Name Jane
Last Name Doe

View 9 Replies View Related

Converting Data In Two Columns Into A Row On Separate Sheet

Oct 20, 2011

I have data in two columns on the same sheet that I need to transpose into rows on a separate sheet (same workbook). One problem is that I need to reference off one of these columns (column B - in Sheet "Gp Trg Plan") as the number of lines will vary.

Example - Sheet "Gp Trg Plan"
Column B Column D
Tower Module 1
Tower Module 2
Tower Module 3
Building Module 1
Building Module 8
Street Module 6

Ideally the row will look like.

Example - Sheet "Status WS"

Column A Column B Column C Column D Column E Column F Column G
Gp Name Sub Gp Person 1st Mod Date 2nd Mod Date

There is an undefined amount of training modules (columns D in Sheet "Gp Trg Plan"), but no more than 10.

View 4 Replies View Related

Converting Conditional Data In Rows To Columns

Aug 23, 2009

How can I convert a table from rows to columns as shown below??? Thanks for the feedback...

Current file with:

Unit_no work_order_date

1234 1/1/2005
1234 6/10/2006
1234 10/1/2007
4321 2/3/2004
4321 5/12/2005

Change to:

Unit_no work_order_date work_order_date2

1234 1/1/2005 6/10/2006
1234 6/10/2006 10/1/2007
4321 2/3/2004 5/12/2005

Will be working with data to sum data between the two work order dates...

View 9 Replies View Related

Converting Pipe Delimited Data String Into Separate Columns Using VBA Macro

Mar 6, 2014

I have a string of data coming from a SQL Server data connection into my workbook. The value in the column is a text string that is pipe delimited. I need a macro to parse the data from that column into applicable separate columns. I would typically use text to columns for this and parse it out manually... but the tool I'm creating is one of the automated variety, so that will not suffice for this application. I need it to do this automatically when the data connection refreshes.

Produce|Fruit|apple|banana|cherry|date Produce Fruit apple banana cherry date

View 2 Replies View Related

Creating Bubble Charts On Three Different Columns Of Data

May 23, 2012

I'm trying to create a bubble chart created on three different columns of data. I'd like the following:

-The X-axis should just show the first columns of dates I have stored.
-The Y-axis should be the number of lots of a product that I bought of a certain product.
-I'd like the actual bubbles to not only represent the number of lots purchased on the given date, but I'd like the size to be represented by the third column, which is the average price of each lot.

Is there a way to do this? I've been trying to maneuver it around all morning, and can't seem to get it how I want it. I'm not sure if I'm ordering the columns incorrectly or what.

View 3 Replies View Related

Formula To Handle 5 Fields Of Data To Avoid Creating Extra Columns?

Nov 27, 2009

I am inputting 5 variables in columns A through E:

Family (Y/N)
Adult (1/0)
Child (1/0)
Date of Birth

I am attempting to generate:

Age Males in families sorted into age brackets
Age Females in families sorted into age brackets
Age Males single sorted into age brackets
Age Females single sorted into age brackets

I did this rather simplistically generating 4 columns (F through I) and then using COUNTIF (although I know there is a better way than the lame way I used COUNTIF but that is not the point right now).

However, my question is, can I generate the data described above (located in D13:I22 on my spreadsheet) without going through the messy, cumbersome step of creating extra columns?

View 6 Replies View Related

Creating Dropdown List That Returns Defined Numerous Columns Of Data

Jan 7, 2013




[Code] .......

We use excel for scheduling purposes and each and every day we fill out a floor chart with who is working that day. As it stands now we have to print out a template and hand write all names or type them in and print it out. What I would like to try and do is define each set of names and allow for easy access to auto fill these names. I feel it needs to be a simple process as not all of our managers experienced in excel or formulas. What I would like to try an accomplish is in sheet 1 cell A1 to be able to create a drop list or formula that that would let me return all the names for a given day.

For the purposes of this discussion we can just define each list as the day that is the header ( IE A2:A4 on sheet 2 would be defined as "monday". Also, I already have the names autofilled based off of what the our schedule has. I'm curious if I could create a drop down list with each day of the week as an option, and if they select Tuesday it will then return all the names defined as Tuesday in a column below.And the next day be able to return Wednesday names and so on.

I'm not stuck on it being a drop down list. Maybe if they could just type and =(defined name) in the box above and it would return all the names.

I have tried an =(defined name) and it does return the value but only for the first cell and it has to be in the same row for it to return the name. Which doesn't work because I would like the names that are being defined on a separate sheet.

View 1 Replies View Related

Property Of Tabbed Panel

Nov 26, 2008

I'd like to know if anybody of you knows which is the property that determines the number of tabs in a tabbed panel. I'd like to realize a dynamic tabbed panle where the number of tabbed panels are regulated by a variable.

View 5 Replies View Related

Updatable Calendar Via An 'Administration' Control Panel

Oct 8, 2006

I'm looking for is a simple calendar that will be used, for example, to see if a certain date is available for vacation. The end user just needs to see the calendar. Open dates could be one color, and closed dates could be another. They will access this from a central location via a desktop shortcut as part of an overall toolkit.

I'm not entirely sure how to do this. Most of the calendar programs I've seen are dynamically generated, so there really wouldn't be a way to set certain values in it. It would need to be updated by me via an 'Administration' control panel (password protected, most likely)

View 2 Replies View Related

Creating A Dropdown List Using Matching Data From 2 Columns To Populate The List

Jun 11, 2013

I'm creating a spreadsheet to keep track of my costs of production in an online game. Within the game there are a range of spawned resources that appear for only a short time before being unobtainable these resources have specific types that is shared between multiple spawns of the resource but each resource spawn has a unique name.

My first worksheet lists all the resources and their various qualities and the later worksheets are meant to allow me to choose from a list resources matching the requirements of the item I'm looking to craft. The example i have shown in the second picture requires Tatooinian Fiberplast and Lokian Wild Wheat to craft so in the Chosen Resource column I would like to have a drop down list allowing me to select the named resource type i would like to use - for Tatooinian Fiberplast the only thing on the list should be Omnitwixi and for the Wild Wheat it should show Fizi and Krad



I am aware there are people with more pressing problems than computer games and as such

View 7 Replies View Related

Converting Columns Into Rows

Mar 23, 2013

I have a spreadsheet that has many columns I need them to be stacked into rows to import them into a publishing program but I don't know how to accomplish this automatically. right now all I know how to do is insert rows and drag and drop the info which is very time consuming.

I have attached a sample of the sheet. A2 through A12 is how I would like all of the data to appear. However A14 through G14 is how it looks now.

View 5 Replies View Related

Converting Columns Into Rows?

Jul 14, 2013

excel to convert the data from columns into rows as mentioned below

current excel data is


Expected data

There are close to 10000 records like this is there any formula/function to change this data.

View 11 Replies View Related

Converting Text To Columns For A String

Jul 31, 2012

I have a problem in converting text to columns for a string that has 1000+ entry's. Excel bumps me out after 851 using the Split function.

The data I can only download in a text format has >1000 fields that are seperated by ",". Not a problem so far but when I attempt to convert to columns there is a maximum limitation of 851.

Data1,Data2,Data3,Data4...................Data1000,Data1001,Data1002 etc...

I have tried:

Sub Split_Delimeter()
Dim Words() As String
Words = Split(Cells(2, 4), ",")
MsgBox "String contains " & (UBound(Words) + 1) & " words"
End Sub

View 2 Replies View Related

Converting A String Of Letters In A Row Into Columns

Oct 5, 2009

I have a set of letters in a single row DISATAGFOORCBORDQFINFCOBBKIEHDSMCNRSKARDFCJSLCQCECFVSBCBOYKPVYKC. I want to distibute them across 5 columns such that each letter occupies one cell. I might want to specify a different column width later on so the solution should be flexible.

View 5 Replies View Related

Automatically Copying 9 Rows And Converting To Columns

Dec 17, 2012

I need to take a list of about 1,500 rows of data (single column) and convert it into columns of 9 entries. I know that I can select nine rows and then paste using transpose... But any way of "automating" the process?



becomes 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 etc....

View 5 Replies View Related

Excel 2010 :: Converting Multiple Columns Into Rows?

Aug 24, 2012

I am trying to convert

Excel 2010


View 9 Replies View Related

Converting Multiple Rows / Columns To One Single Long Row

Nov 22, 2012

I am working on putting together a very large spreadsheet covering multiple data sets over multiple states/years. I am trying to convert the data that I have in one spreadsheet (that is arranged like the example below) and make it so that I can paste the data into another spreadsheet as one single row: i.e, 1,651 would follow in the column to the right in the same row as 6.4 and so on. Right now I am having to copy and paste row by row and it is going to take me years.


[Code] .......

View 7 Replies View Related

Converting Formulas To Values While Copying / Pasting Columns Between Worksheets (VBA)

Feb 6, 2014

I have code that "mostly" works great. I'm copying columns from one sheet to another sheet, but in different columns. I have unique code for each copy/paste. The source is mostly raw data. However, there are a few columns that are formula-based, and I'm having a problem. I've attached my code, and it works, but it takes 20 minutes to complete.

I'm very new to macros, but I think(?) I know enough that a data source with 2,000 rows and 30 columns shouldn't take 20 minutes to complete the macro calculations. All of the columns (copy and paste) in the code are pure data. The only exception is the column labeled "AI". Is there are shortcut, other than creating new columns in the source data sheet and pasting these results as values? I put the specific pieces of code that I'm referencing in bold.

View 3 Replies View Related

Converting 2 Columns With Repeating Info Into Another Worksheet In Horizontal Format

Jun 28, 2013

taking a spreadsheet that has vertical repeating info in Column A and results in Column B and converting that to another sheet in a horizontal list. The main problem is that the repeating info in Column A may or may not always be the same for every customer; therefore, when it is placed in a horizontal format some cells may or may not have results. I tried a arbitrary lookup. Here is the formula I used:

{=INDEX(INFO!$A$1:$B$300, SMALL(IF($A$1=INFO!$A$1:$A$300, ROW(INFO!$A$1:$A$300)-MIN(ROW(INFO!$A$1:$A$300))+1, ""), ROW(B1)),COLUMN(B1))}

That got me started. I am willing to use a macro. Here is some sample data:

Table 1 has a sample of the info:


View 8 Replies View Related

Converting Vertical Data To Horizontal For Varying Data - Large Amount

Jan 20, 2009

I have a one column spreadsheet. The column contains this data:

1 Name
2 Address
3 City
4 State
5 Zip
6 Telephone
7 Fax
11 Name
12 Address
13 City
14 State
15 Zip
16 Phone
17 URL
19 Name
20 Address ... and so on

Where there may be one or two blank rows between the individual records and where there may or may not be a Fax number (or row) in the record.

I am trying to convert this data to a horizontal column format - which works fine if I do a copy/paste special/transpose. However I have to do this for 1,800 records and cannot figure out how to do this reliably.

I gave the above illustration to simplify but, actually this is a two column spreadsheet with individual row labels for every record using the above terminology. In other words the above text is in the first column and the data is in the second. Just thought I'd mention in case there was a way to do some kind of if/then formula.

View 9 Replies View Related

Excel 2010 :: Converting Hourly Data Into Daily Data

May 8, 2014

I have a problem converting hourly data into daily data for electricity import capacity. My file has the following form:
| Connection | Date | Period | Available (capacity) | Where 'Connection' identifies the physical location of the capacity (4 possibilities, Germany -> Netherlands; Norway -> Nehterlands; Belgium -> NL; UK -> NL), 'Date' refers to the date, Period refers to hour of the day (e.g. 18:00-19:00) and 'Available' refers to the import capacity of electricity on that conncetion during that hour. I need daily aggregates and tried several things such as DSUM but I cannot manage I've shortened the attached file quite a bit because of the upload size restriction. I am using Excel 2010. I've tried some suggestions in similar topics but none worked for me.

View 3 Replies View Related

Converting Horizontal Data To Vertical Data With Multiple Rows?

Sep 8, 2009

reformat a table in which data for each named person is presented in one row with mutiple columns into a table in which each named person has multiple rows and one column of data. The solution to to this has eluded me so far.

The attached example shows before and after.

View 6 Replies View Related

Creating Bookmarks / Hyperlinks To Rows And Columns

Jul 6, 2014

I have two sheets, Index and masterdata. Index sheet has S.No and Projects, masterdata sheets has S.No, Projects and other information.

I need to create bookmarks/hyperlink for S.No in Index sheet to masterdata's S.No.

In other words if I click on Index sheet S.No, it should take me to corresponding colums in masterdata's S.No.

Example : In the attached sheet If I click on A2 in Index sheet then it should take me to B1 in masterdata sheet and so on till nth row and column.

I tried using =HYPERLINK("#masterdata!B1",A2) but its not working as expected. and the row values are not incrementing.

View 3 Replies View Related

Creating Master Database With Linked Columns?

Jul 1, 2013

I am working on creating a medical master database. I have a master sheet with a huge number of columns. I have a couple of guys working with me, and they have their own sheets in the workbook.

So here is what I would like to do: anytime one of the guys enters data into his respective worksheet, I want the data to be copied over to the master sheet in a new entry.

The columns among the worksheets are different, and I would like the data to automatically sort in the appropriate column on the master sheet when it is copied.

I have attached a copy of the workbook.

I have included an example of what I would like to see. For example, if Charles puts in an entry in his worksheet, a new entry is created on the master sheet with the information from Charles' sheet sorted in the appropriate columns.

View 8 Replies View Related

Creating Two Columns Filled With Numbers And Alphabets

Dec 9, 2013

I would like to create 384 by 2 table such as below. One column has series of alphabets (A-P) using 24 cells for each letter and other column has series of number (1-24) for each corresponding letter on left column.










I tried my best using my limited knowledge of VBA but macro fails by overwriting entire column in each loop (This code is for only one column, but I want to make two columns shown above).

Sub alphabetNumber()
Dim e As Long, f As Long
For f = 1 To 16
For e = 4 To 387 Step 24
Range(Cells(e, 2), Cells(e + 23, 2)) = Chr(64 + f)
Next e
Next f
End Sub

View 9 Replies View Related

Creating Single List With No Blanks From Multiple Columns?

May 2, 2014

I typically use the following formula to create a list with no blanks, when I have a single column of data. Is there a way to do the same thing when you have multiple columns of data (side by side to make it simple) and you want to create a master list that gets rid of the blanks, and keeps them in the order they appear (by column)?

[Code] .....

For example, if I had data (with some blank cells randomly placed) in columns AM and AN, and I wanted a singe master list in one column of all non-blank cells in column AL, followed by column AM, etc.? Is there a formula to do this, or must I resort to a macro?

View 2 Replies View Related

Copyrights 2005-15, All rights reserved