• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to find the last filled cell in row and show column header based on criteria in excel

HI,

I need an excel formula to extract last allocation month based on criteria.

Query:

I have one allocation table with around 500 rows. Each rows is an employee month on month allocation in a program.

My query is that I need a formula in Table-2 which shows Last Allocation month based on Employee no and program name entered.


Sample sheet attached for your reference


Thanks

Nitesh
 

Attachments

  • Sample Sheet.xlsx
    12.9 KB · Views: 16
HI,

I need an excel formula to extract last allocation month based on criteria.

Query:

I have one allocation table with around 500 rows. Each rows is an employee month on month allocation in a program.

My query is that I need a formula in Table-2 which shows Last Allocation month based on Employee no and program name entered.


Sample sheet attached for your reference


Thanks

Nitesh
Hi Nitesh,

Probably this:

=LOOKUP(2,1/(4:4<>""),$3:$3)

Regards
Jaya S
 
=INDEX($D$3:$O$3,0,COUNTA(INDIRECT("D" & MATCH(A14&B14&C14,$A$4:$A$8&$B$4:$B$8&$C$4:$C$8,0)+ROW($A$3) & ":O" & MATCH(A14&B14&C14,$A$4:$A$8&$B$4:$B$8&$C$4:$C$8,0)+ROW($A$3))))

little complicated...but it works if you change the orders.
since this array formula...press ctrl+shift+enter in formula cell and press ctrl+d for drag in below cells
 
There are two obvious contenders for such a calculation. One is the old function LOOKUP and the other, newer function is MAXIFS. Assuming you can select a single record 'allocation' from the allocation table, the following formulas would each give the final date
= LOOKUP( 2, 1/(allocation>0), month )
= LOOKUP( MAX(month), month/(allocation>0) )
= MAXIFS(month, allocation, ">0")
 
It is also true that things are changing. For example, if ones formula were in rows 14-18 then it will be possible to pick a single name and program from the Query name/project list using the implicit intersection operator '@'
= @Name
= @Program

The entire array showing whether a person is allocated to a program is
= allocationTable>0
Combine these allows the corresponding row of the allocation table, 'allocated?' to be filtered out
= FILTER( allocationTable>0, (NameList=@Name)*(ProgramList=@Program) )
After that, determining the final month is almost an anticlimax
= MAX( month * allocated? )

There is very little sign of the ingenuity that has characterised traditional spreadsheet formula development but there is still a huge mental 'gear change' to be accommodated.
 
The slight issue with such LOOKUP constructions is that they are forced to calculate over all cells passed to them. Referencing entire rows (4:4, etc.) thus gives rise to a formula which must process 16384 cells (double that when reciprocated with e.g. unity).

Much more efficient (and also applicable to all versions of Excel) would be a binary construction, e.g.:

=INDEX($3:$3,MATCH(88^88,4:4))

and copied down.

Regards
 
The slight issue with such LOOKUP constructions is that they are forced to calculate over all cells passed to them.
True but, for me, it is not an issue. Because over 80% of my formulas are array formulas (I always wished CSE was the default setting but now, with modern DA, I am happy), I have never used an entire row or column reference. I would always down-select from a bounded range or perform a range intersection to ensure that only cells defined to be part of the model are addressed within the formula.
 
Back
Top