Rearranging Data In Form Of Panel Data?
Apr 18, 2014
Is there any way to rearrange data in this way for the following:
: BEFORE:
/////////[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
: LATER :
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
ADVERTISEMENT
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
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
View Related
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
Aug 28, 2009
I want to rearranging the data in year wise for each company. My data (Sheet 1) is in this order.
Year Company A Company B
1996 Data 1 Data 1
.......
2006 Data 11 Data 2
I wan to rearrange the data (Sheet 2) in to the following order.
Company A 1996 Data 1
........
2006 Data 11
Company B 1996 Data 1
.........
2006 Data 11
Is there any possibility for vba for this soloution. If i will increase the no of companies, whether the vba will work or not.
I have attached a file for the same
View 10 Replies
View Related
Oct 31, 2012
I have about 20 columns headed things like Product Number, Size, Colour, Weight etc
So for example, one record might be something like:
Product number
Size
Colour
Weight
Col5
Col6
Col7
Col8
etc
[Code]....
I have about 10,000 of these records.
What I need is to rearrange them in another sheet which just has 3 columns.
Product number
Attributes
Values
A record would look something like
Product number
Attributes
Values
[Code]....
Is it possible to transform the first format in the first spread sheet to the second format in the second sheet?
View 14 Replies
View Related
Sep 19, 2006
I have a worksheet that contains data in the first three columns and then the 4th column is empty and then there's data in the next three columns and then an empty column etc. How can I cut the data from columns E to G and I to K and M to O etc....and paste it directly below the data in columns A to C? I dont know how many columns of data there are in the worksheet and every set of 3 columns of data (eg. E to G) varies in size. I recorded the following macro of what I want to do.
Sub rearrange()
Range("E1:G1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("A95").Select
ActiveSheet.Paste
Range("I1:K1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("A203").Select
ActiveSheet.Paste
Range("M1:O1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("A386").Select
ActiveSheet.Paste
End Sub
View 2 Replies
View Related
Jan 6, 2014
I have a csv file that includes several runs of a test per day on 5 different parameters. I need to rearrange this data to show the results from each run on each date, but arranged in a different way. I've attached an .xlsx file that best describes what I have
View 8 Replies
View Related
Nov 28, 2011
I have a data table which looks like this:
1 2 3 4 a b c
5 6 7 8 d e f
And it should be converted to look like this:
1 2 3 4 a
1 2 3 4 b
1 2 3 4 c
5 6 7 8 d
5 6 7 8 e
5 6 7 8 f
So basically, the data in the last columns should be transposed, and the data in the first columns need to be copied in the cells of the new rows.
View 1 Replies
View Related
Apr 25, 2012
unable to find anything besides splitting numbers and letters in a column into multiple columns.
The raw data at the moment looks like this:
ID Number
Ticket Number
Event
Sheet1 *A158662TIC-00013Food4flags512346TIC-00027flags8picnic9555510TIC-000311Food12picnic139707414TIC-000415Food16picnic17784218TIC-000519Everything
I would like to somehow change that into this:
Sheet1 *ABCDE15866TIC-0001Foodflags*21234TIC-0002*flagspicnic35555TIC-0003Food*picnic497074TIC-0004Foodpicnic*57842TIC-0005Foodflagspicnic
View 3 Replies
View Related
Oct 18, 2012
How do I rearrange a list of data in one column that are in sequence to appear at random i.e data should not follow any particular pattern.
View 4 Replies
View Related
Sep 20, 2007
I am trying to create a form to use as a golf tracker. I basically have created a scorecard where I input the date, score, fairways in regulation, greens in regulation and putts. I want to be able to put that information just like if it was a scorecard and then have a button that says submit. Then that information is output into individual sheets (i.e. one for scores, one for fairways, one for greens and one for putts).
View 13 Replies
View Related
Aug 21, 2014
Is there a simple way via VBA to alter the layout of data from a mixed up two column list into multiple headed lists on another worksheet (within the same workbook)?
The attachment should better demonstrate what I mean. Sheet1 has example data of how it is and Sheet2 shows how I would like it.
The data will be dynamic in the sense the numbers of unique values in column A will change (only increase, never decrease), as will the number of unique values in column B.
View 2 Replies
View Related
Sep 13, 2009
I have a set of related variables that are split over multiple worksheets, and I need to be able to take specific information, duplicate certain values and produce an output sheet for use in a separate piece of software.
The variables are:
Position Number (Sheet 1)
Position Title (Sheets 1 and 2)
Position Requirement (Sheet 2)
Requirement Importance (Sheet 2)
The output sheet requires a list of all the requirements for each position number, which means the position number itself needs duplicating (in new rows) X number of times, where X is the number of requirements assigned. The appropriate requirements are then to be pasted in next to each position number (and the requirements can be found by comparing position number to title, and from title to requirements).
Normally, I’d be able to do this using lookups and so forth, but my problem arises when I have multiple position numbers with the same related title (in the attached example, there are three plumbers with unique position numbers). I can’t figure out how to say to Excel “a plumber has five requirements, and there are three plumbers, so duplicate each position number for each plumber five times, then insert the appropriate qualifications (and their associated importance values) next to the position numbers”.
View 4 Replies
View Related
Sep 11, 2009
Is there a function to convert the data shown in table 1 to table 2 without rearranging the columns and rows? because i don't want to use TRANSPOSE. I want a function, somthing like SUMIF with OFFSET or INDEX and MATCH or any other function.
Table 1
Team 1Team 2Team 3Team 4Team 4Team 5Team 5ABABCity 12531642City 231173705City 367891125City 436251348
Table 2
City 4City 2City 1City 3Team 4BTeam 2Team 5ATeam 4ATeam 1Team 3Team 5B
View 2 Replies
View Related
Mar 31, 2009
I'm missing something in my UserForm initialization code. If I fill the form out once and click 'OK' (run the code to put the form data into a sheet), when I go back into the form all the old info is still there. If I then click 'Cancel' (Unload Me) and reopen the form, the old data is cleared out. What am I missing to make it clear it out the first time?
View 2 Replies
View Related
Dec 2, 2008
I'm having trouble trying to come up with a way to insert data fields into a spreadsheet form. I have a travel authorization form that I would like to have automatically fill in the required fields based on typing in a name. i.e. I would type in an employees name, and it would automatically fill-in the correct address, etc for that employee. I have attached a spreadsheet that contains one sheet as the form, and another sheet containing the employee data. I know nothing about VBA, but I have a feeling that is where I need to go.
View 3 Replies
View Related
Jun 26, 2013
I am needing to create a form that exports data (a quote) to an Excel Db (table) and is then able to recall the data back into the form. (the default form in excel does this and I want to copy that.)
Once the data is called back in, I can then export it to another Table to show that the quote has been approved and will be used.
I am having trouble with the VBA coding that copies the inputted quote in Cell C2 (the reference for the quote number) of the "Form" sheet and looks it up in the "Database" sheet. I have tried several variations of code, but nothing works so far.
-SS
Sub RecallQuote()
'
' RecallQuote Macro
'
Sheets("Form").Select
Range("C2").Select 'this is the cell that holds the quote number to look up from the table
[Code] ......
View 2 Replies
View Related
Jun 3, 2006
find the attached workbook
I have a Database and user form, in the user form i have a field named “Vehicle No” this is a combo box from which a user needs to select the Vehicle numbers, and all these are working fine now, I need your help in the following:
When user selects the second field named "Select Vendor name" i need a pop up window which shows all the Vehicles belongs to the vendor which they have selected, and with the popup window user selects the vehicle number then the Vehicle number combo box should be filled.
Currently users have to select by scrolling through Combo box which takes long time and difficult to find by scrolling.
View 7 Replies
View Related
Jan 14, 2009
I have created a registration workbook for this year's youth sports league. All of the information is entered into a User Form and separated onto it's appropriate sheet designated by the child's age. Next year, I would like to use this year's workbook to look up returning players.
Will it be possible to add a "lookup" button into my form, or create a lookup program, that once the registrar clicks on the correct player, the information is plugged into the User Form, the registrar adjusts the age and any necessary info, presses enter, and the information is copied into the appropriate category in the new workbook? I haven't worked with User Forms long enough to know if they can be filled in that way, but if this can be done, you are the people who would know.
View 3 Replies
View Related
May 5, 2009
Now that the calculations are working, with the press of a button, I need to be able to select a range of dates and copy all lines within the range to a seperate sheet with the desired name under the same headings they currently reside under. I have included some modified code that is being used in another spreadsheet that was created for me, but I do not pretent to understand all of it and I no longer work with the creator of the spreadsheet. How do I use a button to open the form for date selections and entering the name of the new sheet, and then use the start button on the form to begin the matching and copying to a new sheet? If there is an easier way I am all for that too.
View 14 Replies
View Related
Sep 15, 2006
I have limited ability with Excel, and I'm trying to create a database using the Data/Form menu choices. My problem is I only want specific data in two of the 20 or so fields. When I use Data/ Validation and direct data entry, I get exactly what I want, EXCEPT that the choices don't come up when I use the Data/Form. Users can enter anything they want, which defeats the purpose. I tried to build a user form to use for data entry, but I don't know how to get it to add items to a database or lookup items like the data/form process does. I also want to add a few macro buttons to the form to do other things.
I am pretty sure of a few things: 1) This should be pretty simple. Anyone knowing VBA would probably know how to do this in minutes. I found some code that did a simliar function, but couldn't interpret it to fit my needs, which brings me to 2) I'll never use VBA or complicated macros again. I don't have the need for them. This is just an isolated incident. Therefore, signing up for a class would be useless to me. I tried to locate some advanced Excel/VBA classes, hoping to find an instructor or even a student who would welcome a simple challenge, but there are none available.
View 5 Replies
View Related
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
Aug 30, 2012
How can I make my data in "input" tab to look like in the "output" tab the easiest way?
View 1 Replies
View Related
Nov 5, 2008
I have many company-specific Excel files containing given data fields such as company name, company address, commodities supplied, etc. An example is attached here under the tab "Company Profile". I would like to extract all the information from the different "Company Profile" Excel files and put it into a more usable format, as shown in the attached tab "Results," where the company data runs horizontally across.
I tried to record a macro to do this ("CompanyProfile" -- included in the attached file), but I am not able to make it do what I want it to do. I also do not know how I may be able to capture the information in the "check boxes."
View 2 Replies
View Related
Jul 12, 2009
I have a main form (Form1). From this main form another is loaded (form2) and then if necessary another form is loaded (form3). On form3 I have the following
View 2 Replies
View Related
Aug 3, 2009
I have a database with a few hundred records. I can display the Data Form using Excel but receive the following error when I try display the DataForm with VBA? "ShowDataForm method of Worksheet class failed"
View 3 Replies
View Related
Sep 25, 2009
I've been using a macro which uses a vlookup and a concatenate to create some text without issue for a while. I'm just trying to add some functionality to it and give the option of adding some text after the generated string. So I have this formula from the form:
Private Sub CommandButton1_Click()
Dim lookFor As Range
Dim rng As Range
Dim col As Integer
Dim Found As Variant
Dim add As long
Set add = Me.add.Value
View 9 Replies
View Related
Nov 4, 2009
How can I create a form from data that I have in an excel sheet? Would I need to import it into access or can I do it from excel?
View 9 Replies
View Related
Dec 6, 2009
Have one data table with 200+ codes
The data table looks like this
code # bit mfg bit type
2001 HTC E5001FG
Main worksheet
In the main table (see image) I want to look up the code# biased on the information in columns e & f and place the code# in column d
View 9 Replies
View Related
