Many Many Thanks ! It works. If you do not consider it foolish, would you
please tell me what the figure "1" in MATCH which is appearing at "Look up
Value" column means ? I would have used A2 or A2:A500 to refer to the dates
to be looked up.

"Peo Sjoblom" wrote:

Create a list of all names, you can do that by using the advanced filter and
selecting just the column with names (include the header), then copy it to
another location and selecting unique records only. Assume you copy it to
H1, that means that the unique names starts in H2, in I2 (or the adjacent
cell to the right of where you put the name list) put this formula and copy
down


=INDEX($D$2:$D$500,MATCH(1,($A$2:$A$500=MAX(IF(($B $2:$B$500=$H2)*($C$2:$C$500="Purchase"),$A$2:$A$50 0)))*($B$2:$B$500=$H2)*($C$2:$C$500="Purchase"),0) )


entered with ctrl + shift & enter


copy down along the unique names as long as needed


where D2:D500 are the unit values, C2:C500 are the transactions,
B2:B500 are the names and A2:A500 the dates

the above formula will extract the unit values for each name in the unique
name list created (in my example in column H)



--
Regards,

Peo Sjoblom





"Balan" wrote in message
...
It is not working. When I choose the max date, it is displaying the
records
for that date only. I want the max date for each name to be displayed.
"CLR" wrote:

Use Autofilter.....choose "Purchase" in that column, and choose the most
recent date from the date column dropdown. It will return the row(s) IAW
those conditions.

Vaya con Dios,
Chuck, CABGx3



"Balan" wrote:

I tried to sort data using autofilter and advanced filter. Both were
not
helpful. My data has the following columns:

Date Name Transaction Qty

The transaction could be "Purchase" or "Sale". Under same names
different
types of transactions could be there on different dates.

I want the "Qty" data for the latest date in respect of "Purchase"
made by
each per son ( "names"). The latest date is different for different
names.
However. When I used advanced filter I am not able to get the required
data.
In the criteria range for advanced filter under the Date column I used
=Max() function to filter the latest date for each name. But I am
getting
the latest date for the entire range put together (i.e., the max date
of all
entries in Date column), instead of the max date for each name under
"Purchase". I tried Index and Match functions also; But I am not able
to
frame the formula to suit my requirement using these two functions.
Can
you pl help ? Thanks in advance


"CLR" wrote:

Take a look at Data Filter Autofilter..........you should be able
to
filter to any specs you have and then copy and paste the resulting
data.........


Vaya con Dios,
Chuck, CABGx3





"Balan" wrote:

Hi ! I have a table of several rows and columns. I want to extract
the
values of select rows fully. The rows I want to select based on 3
different
criteria. For example, the array is a4:m50. Column A has
dates.Column B has
names of the customers. Column C lists whether the transaction is
"Purchase"
or "Sale". Column D has the unit value. I want to extract the
entire row
values for "Sale" transactions by say customer X in column B on one
of the
latest dates i.e, if the customer has sold material on more than
one day, I
want the data relating to latest date. Through a formula I want to
extract
data in respect of more than one customer. Is this possible ? Can
any one of
you help ?