Selective extraction of data from a table

[Guest]

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 ?

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Debra Dalgleish]

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.

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Peo Sjoblom]

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$500)))*($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 D2500 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

 

[Guest]

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.