Hi,

After running this macro I am getting output like:

material vendor invoice quantity price rank
m1 v1 10 5 2 1
m1 v2 80 10 8 1
m1 v3 30 5 6 1
m1 v4 90 10 9 1
m2 v3 65 5 13 1
m2 v5 70 10 7 1


Regards
Shweta

"Jacob Skaria" wrote:

Dear Shweta

Please try the below macro..If you are new to macros Set the Security level
to low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11.
Insert a module and paste the below code. Save. Get back to
Workbook...Tools|Macro|Run Macro1(). I assume your data starts from Row2..

B vendor
C invoice value
D is quanti
E is avg formula

The below macro will assign the forumla for RANK. Please try and feedback


Sub Macro1()
Dim lngRow, lngStartRange, lngLastUpdated
lngStartRange = 2

For lngRow = 2 To ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1
If Trim(Range("B" & lngRow)) = "" Or (Trim(Range("A" & lngRow)) < "" _
And lngRow < 2) Then
If lngLastUpdated < lngStartRange Then
lngLastUpdated = lngStartRange
Range("F" & lngStartRange & ":F" & lngRow - 1).Formula = "=RANK(E" & _
lngStartRange & ",$E$" & lngStartRange & ":$E$" & lngRow - 1 & ",1)"
End If
End If
If Trim(Range("A" & lngRow)) < "" Then lngStartRange = lngRow
Next

End Sub



If this post helps click Yes
---------------
Jacob Skaria


"SHWETA GOEL" wrote:

Those columns are there but i removed just for convienence...
can u pls tell me how to write macro ...its really very urgent...

Thanks in advance

"Jacob Skaria" wrote:

You will have to use a macro to do this///

A small question...In the below table i dont see the quantity and average
columns////

If this post helps click Yes
---------------
Jacob Skaria


"SHWETA GOEL" wrote:

Thanks Jacob,
But prob is report is dynamic means
for m1 , 4 vendors are there then have to display rating for 4 vendors then
for m2 (material), 5 vendors are there then have to dispaly rating for those
5 vendors ..and number of vendors and materials are not fixed it is depend on
input date....
format is
Material vendor price rating
M1 v1 40 3
v2 36 2
v3 20 1
------------------------------------
M2 v1 45 2
v5 67 3
v6 36 1

and so on

"Jacob Skaria" wrote:

Sorry..

B2 to B5 is vendor
C2 to C5 is invoice value
D2 to d5 is quanti
E2 to e5 is avg,
F2 = RANK(E2,$E$2:$E$5,1)

If this post helps click Yes
---------------
Jacob Skaria


"SHWETA GOEL" wrote:

U R NOT GETTING ME
COLUMNS ARE MATERIAL , VENDOR AND AVG PRICE
RATING I HAV ETO CALCULATE AND DISPLAY



"Jacob Skaria" wrote:

If Column C2 to C5 contains rates..

F2 =RANK(C2,$C$2:$C$5,1)

Drag the formula down upto F5

--
If this post helps click Yes
---------------
Jacob Skaria


"SHWETA GOEL" wrote:

Hi,

I have a requirement , I have posted thread but not getting solution and its
urgent so I am sending mail...

If u can help me then I will be really thankful to you.



I want to develop a report "Vendor evaluation" ..
I have keyfigure invoice amount and invoice quantity... and characteristic
Material and vendor
then have to calculate wtd avg price by formula( invoice amount/invoice
quantity).
Now i got one more column wtd avg price...
Report layout is





material vendor inv value invoice quan wtd avg price price rating
M1 V1 45 15 3
V2 40 15 2.6 40
V3 60 12 5
v4 80 12 6.6


As you see above.. for Material M1, four vendors are there , and v2 is
giving material at lowest price so his rating is 40
v1 is second lowest so his rating will be calculated by formula Weightage
ofFirst Lowest Vendor(i.e V2) - (Avg.Price of Second Vendor(v1) €“ Avg.
price of First Vendor) / Avg. Price of first vendor ) *100


How to implement this scenerio... pls help ...


REGARDS