is it possible, filter data come in a2, b2 cell

Colin Legg · #2 07-05-2012, 04:08 PM

There isn't a simple formula to do this. I've attached your example with two array formulas.

Code:

A2
 
=IF(SUBTOTAL(3,A4:A1753)=1,
    OFFSET(A4,MAX((SUBTOTAL(3,OFFSET(A4,ROW(A4:A1753)-ROW(A4),,1)))*ROW(A4:A1753))-ROW(A4),0)
    ,"")
 
B2
 
=IF(SUBTOTAL(3,B4:B1753)=1,
    OFFSET(B4,MAX((SUBTOTAL(3,OFFSET(B4,ROW(B4:B1753)-ROW(B4),,1)))*ROW(B4:B1753))-ROW(B4),0)
    ,"")

Array formulas need to be entered with CTRL+SHIFT+ENTER, not just ENTER.

By the way, please can you update your profile to reflect the correct version of Office which you are using. It has a major influence on the appropriate answer(s) to your question(s).

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Split Data from One Cell Into Two	Karen615	Excel	3	09-20-2011 06:36 AM
Move data from 1 cell to another cell	Catalin.B	Excel	1	06-25-2011 12:51 PM
How to combine different cells data in one cell?	Learner7	Excel	1	07-28-2010 12:07 PM
Displaying cell data in another spreadsheet	Colonel Biggs	Excel	0	07-01-2010 07:37 PM
Copying data from one cell to another automatically	mrphilk	Excel	4	06-10-2010 11:52 PM