Results 1 to 5 of 5

2 Columns - Compare columns and give a percentage of number of matches

Threaded View

  1. #1
    Registered User
    Join Date
    08-22-2020
    Location
    New York
    MS-Off Ver
    2016
    Posts
    4

    Post 2 Columns - Compare columns and give a percentage of number of matches

    I have 2 columns of numbers. Column 1 is numbered from 1 - 10 in sequence. Column 2 can be in sequential order or mixed up.

    It may look like this:
    1 1
    2 2
    3 3
    4 4
    5 5
    6 6
    7 7
    8 8
    9 9
    10 10

    or it can look like this:
    1 1
    2 2
    3 3
    4 4
    5 6
    6 8
    7 7
    8 9
    9 5
    10 10

    or it can look like this:
    1 1
    2 2
    3 3
    4 4
    5
    6 5
    7
    8 7
    9 8
    10 6

    I want to get the percentage of the total numbers that match in each column. 1st example is a 100% match. 2nd example is 60%. 3rd example is 40% and it has blank spaces.

    The actual formula would be great, but just the function that would perform this calculation would be helpful. I'm not even sure Excel can perform this operation.

    To make it more complicated, in the 3rd example, the 5th stop was canceled, so I would consider it matching the number 5 in the left column...in other words, where there is a blank space on the right, I want to shift the numbers down on the left by 1 row. This would make the number 5 match in both columns. Then do the same thing for the next blank space.

    (These are 10 stops that our delivery trucks have to do every day. Sometimes they have to cancel a stop, hence the blank spaces).

    The goal is to see if our drivers are following the routing that is given to them every day...and if not, which percentage of deviation is there. We are developing this software and are working on giving our drivers the best possible routing, but if they don't follow it, it does no good. This formula will help us evaluate this.

    Thank you!

    Dave
    Last edited by Dave1969; 08-22-2020 at 11:43 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Compare Sum of 2 Columns and Give Message Box
    By hutch@edge.net in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-08-2019, 03:13 PM
  2. Compare 2 columns and give % difference
    By grafx77 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-24-2018, 05:38 AM
  3. Compare multiple columns for match then give MIN
    By em1335 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-01-2016, 03:43 PM
  4. please help with compare two columns and give me result?
    By tomislav91 in forum Excel General
    Replies: 3
    Last Post: 01-24-2015, 02:48 PM
  5. Replies: 1
    Last Post: 09-26-2013, 12:16 PM
  6. Compare 2 other columns if first one matches
    By dclive in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-21-2011, 05:56 AM
  7. Compare 2 columns then give count
    By George4 in forum Excel General
    Replies: 27
    Last Post: 10-02-2008, 11:43 PM

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