|
MACRO
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 |
MACRO
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 |
MACRO
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 |
MACRO
Hi ,
U were right , now i got desired output... Thanks a lot.... u r gr8 buddy "SHWETA GOEL" wrote: 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 |
MACRO
I assume that Material will be referred only in the first row...as
below..Also going forward any questions on Programming or Macro you need to post it under 'Excel Programming' and not under Excel General Questions. material vendor invoice quantity price rank m1 v1 10 5 2 1 v2 80 10 8 1 v3 30 5 6 1 v4 90 10 9 1 m2 v3 65 5 13 1 v5 70 10 7 1 If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: Hi , U were right , now i got desired output... Thanks a lot.... u r gr8 buddy "SHWETA GOEL" wrote: 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 |
MACRO
FYR..
http://www.microsoft.com/library/gal...RulesofConduct -- If this post helps click Yes --------------- Jacob Skaria "SHWETA GOEL" wrote: Hi , U were right , now i got desired output... Thanks a lot.... u r gr8 buddy "SHWETA GOEL" wrote: 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 |
| All times are GMT +1. The time now is 12:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com