Maximum amount with various data

[MAHMUZ]

Dears

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

[Punnam]

Maximum Amount.xlsx


Hi,


Find the Solved Sheet

[Saarang84]

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

Please Login or Register  to view this content.

[MAHMUZ]

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).

[MAHMUZ]

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

Please Login or Register  to view this content.

Thanks
but this is not which i am looking for.

[sktneer]

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

Please Login or Register  to view this content.

and then drag down.

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

Please Login or Register  to view this content.

and then drag down.

For detail see the attached sheet.

[MAHMUZ]

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

Please Login or Register  to view this content.

and then drag down.

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

Please Login or Register  to view this content.

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.

[Saarang84]

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.

[joeu2004]

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

[sktneer]

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.

This is what Saarang84 suggested in his post#3.

[oeldere]

I used a pivot table, to get the result.

See the attached file.