none
Excel 2016 VBA - function to rank a group of values based on a common key RRS feed

  • Question

  • Dear community,

    I am trying to create a procedure in Excel VBA to rank a group of values based on a common key (Product Id).

    This can be a function or a sub.

    I have a list of different products in a column, and then next to it the price given by the suppliers for each product.

    I want to have the ascending ranking (lowest price gets rank = 1 etc...) in the column next to the price. 

    As I have many product I cannot use the individual rank formula for each group of offer, so I am looking for the VBA code to assign the ranking on the price given for the different product Product ID. 

    I can send you the .xls example file for easier understanding by email, as I cannot attach to this message since my account is not yet verified...

    Thanks in advance to all who will take the time to review this query!

    Nicola


    Tuesday, May 29, 2018 9:54 AM

All replies

  • I can send you the .xls example file for easier understanding by email, as I cannot attach to this message since my account is not yet verified...

    Even if your account is verified, you can not attach files to posts.

    Anyway, you have to read the data into an array, then you can use the RANK function as usual to get the results.

    If you need further help please upload your file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

    A macro to anonymize data in selected cells can be downloaded here:
    https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1

    Andreas.


    Thursday, May 31, 2018 10:30 AM