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 ?

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 ?

Mr Dios,
Many thanks. Sorry for the delay in acknowledging your help. I was busy
learning the finer points of autofilter and advanced filter. I am yet to
solve my problem. If I still find it difficult to solve, I shall come back
to you. Hope you wouldn'g mind. Thanks again

Balan

"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 ?

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 ?

I couldn't solve the problem. Let me restate it:

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
anyone 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 ?

There's a sample file here that may help you:

http://www.contextures.com/excelfiles.html

Under Filters, look for 'FL0019 - Filter Latest Date for Customer'

It uses an advanced filter to extract the latest Sale record for the
selected customer. In the criteria range, the formula for MaxDate checks
each record for the latest date.

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 ?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

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 ?

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 ?

Many Thanks. It was a useful site with lot of sample files. However, I have
not succeeded so far in solving my problem. the macro is working when used
with each name separately. But how to get the result for all names at a time
is my problem. Further, I do not know why, the same formula used in the
macro for max date is not working when I use under Advanced filter. I
entered it in the criteria range as an array formula and tried. Some times I
am only getting the headings in the results area. Without the formula some
results are being extracted , but that was not sufficient to meet my
requirement of getting the latest transaction for each name.



"Debra Dalgleish" wrote:

There's a sample file here that may help you:

http://www.contextures.com/excelfiles.html

Under Filters, look for 'FL0019 - Filter Latest Date for Customer'

It uses an advanced filter to extract the latest Sale record for the
selected customer. In the criteria range, the formula for MaxDate checks
each record for the latest date.

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 ?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

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 ?

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 ?

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 ?