Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
This is my shame, I am an accountant who is crap at excel..
I cannot provide the actual data as its confidential but lets say I have a spreadsheet that lists 300 businesses
B1, B2, B3, .... B300
For each business there are 5 columns Age, Service time, Salary, Grade, Education level (graded 1-11), Absence days this year. These may have 100's or 1000's of employees. See below
I want to find out of all 300 businesses, the oldest employee, the longest service, the highest grade, the highest education level, The most absence days.
I have been told that I can use index/match to do this?
But I am not sure how. Any tips?
Use the Max function to find the highest value in the range then index match to see fund which person or business has that Max value
Can you rearrange the data so "business" is in a column and your rows are then business, age, service etc (you can almost certainly do this using excels internal data modelling but I'm not at my desk so can't have a play to walk you through it)
One that's done you can then use simple pivot tables to give you max/min of each column for each business
The formatting in your example is overcomplicating things.
If the business name is its own column for each employee you can use MAXIFS, e.g.
=MAXIFS([age],[business],"B1")
Returns the highest value in the age column where the value in the business column is B1.
(Or pivot table as above)
If you use a formula, unless you get REALLY fancy you'll have no way to spot if there are two employees who are both 64, or with 18 yrs service etc.
Excel is not a database, so if this is the sort of thing you do a lot of - consider importing that data to a better tool that will let you search/filter/slice.
+1 the data structure is the main stumbling block.
If it has to stay like this (in blocks of 5 columns for each business, running across the sheet) then you could do the following:
- insert 5 rows above the data, so the data headings are now in row 6
- in cell A1 type in =If(A6 ="Age",MAX(A7:A10000),"") press Enter then copy across the sheet for all businesses
- in cell A2 type in =If(A6 ="Service",MAX(A7:A10000),"") press Enter then copy across the sheet
- in cell A3 type in =If(A6 ="Grade",MAX(A7:A10000),"") press Enter then copy across the sheet
- in cell A4 type in =If(A6 ="Edu",MAX(A7:A10000),"") press Enter then copy across the sheet
- in cell A5 type in =If(A6 ="Absence",MAX(A7:A10000),"") press Enter then copy across the sheet
Then to get the max Age across all businesses = MAX(A1:ZZZ1) - or whatever the last column is
Maximum Service is = MAX(A2:ZZZ2)
Maximum Grade is = MAX(A3:ZZZ3) and so on
I don't think he wants to know what the maximum is, he wants to know WHO has the maximum, which means he then needs to apply the output of your formulae into an Match/Index
No no, reggiegasket has it, thats perfect. Works like a dream.
Cheers.
That data structure is truly awful.
It is how it was supplied...
All I want is to to know max age, max, grade, max salary etc across the data.
Thanks reggiegasget, took 2 mins.
I need to learn more.
Cool.
There's no way to know 'who' the person is though, as we don't have column for the name or employee code.
If we did then we could use MATCH and INDEX, as said (or VLOOKUP even though it's inferior).
I don't have employee names, its just needed to know across the 300 business's what is the highest value of each category. You read my original question perfectly, I appreciate the help.
TBH if I wanted to know which business or name if I had it, now I know the max in each 5 categories I can just manually do ctrl F and find the cells with those values and cross ref from column and row number.
TBH if I wanted to know which business or name if I had it, now I know the max in each 5 categories I can just manually do ctrl F and find the cells with those values
Use an index/match or xlookup or vlookup to do it. From one accountant to another, save yourself some time that you can then spend doing useful things, not 'ctrl+F' for hours!*
Also, is your spreadsheet really 1800 columns wide?
* EDIT: Unless you bill by the hour I guess!
Also, is your spreadsheet really 1800 columns wide?
* EDIT: Unless you bill by the hour I guess!
yes it is 1800 columns wide.
No billing this is helping out a charity.
(Note it is not employees or businesses that was just to give the example of the data structure I have been provided with)
I would posit that proposing a better layout might help
(a) now
(b) in the future, potentially a number of times
Excel is not a database,
OP coughed to being an accountant. Like the one I work for they don't do databases just spreadsheets.  🙂
OP coughed to being an accountant. Like the one I work for they don’t do databases just spreadsheets
Hey, I do SQL and visual studio too. Lots of Excel though because that all that most of my colleagues can use!