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.
I am trying to use a function to search for a particluar value (e.g., ssn) in one column and return the "InDate" in another column. I thought this would be easy, but I'm having considerable trouble. When I do manage to get a value returned, it's the wrong value. I need to return the most recent "InDate" for the ssn that I search for. The button on the " Lookup" sheet is to sort the InDate column descending so that the Excel function will start with the most recent date when it searches. I've tried Lookup and and just about every other similar function with no luck. I originally wanted to search for a ssn and return the information for the last 5-10 most recent visits, but gave up on that long ago.
I need to pull info to additional tabs. The problem I am having is that the data is sorted daily based upon performance numbers, so the REP names in column A can change based upon the data reported in column C.
What I want to accomplish:
Pull daily data for each unique name in column A to a team tab/worksheet that will be used to report that team's daily data.
Below is a representation of the data I need to break down by Rep Name and Team.
Rep DATA DATA DATA DATA DATA DATA DATA 1 2 3 4 5 6 7 REP 2 27.5% 14
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.
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.
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.
I have a table with different values on different dates. To get the newest value on the lastest date I'm using this formula; =INDEX(F5:K5;MATCH(9,99999999999999E+160;F5:K5)).
However, how I can get the second latest value? In this example I want a formula to to return the value previous price;
Latest observation Latest observation date Previous price previous price change date 01.08.2012 02.08.2012 03.08.2012 04.08.2012 05.08.2012 06.08.2012
I am in need of a vlookup formula that will return a value that corresponds to the latest day in a month. Example:
Column A had dates in format 1/3/2012 ect.... Column B is a $ amount Column C is a date in format Oct-12 ect...
I want column D to be a formula that match up column C's month in column A and return the $ in column B that corresponds to the latest day in the month. IN the example above it would returna value for Oct 31 (if there was one), Oct 30 next and then Oct 29 ect...
So if column A had October dates of: 10/1, 10/4, 10/17 and thats it in Oct then it would show the 10/17 $ in column B.
I'm working on a project with tables. I'm not sure if I can use a function or I have to vba. I want to retrieve the lastest 5 entries for a specific column (column AA), but not blank ones. Then I want to retrieve latest 5 entries for column AC. On a seperate sheet I want the entries to be seen in order and I want it to change as new data is entered into the table. In the end I'll have the latest 5 entries for Internal (Column AA) and then after that the latest 5 entreis for External (Column AC).
I have a list of students that have sat a number of exams on different dates. Can I pick out from the list the last exam for each person. I can sort by id number and then by date but how do I then show the record showing the latest date for each person?
Example Mary 1/2/2012 john 2/3/2012 john 1/3/2012 mary 1/1/2012 mary 4/4/ 2012
I want to select the record showing the latest date for each person. the result would be mary 4/4/2012 John 2/3/2012
I am looking for a formula that returns the latest Sale date for a each model of car. Below is sample data which I am trying to use the formula. I tried with below formula, but not successful.
I'm trying to return a distinct list of rows that filter based on the latest date and largest quantity for each distinct AccountID and ProductID combination.
I tried some variant of the =max function, but I need two filters.
The purpose of this is to create a data set of all company accounts with the most recent number of products used to upload to a database.
The simplified and original data set is as follows:
In the attached workbook - the stock Inventory is increased, every second day, by the value shown in cells of column A. Column B displays the date of the update. I'm looking for a Formula (might be an Array Formula) that will return the latest date before the stock turns to be greater than the value in cell D3. I managed to solve it, in cell F3, but with the help column C.
We have list of firms and employees for six years and we need to give size no 1,2 or 3 for each firm based on average number of employees as the following :
size 1 for 1-9 employee size 2 for 10-999 employee size 3 for 1000-above
I m maintaining daily Receipt List which are being reported from deffrient Departments..At the end of month how can get a list of Receipts which are not reported
I've got a spreadsheet with several rows and the numbers within those rows are totalled up at the bottom. Is there a way where i can highlight the cell within the spreadsheet and it takes it off the accumulative total at the bottom? or something similar to this effect? I just don't want to have to remove figures from this spreadsheet and highlighting will be a good way of seeing what has been done
I learnt that " " & can combine the text and figure. However, when it comes to a negative numbers, i dont know how to maneouver it. Eg, Cell A1 is USD(50,000.00) and the comment in Cell B1 is Cost is USD(50,000.00). So the function in B1 = "Cost is "& what to put here?
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.
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)
I have a table as below, and I would like to keep only the latest date for each EMP_NO. For example:
001VICE PRESIDENT A062004-01-01 031SENIOR VICE PRESIDENTA042004-01-01 ... .... ....
======================================= [EMP_NO JOB_TITLE DEPTCHANGE_DATE 014 ASSISTANT VICE PRESIDENT A01 2004-01-01 001 MANAGER A062000-03-01 001 MANAGER A062001-06-01 001 VICE PRESIDENT A062004-01-01 076 VICE PRESIDENT A022003-04-23 013 DIRECTOR OF MARKETING A01 2000-06-19 031 FIRST VICE PRESIDENT A042002-01-01 031 FIRST VICE PRESIDENT A042003-01-01 031 SENIOR VICE PRESIDENT A042004-01-01 =======================================
I have two very large spread sheets that are worked on individually. Because of there size, sharing them causes issues and lock-ups (excel not reasoning) and lengthy download time.
They are now worked on individually with two separate file names.
I am trying to find a way, even a program, to at the end of each period, combine the files and update the data that would have been input.
I have attached examples of what might take place.
Example 1
A B C D E F
[Code]...
Example 2
A B C D E F
[Code]...
As can be seen, Example 1 has had information added to Columns A, B and C, whereas Example two has had info added to Columns D,E and F. Also, the data in Example 1 (E,3 and F,3) has been updated to reflect No with F,3 changing to "Blank"
The result is to combine both spread sheets and update the added / amended data into one spread sheet that shows an updated position as follows.
My data consist of multiple entries of same id(s) at various point of time. I need to extract the latest data of a particular ID. I was using vlookup but it extract the first entry of that ID but not the latest. and the data is entered into the database using userform and VBA macros.
I'm trying to figure a to enforce dual data validation on a single cell. That is, I need to restrict the user to entering only a decimal value, only if a particular other cell (say A2) is blank. To put it another way, if A2 is blank, the user can enter a decimal value, but if A2 is not blank, the user cannot enter anything. I can use Data Validation to enforce either the decimal restriction or the ISBLANK, but I'm not sure how to make them work together.
There must be a VBA command out there, with which one can call the color panel such that the user can then choose his/her preferred color, I just cannot find it. Could you help me out?
With Color-Panel I mean this little interface you get when manually changing the font-color or the fill-color. (it consits out of maybe 8 colors in a row and 5 colors per column and usually has a button "automatic"). I would like to call that interface such that it gives back the color-index of the color choosen by the user.
