How to use advanced text filter and get the value of cell that are not in the results?

Hi,
So what i'm trying to do is use advanced text filter in Col B for 'Block' and 'Reinstate'. But the corresponding dates when these actions were performed are given in the row below the row in which block and reinstate appears. I have attached a dummy excel for example.
What i would like to do is get these dates in same row as the block and reinstate using some formula. Or if you guys have a better idea please let me know. I want to get the timeline of when the Block and Reinstate actions were taken.

I want to wrap this up in a macro so that the data is filtered for Block and Reinstate with the dates showing.
PS also if you guys can tell me if i can use more than 2 criteria in advanced text filter it would be awesome.

Thanks.

Could you also please post the code you already created?

I haven't created any code yet.

Ok, so i have figured out how to use more than 2 filter criteria. So only problem left is how do i get the dates that are one row below the filtered rows :D

D2:

=IF(SUM(COUNTIF(B2,{"block","reinstate"})),A3,"")

then copy down.

Excel has a number of different ways to count cells, depending on what type of value is contained in those cells and whether the rows are hidden. No matter what criteria you specify for counting, there is likely a formula that will do it.

COUNT, COUNTA and COUNTIF are the workhorses of cell counting. COUNT looks for cells containing numbers, while COUNTA just looks for cells that aren’t blank. COUNTIF lets you test cells to see if they should be counted. If you need more than one criteria to determine if a cell should be counted, then use either COUNTIFS and SUMPRODUCT

SUBTOTAL and AGGREGATE (Excel 2010 and later) are unique in their ability to discriminate between rows that are hidden (either manually or by filters). AGGREGATE has the optional ability to ignore rows containing error values.

Originally Posted by ankitkraken

Right now its giving "TempBlock" as the result as well.

That is not possible.

It only matches to block or reinstate.

Otherwise, I need to se your workbook.

Yes, its working fine now Thanks a lot
I need one more help

Please Login or Register  to view this content.

I'm using the above code to goto the Reinstate cell if it is found, else give a msgbox that Reinstate is not found and goto the last visible cell in Col B, but i'm unable to do that. It is giving me an error. Can you help me with this. If you have a better way of accomplishing what i want to do that's welcome as well but this is the best i could come up with.

Hi, I got this code working, if you have a better way please let me know.

Please Login or Register  to view this content.