Forum Discussion
Solved
Formula to pull out the most recent data
Dear all,
I would like to ask for formula in excel which I can use if I would like to pull out the most recent data that I have.
I have a spreadsheet containing 3 year of data – 2006, 2009 and 2012.
Each year has couple of variables which are the same for example I have number of staff for each year.
Currently the way it is structure is like this:
Department | 2006 | 2009 | 2012 |
A | 5 | 9 | 12 |
B | 7 | 8 |
|
C | 1 |
| 9 |
D | 3 |
|
|
I would like to develop a table like this:
Department | Number | Year (which year is the most recent data |
A | 12 | 2012 |
B | 8 | 2009 |
C | 9 | 2012 |
D | 3 | 2006 |
Is there a way for me to obtain ‘number’ and ‘year’ coloumn (purple fonts) automatically for the above table? If yes, what would be the formula?
Thank you in advance for your help.
Best,
PQ variant
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Unpivoted Other Columns" = Table.UnpivotOtherColumns( Source, {"Department"}, "Year", "Number"), declareTypes = Table.TransformColumnTypes( #"Unpivoted Other Columns", { {"Year", Int64.Type}, {"Number", Int64.Type} }), #"Sorted Rows" = Table.Sort( declareTypes, { {"Department", Order.Ascending}, {"Year", Order.Descending} }), fixInMemory = Table.AddIndexColumn( #"Sorted Rows", "Index", 0, 1, Int64.Type), removeDuplicates = Table.Distinct(fixInMemory, {"Department"}), removeIndex = Table.SelectColumns( removeDuplicates, {"Department", "Number", "Year"}) in removeIndex
Alternatively, you may want to try Power Query or when your Excel version supports it, use some of the newer functions like LET, BYROW, LAMBDA, VSTACK and HSTACK.
The attached file contains examples for both.
PQ variant
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Unpivoted Other Columns" = Table.UnpivotOtherColumns( Source, {"Department"}, "Year", "Number"), declareTypes = Table.TransformColumnTypes( #"Unpivoted Other Columns", { {"Year", Int64.Type}, {"Number", Int64.Type} }), #"Sorted Rows" = Table.Sort( declareTypes, { {"Department", Order.Ascending}, {"Year", Order.Descending} }), fixInMemory = Table.AddIndexColumn( #"Sorted Rows", "Index", 0, 1, Int64.Type), removeDuplicates = Table.Distinct(fixInMemory, {"Department"}), removeIndex = Table.SelectColumns( removeDuplicates, {"Department", "Number", "Year"}) in removeIndex
The attached workbook uses the LOOKUP function.
Thank you for this. I look at what it means by 1E+307 -> it means that it is a value to get the biggest number - is that correct? If yes, then I don't think I will be able to use them - because there is possibility I have lower number in a recent data.
Thank you.
1E+307 is a number larger than any you will have in your data. Have you tried it?
