+ Reply to Thread
Results 1 to 4 of 4

Comparing two lists for positional changes

  1. #1
    Registered User
    Join Date
    12-05-2019
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    1

    Comparing two lists for positional changes

    Hi All,

    I'm new here, i'm a lawyer working in London.

    My boss has given me the additional responsibilities of doing our firms data analysis, so i'm starting to use excel properly for the first time in my life.

    I'm pretty tech savvy but i've got no more than just basic experience with excel so i'm wrecking my brains trying to find the answer to the below. Any help would highly be appreciated.

    I have to lists of the same countries that i have ranked using two separate metrics, i would now like to work out how i can find out the positional changes (either moved up or moved down) from each other.

    List 1:

    India
    Pakistan
    Iran
    United Kingdom
    Nigeria
    USA
    Bangladesh
    China
    Philippines
    Canada
    Albania
    Egypt
    Australia
    Jamaica
    Algeria
    Morocco
    South Africa
    Afghanistan
    Iraq
    Somalia
    Italy
    Brazil
    Ghana
    Turkey
    Russia
    Hongkong
    Lebanon
    Zimbabwe
    Sri Lanka
    Syria
    Nepal
    Romania
    Sierra Leone
    Saudi Arabia
    Spain
    Colombia
    Eritrea
    Malaysia
    New Zealand
    Poland
    Yemen
    Israel
    Uganda
    France
    Jordan
    Libya
    Tunisia
    Germany
    Kenya
    Portugal
    Sweden
    Dominica
    Ethiopia
    Mauritius
    Mexico
    South Korea
    Ukraine
    Vietnam
    Cameroon
    Japan
    Peru
    Sudan
    Thailand
    United Arab Emirates
    Greece
    Kosovo
    Singapore
    Venezuela
    Chad
    Mongolia
    Slovakia
    Taiwan
    Trinidad Tobago
    Angola
    Benin
    Congo
    Denmark
    Ecuador
    Gambia, the
    Georgia
    Kazakhstan
    Liberia
    Moldova
    Palestine
    Saint Lucia
    Azerbaijan
    Bulgaria
    Burma
    Kurdistan
    Cuba
    Czech Republic
    Honduras
    Indonesia
    Ivory Coast
    Kuwait
    Norway
    Tanzania
    Uzbekistan
    Zambia
    Argentina
    Austria
    Belgium
    Bolivia
    Cyprus
    Grenada
    Guinea
    Guyana
    Hungary
    Latvia
    Namibia
    Senegal
    Serbia
    Switzerland
    Yugoslavia
    Bahamas
    Bahrain
    Barbados
    Belarus
    Bosnia-Herzegovina
    Bukino Faso
    Cambodia
    Chile
    Croatia
    Dominican Republic
    El Salvador
    Estonia
    Finland
    Holland
    Ireland
    Lithuania
    Malawi
    Malta
    Mauritania
    Montenegro
    Myanmar
    Netherlands
    Panama
    Papua New Guinea
    Paraguay
    Qatar
    Saint Vincent and the Grenadines
    Scotland
    Seychelles, the
    Solomon Islands
    Suriname
    Swaziland
    Solomon Islands
    Suriname
    Swaziland


    List 2:

    USA
    United Kingdom
    Hongkong
    Australia
    Canada
    South Korea
    New Zealand
    Germany
    Israel
    Indonesia
    China
    Saudi Arabia
    Malaysia
    Greece
    Lebanon
    Kazakhstan
    Mexico
    Ukraine
    Japan
    Sweden
    Benin
    Azerbaijan
    Belarus
    Netherlands
    Paraguay
    Singapore
    Burma
    Argentina
    Switzerland
    Venezuela
    Guyana
    Bahamas
    Solomon Islands
    Solomon Islands
    Serbia
    Taiwan
    Ecuador
    Grenada
    Bahrain
    Chile
    Malawi
    Montenegro
    Papua New Guinea
    Thailand
    United Arab Emirates
    Norway
    Hungary
    Yemen
    Kosovo
    Mongolia
    Moldova
    Kuwait
    Cyprus
    Guinea
    Libya
    Barbados
    Estonia
    Finland
    Qatar
    Suriname
    Swaziland
    Suriname
    Swaziland
    South Africa
    Italy
    Vietnam
    Congo
    Denmark
    Ivory Coast
    Namibia
    Holland
    Ireland
    Mauritania
    Panama
    Scotland
    Seychelles, the
    Palestine
    Czech Republic
    Zambia
    Bukino Faso
    Cambodia
    Croatia
    Lithuania
    Myanmar
    Saint Vincent and the Grenadines
    Bolivia
    Latvia
    Bosnia-Herzegovina
    Dominican Republic
    El Salvador
    Malta
    Brazil
    Poland
    Sudan
    Tanzania
    Austria
    Senegal
    Yugoslavia
    Jordan
    Gambia, the
    Liberia
    Saint Lucia
    Cuba
    Honduras
    Belgium
    Peru
    France
    Tunisia
    Bulgaria
    Kurdistan
    Turkey
    Mauritius
    Uzbekistan
    Kenya
    Trinidad Tobago
    Georgia
    Egypt
    Spain
    Angola
    Sierra Leone
    Cameroon
    Slovakia
    Portugal
    Dominica
    Sri Lanka
    Ethiopia
    Nepal
    Zimbabwe
    Russia
    Colombia
    Chad
    Uganda
    Morocco
    Syria
    Romania
    Eritrea
    Philippines
    Afghanistan
    Ghana
    Algeria
    Iraq
    Albania
    Somalia
    Jamaica
    Nigeria
    Bangladesh
    Pakistan
    Iran
    India


    So i'm trying to figure out what the positional change from the countries in list 1 are to list 2; eg; USA has moved up 5 places. How can this be done in Excel?

    thank you in advance for your help

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Comparing two lists for positional changes

    you could use a vlookup to find the position. Let's say that in list one Pakistan is ranked 2 and in list two it is ranked 4, depending on which list you want, you can do a vlookup.
    something like =VLOOKUP(A2,Sheet2!A:B,2,FALSE) where list two is in sheet2 with the names in column A and the ranks are in column B, putting that formula in Sheet1 column C will return the rank of the second list in Sheet2 right beside the rank of list one in Sheet1.
    you can also do the reverse in Sheet2 with =VLOOKUP(A2,Sheet1!A:B,2,FALSE)
    hope that helps get you started.
    BTW, you might need to change commas to semicolons if your settings require it.
    For a better formula follow the instructions in the yellow banner at the top and upload a sample workbook WITH expected results (though they don't sound that complicated).
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Comparing two lists for positional changes

    Hi legaltron,

    Welcome to the Forum.

    I aadded a number, 1 - 149, to both lists, the sorted them alphabetically and placed them side by side.
    Then showed the differences between the numbers allocated.
    Please see attached.
    Does this work for you?

    Regards

    peterrc
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Bistrita, Romania
    MS-Off Ver
    2019; 365
    Posts
    231

    Re: Comparing two lists for positional changes

    In case you must keep the lists in the order you posted, attached is another possible solution.

    I used formulas to rank instead of 1-149 so that they apply for any country order.
    Attached Files Attached Files
    To show your appreciation
    Click ★ Add reputation!

+ 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. conditional formatting - positional
    By kennylarid in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-27-2015, 01:43 PM
  2. Comparing two lists
    By Imran688 in forum Excel General
    Replies: 2
    Last Post: 08-03-2012, 10:31 AM
  3. [SOLVED] Excel 2007 : Offset - Positional Reference
    By colorkid in forum Excel General
    Replies: 9
    Last Post: 07-05-2012, 02:22 PM
  4. comparing lists
    By jspinx in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-14-2009, 02:12 PM
  5. comparing from two lists
    By foltza in forum Excel General
    Replies: 1
    Last Post: 01-19-2007, 03:17 PM
  6. positional relevance
    By rodchar in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-18-2006, 04:35 PM
  7. [SOLVED] comparing lists
    By Gary L Brown in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 04:05 AM

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