index / match ?
 

I have a column that contains sale # and another column that has cust names.

How could I get the highest 10 sale #s for a specific customer?

Is this a place for index / match?

TIA

Ben

index / match ?
 

Sounds like a perfect fit for autofilter

--

Regards,

Peo Sjoblom

"Benjamin" wrote in message
...
I have a column that contains sale # and another column that has cust
names.

How could I get the highest 10 sale #s for a specific customer?

Is this a place for index / match?

TIA

Ben

index / match ?
 

I would like to avoid re-arranging the data. I just need to have the highest
sale # to come up in a specific cell based on what customer I want.

"Peo Sjoblom" wrote:

Sounds like a perfect fit for autofilter

--

Regards,

Peo Sjoblom

"Benjamin" wrote in message
...
I have a column that contains sale # and another column that has cust
names.

How could I get the highest 10 sale #s for a specific customer?

Is this a place for index / match?

TIA

Ben

index / match ?
 

autofilter does not rearrange like sort. If simply filters (hides) out the
undesired cells. Then unfilter to be back exactly like you were. TRY it.

--
Don Guillett
SalesAid Software

"Benjamin" wrote in message
...
I would like to avoid re-arranging the data. I just need to have the
highest
sale # to come up in a specific cell based on what customer I want.

"Peo Sjoblom" wrote:

Sounds like a perfect fit for autofilter

--

Regards,

Peo Sjoblom

"Benjamin" wrote in message
...
I have a column that contains sale # and another column that has cust
names.

How could I get the highest 10 sale #s for a specific customer?

Is this a place for index / match?

TIA

Ben

index / match ?
 

Really looking for something else... I would like to ave the users simply
input a customer name, and a table to fill below with the last 10 sales info.
If I can get the last 10 sale #, I can fill the table.

"Don Guillett" wrote:

autofilter does not rearrange like sort. If simply filters (hides) out the
undesired cells. Then unfilter to be back exactly like you were. TRY it.

--
Don Guillett
SalesAid Software

"Benjamin" wrote in message
...
I would like to avoid re-arranging the data. I just need to have the
highest
sale # to come up in a specific cell based on what customer I want.

"Peo Sjoblom" wrote:

Sounds like a perfect fit for autofilter

--

Regards,

Peo Sjoblom

"Benjamin" wrote in message
...
I have a column that contains sale # and another column that has cust
names.

How could I get the highest 10 sale #s for a specific customer?

Is this a place for index / match?

TIA

Ben

index / match ?
 

Enter the following formula as an array formula (Shift-Ctl-Enter) in a
cell named Result

=IF(ROW()-ROW(Result)+1COUNTIF(Customer,CustID),"",LARGE((C ustomer=CustID)*Sales,ROW()-ROW(Result)+1))

and copy down for a total of 10 or however many results you want.

Customer is the name of the Customer range in your data, Sales is the
name of the Sales info range and CustID is the cell containing the
entered name.

This should produce a list of the 10 highest values of the Sales info
for the customer, with blanks if the customer has fewer than 10 sales.

HTH

index / match ?
 

Thanks for the start.. this is waht I was looking for, but I get circular ref
with this formula. Any help?

TIA

"DOR" wrote:

Enter the following formula as an array formula (Shift-Ctl-Enter) in a
cell named Result

=IF(ROW()-ROW(Result)+1COUNTIF(Customer,CustID),"",LARGE((C ustomer=CustID)*Sales,ROW()-ROW(Result)+1))

and copy down for a total of 10 or however many results you want.

Customer is the name of the Customer range in your data, Sales is the
name of the Sales info range and CustID is the cell containing the
entered name.

This should produce a list of the 10 highest values of the Sales info
for the customer, with blanks if the customer has fewer than 10 sales.

HTH

index / match ?
 

I don't know how you got the circ ref - it worked fine on my test
spreadsheet. Maybe you placed the result formula within the range of
one or more of the other ranges (Customer and Sales) it refers to.
There may be some overlap between your result range and the source
ranges.

Could you post the formula you used, the definitions of the named
ranges, and the location of the result..

index / match ?
 

I have tried on sample sheet as well, and get the same error. I must
misunderstand.

{=IF(ROW()-ROW(RESULT)+1COUNTIF(SALE_CUST,E1),"",LARGE((SALE _CUST=E1)*INDEX_ROW,ROW()-ROW(RESULT)+1))}

Where...
RESULT=D2
SALE_CUST=C1:20
INDEX_ROW=A1:20
Cell to enter customer name = E1

TIA

"DOR" wrote:

I don't know how you got the circ ref - it worked fine on my test
spreadsheet. Maybe you placed the result formula within the range of
one or more of the other ranges (Customer and Sales) it refers to.
There may be some overlap between your result range and the source
ranges.

Could you post the formula you used, the definitions of the named
ranges, and the location of the result..

index / match ?
 

I reproduced your formula and definitions, with one exception, in a
blank sheet, and it worked OK. The one exception was that you have E1
in your formula whereas you should have $E$1 to make it absolute; I
used $E$1. However, this alone does not explain the situation unless
there are formulas below E1 that depend on the results in the same rows
in D. The latter would might cause a circular ref as long as you have
E1 rather than $E$1.

What cells are involved in the circ ref?

index / match ?
 

It would also be useful to know if there are any formulas in Sale_Cust
and Index_Row and what they depend on.

I am assuming that Sales_Cust is actually an absolute reference such as
$C$1:$C$20 and not as you show it above; ditto for the other
definitions.