Return latest reported figure by firm in panel data

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.

Have been on this for a while now, so would love some help!

Hi

Why is the output data sorted in the order that it is sorted?

rylo

Hi,

No particular reason, it doesn't need to be sorted in any specific order. I think I just listed it randomly.

Hi

Try this

Please Login or Register  to view this content.

rylo