Find Best Customer

Hi,

I need to find the customer whom we sold more. below is the data that need to be used. I do not want to use any excel Function. there may be some blank cell in USD column against any customer.

I just want to solve it by VB. Can anyone help me to write the code pls...

Customer USD
CAWALMART 3,512.02
CAWALMART
CAWALMART 223.42
CAWALMART 1,837.44
MONOPRIX 110.66
CAWALMART
PUMA 741.24
KIABI 1,641.17
MONOPRIX 793.44
MONOPRIX 1,553.47
MONOPRIX 701.57
TESCO
TESCO 1,828.39

Thanks
Tuhin

mshtuhin,

Detach/open workbook mshtuhin - EF 738765 - SDG14.xls and run macro FindMaxCustomer.

Originally Posted by stanleydgromjr

mshtuhin,

Detach/open workbook mshtuhin - EF 738765 - SDG14.xls and run macro FindMaxCustomer.

Why to use macro when this can be done with simple formula
Just put this formula in E2

=MAX(M32:M36)

And this one in D2

=INDEX(L32:L36,MATCH(E2,M32:M36,0))

And you'll get the same result which is much easier to understand...

mshtuhin,

The following is done without using an array, and should be easier to understand, and change to work on another data set.

It uses AdvancedFilter of column A, Unique, to column D.

It then inserts the following formula into cell E2:
=SUMIF(A:A,D2,B:B)

It then uses autofil in column E, to the last row of data in column D.

It then formats E2:E6, and changes the formulae to values.

It then sorts D2:E6, with sort key E2, decending, which puts the MAX customer and value into cell D2:E2. It then clears D3:E6.


If you were to step thru the VBA code, you could see how the macro works.

Detach/open mshtuhin - EF 738765 - V2 - SDG14.xls and run the updated FindMaxCustomer macro.

mshtuhin,

Thanks, and you are welcome.

Come back anytime.