+ Reply to Thread
Results 1 to 8 of 8

Excel - Ranking and Sorting the data in alphabetical order and multiply conditions

  1. #1
    Registered User
    Join Date
    08-14-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    5

    Lightbulb Excel - Ranking and Sorting the data in alphabetical order and multiply conditions

    Hi there,

    I need to rank the data in the way that it numbers/ranks lines of data based on the following criteria:
    * Column A - Store Name (A, B, C, D, etc.)
    * Column B - Unique ID number (1
    * Column C - But only if Criteria says "Yes" in separate column
    * Column D - Ranking ???

    All details shown in attached picture.

    I would really appreciate your help on this.

    Kind regards,

    Koniu
    Excel issue.JPG
    Attached Files Attached Files
    Last edited by KoniuUK; 08-14-2020 at 01:33 PM.

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,965

    Re: Excel - Ranking and Sorting the data in alphabetical order and multiply conditions

    Hi,
    Please attach your excel file. None of the helpers like to build the data themselves from scratch
    See the yellow banner at the top.

  3. #3
    Registered User
    Join Date
    08-14-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    5

    Re: Excel - Ranking and Sorting the data in alphabetical order and multiply conditions

    Thanks for tip Belinda. I was trying to do that previously but it didn't work. Now it should be all attached.
    Again many thanks.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Excel - Ranking and Sorting the data in alphabetical order and multiply conditions

    Sort the data => data => sort

    first sort on criteria => Z => A

    second sort on store => A => Z


    d3 =IF($C3="no","",COUNTIF($C$3:$C3,$C3))
    Last edited by oeldere; 08-14-2020 at 02:13 PM. Reason: A => Z
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,472

    Re: Excel - Ranking and Sorting the data in alphabetical order and multiply conditions

    Please try at D3

    =IF(C3="No","--",SUMPRODUCT(N(CODE($A$3:$A$15)*10^9+$B$3:$B$15<CODE(A3)*10^9+B3)*($C$3:$C$15="Yes"))+1)
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-14-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    5

    Re: Excel - Ranking and Sorting the data in alphabetical order and multiply conditions

    Hi Oeldre,

    Thank you for your reply and appreciate your help, however I need to achieve the same without the requirement of sorting the data first manually. I need formula to do this automatically and allocate ranking on the set of data where this data is all mixed up.

  7. #7
    Registered User
    Join Date
    08-14-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    5

    Re: Excel - Ranking and Sorting the data in alphabetical order and multiply conditions

    Hi Bo_Ry,

    It seems that this solve my issue and give me the answer that I was looking for.
    However, I have got a couple of queries on this:
    1. Could you elaborate/explain on your code?
    2. I tried to replicate/transfer your formula in my spreadsheet but it seems that I get “Value” error. Any idea why this may be? PS. I do use array formula brackets. 

    Many thanks for your help.

    Kind regards,

    KoniuUK

  8. #8
    Registered User
    Join Date
    08-14-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    5

    Re: Excel - Ranking and Sorting the data in alphabetical order and multiply conditions

    Please ignore point 2 as I found the issue with my spreadsheet.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula for Sorting in alphabetical order
    By Neilesh Kumar in forum Excel General
    Replies: 4
    Last Post: 11-05-2017, 08:51 AM
  2. Sorting a table into alphabetical order?
    By elliottt in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 02-21-2014, 12:57 PM
  3. Automatically sorting in to alphabetical order
    By macky18 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-15-2013, 07:45 AM
  4. Sorting the alphabetical order with semicolon in each cell.
    By spiderkzn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-07-2012, 09:55 AM
  5. Ranking in alphabetical order
    By Gearcutter in forum Excel General
    Replies: 8
    Last Post: 11-07-2006, 02:52 PM
  6. [SOLVED] Sorting Colums into alphabetical order
    By tox in forum Excel General
    Replies: 3
    Last Post: 02-22-2005, 12:06 PM
  7. Sorting rows into alphabetical order?
    By tox in forum Excel General
    Replies: 8
    Last Post: 02-04-2005, 07:08 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1