Maximum amount with various data

Dears

I have a sheet (attached), I am looking for the maximum amount in listed cell (C:C) for each company.

Maximum Amount.xlsx


Hi,


Find the Solved Sheet

Thanks for your contribution and feedback.

This a partially solved, but what if I have more than a thousand co. I don’t need to write down each company name and linked the formula with the requested cell, actually I need to get the result in column (D).

Originally Posted by Saarang84

In col D, use the below as an array formula (confirmed by pressing Ctrl+Alt+Enter)

Formula:

=MAX(IF($A:$A=A2,$C:$C))

Thanks
but this is not which i am looking for.

Originally Posted by sktneer

In D2, try this formula to get the list unique companies.

=IFERROR(INDEX($A$2:$A$10,MATCH(0,INDEX(COUNTIF($D$1:D1,$A$2:$A$10),0,0),0)),"")

and then drag down.

In E2, try this array formula which requires confirmation with Ctrl+Shift+Enter

=IF(D2="","",MAX(IF(A:A=D2,C:C)))

and then drag down.

For detail see the attached sheet.

Thanks for your contribution and valuable time.
Kindly could I have the result as per the attached file, in fact I need to repeat each maximum value in column (D:D) all over the respective company name.

Originally Posted by MAHMUZ

Kindly could I have the result as per the attached file, in fact I need to repeat each maximum value in column (D:D) all over the respective company name.

Download "Maximum Amount v2.xls" (click here) [1]. Ignore the preview. Just click on Download.

In part, the table is:

	A	B	C	D	E
1	Company Name	Invoice	Amount	Maximum	Intended Maximum
2	C	123456	78.50	78.50	78.50
3	C	443335	54.77	78.50	78.50
4	C	56789	1.10	78.50	78.50
5	B	fd4567	3.00	69.99	69.99
6	B	vcb5657	34.50	69.99	69.99
7	B	bnm9990	69.99	69.99	69.99
8	D	ret561	55.67	55.67	55.67
9	A	qwe234	99.90	324.80	324.80
10	A	vcv878	324.80	324.80	324.80
11

Column E is your original column D, for comparison.

The formula in D2 is:

=IF(A2="","",MAX(IF($A$2:$A$1000<>"",IF($A$2:$A$1000=A2,$C$2:$C$1000))))

Be sure to array-enter the formula: press ctrl+shift+Enter, not just Enter.

Copy D2 down through D1000.

If you might need more rows of data (!), change all instances 1000 to the rows needed, and copy down column D through the last row needed. Do similarly if you need fewer rows.

Note: Do not use A:A and C:C instead of $A$2:$A$1000 and $C$2:$C$1000. Although A:A and C:C are more convenient to write, you pay a hefty price in Excel 2007 and later: the array-entered IF() expression builds an array of 1+ million rows, and Excel must evaluate 1+ million rows. It is unlikely that you will need that many rows of data.


-----
[1] https://app.box.com/s/3ge7ep5mg49gpycfutwa

Originally Posted by MAHMUZ

Thanks for your contribution and valuable time.
Kindly could I have the result as per the attached file, in fact I need to repeat each maximum value in column (D:D) all over the respective company name.

Hi,

The output you need (as given in column D marked in yellow in your attachment) can be generated from my formula. Refer my post #3 for the formula. After you paste the formula in the required column on your worksheet, don't forget to press Ctrl+Shift+Enter together to convert it into an array formula. Once you have done so, copy it down to as many rows as you want.