I have a spreadsheet in which data is imported daily. It records
transactions of each client. The general set-up is as follows:

(Customer) (Amount)
A $50.00
B $187.00
B $69.00
C $58.00
C $43.00
C $82.00
D $199.00
D $25.00

My problem is the information isn't static. When I download it, each
customer is never on a specific row or in a specific column. Sometimes
Customer A will purchase only 1 item or even no items, in which they wouldn't
appear on the report. Customer B might go on a shopping spree and purchase
50 items once a year. I've already got a sumif formula in place to tell me
the total purchased by each customer, but I want to show the largest ticket
item for each customer. Is this even possible since my report size and range
change daily?

=MAX((A2:A9="C")*(B2:B9))

Enter with Ctrl+Shift+Enter

"Peanut" wrote:

I have a spreadsheet in which data is imported daily. It records
transactions of each client. The general set-up is as follows:

(Customer) (Amount)
A $50.00
B $187.00
B $69.00
C $58.00
C $43.00
C $82.00
D $199.00
D $25.00

My problem is the information isn't static. When I download it, each
customer is never on a specific row or in a specific column. Sometimes
Customer A will purchase only 1 item or even no items, in which they wouldn't
appear on the report. Customer B might go on a shopping spree and purchase
50 items once a year. I've already got a sumif formula in place to tell me
the total purchased by each customer, but I want to show the largest ticket
item for each customer. Is this even possible since my report size and range
change daily?

Thanks, that did work!
What is the significance of Ctrl+Shift+Enter?



"Toppers" wrote:

=MAX((A2:A9="C")*(B2:B9))

Enter with Ctrl+Shift+Enter

"Peanut" wrote:

I have a spreadsheet in which data is imported daily. It records
transactions of each client. The general set-up is as follows:

(Customer) (Amount)
A $50.00
B $187.00
B $69.00
C $58.00
C $43.00
C $82.00
D $199.00
D $25.00

My problem is the information isn't static. When I download it, each
customer is never on a specific row or in a specific column. Sometimes
Customer A will purchase only 1 item or even no items, in which they wouldn't
appear on the report. Customer B might go on a shopping spree and purchase
50 items once a year. I've already got a sumif formula in place to tell me
the total purchased by each customer, but I want to show the largest ticket
item for each customer. Is this even possible since my report size and range
change daily?

The formula is an "array formula" and this is entered using Ctrl+Shift+Enter

=MAX((A2:A9="C")*(B2:B9))

so it calculates A2*B2, A3*B3 etc

if A2 (etc) ="C" the value is 1, otherwise 0 so results for "An" not equal
to "C" will be 0 and the results for A="C" will be the corresponding values
in B (58,43 and 82).

so MAX=82

HTH




"Peanut" wrote:

Thanks, that did work!
What is the significance of Ctrl+Shift+Enter?



"Toppers" wrote:

=MAX((A2:A9="C")*(B2:B9))

Enter with Ctrl+Shift+Enter

"Peanut" wrote:

I have a spreadsheet in which data is imported daily. It records
transactions of each client. The general set-up is as follows:

(Customer) (Amount)
A $50.00
B $187.00
B $69.00
C $58.00
C $43.00
C $82.00
D $199.00
D $25.00

My problem is the information isn't static. When I download it, each
customer is never on a specific row or in a specific column. Sometimes
Customer A will purchase only 1 item or even no items, in which they wouldn't
appear on the report. Customer B might go on a shopping spree and purchase
50 items once a year. I've already got a sumif formula in place to tell me
the total purchased by each customer, but I want to show the largest ticket
item for each customer. Is this even possible since my report size and range
change daily?

You are wonderful. Thanks again,
Peanut



"Toppers" wrote:

The formula is an "array formula" and this is entered using Ctrl+Shift+Enter

=MAX((A2:A9="C")*(B2:B9))

so it calculates A2*B2, A3*B3 etc

if A2 (etc) ="C" the value is 1, otherwise 0 so results for "An" not equal
to "C" will be 0 and the results for A="C" will be the corresponding values
in B (58,43 and 82).

so MAX=82

HTH




"Peanut" wrote:

Thanks, that did work!
What is the significance of Ctrl+Shift+Enter?



"Toppers" wrote:

=MAX((A2:A9="C")*(B2:B9))

Enter with Ctrl+Shift+Enter

"Peanut" wrote:

I have a spreadsheet in which data is imported daily. It records
transactions of each client. The general set-up is as follows:

(Customer) (Amount)
A $50.00
B $187.00
B $69.00
C $58.00
C $43.00
C $82.00
D $199.00
D $25.00

My problem is the information isn't static. When I download it, each
customer is never on a specific row or in a specific column. Sometimes
Customer A will purchase only 1 item or even no items, in which they wouldn't
appear on the report. Customer B might go on a shopping spree and purchase
50 items once a year. I've already got a sumif formula in place to tell me
the total purchased by each customer, but I want to show the largest ticket
item for each customer. Is this even possible since my report size and range
change daily?