+ Reply to Thread
Results 1 to 18 of 18

Compare 4 columns

  1. #1
    Registered User
    Join Date
    04-28-2020
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    8

    Compare 4 columns

    Hello all!

    I'm trying to compare 4 columns. I want to find a match from 2 columns, which I've already figured out using VLOOKUP. Once I find a match I want to compare values in two other columns using < operator and return true or false. Is there a way to do this in Excel? Hopefully this makes sense.

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Compare 4 columns

    Try uploading a sample worksheet that contains a layout and some sample data that mimics the kind of data you're working with. Provide an example and the expected results. Without this, any solution would be very generic and may not apply to your situation.

  3. #3
    Registered User
    Join Date
    04-28-2020
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Compare 4 columns

    Sure, so basically I have a device list sheet with their BIOS versions. And I have another sheet with the most up to date BIOS from the manufacturer. I want to make a match with say laptop6 and compare the devices BIOS with the manufacturer's BIOS.
    If the device's BIOS is less than the version number from the manufacturer I would like to have a way to note that the BIOS needs an update. So in the pics below. I have a match with laptop 6,
    and the device's BIOS version is less than the manufacturer's version so it needs an update. Hopefully this makes a little more sense now.

    deviceBIOS.png

    userBIOS.png

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Compare 4 columns

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  5. #5
    Registered User
    Join Date
    04-28-2020
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Compare 4 columns

    Of course. Here's a mock up of what I would like done. Hopefully this helps. Sorry for the confusion.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Compare 4 columns

    f2 =Vlookup(A2,CurrentBIOS!$A$2:$B$10,2,0) and drag down

    g2 =MID(B2,3,LEN(B2)-4)*1 and drag down

    h2 =MID(F2,3,LEN(F2)-4)*1 and drag down

    result in E2 = if(H2>G2,"yes","no") and drag down


    See the attached file.


    edit

    MOD is wrong translation, wait a second, will be changed soon. => MID
    Last edited by oeldere; 04-29-2020 at 01:33 PM.
    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.

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

    Re: Compare 4 columns

    Please try at C2
    =IF(B2=VLOOKUP(A2,CurrentBIOS!$A$2:$B$10,2,0),"No","Yes")
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    04-28-2020
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Compare 4 columns

    That does work but I have a couple of values for bios that are like this A23. And this gives me an error like this. #VALUE!. I'm guessing it has to do with the letter in the bios version?

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Compare 4 columns

    @ John1974

    Since you got answers of several forummembers it is good to add to whom you replying.

  10. #10
    Registered User
    Join Date
    04-28-2020
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Compare 4 columns

    Quote Originally Posted by oeldere View Post
    f2 =Vlookup(A2,CurrentBIOS!$A$2:$B$10,2,0) and drag down

    g2 =MID(B2,3,LEN(B2)-4)*1 and drag down

    h2 =MID(F2,3,LEN(F2)-4)*1 and drag down

    result in E2 = if(H2>G2,"yes","no") and drag down


    See the attached file.


    edit

    MOD is wrong translation, wait a second, will be changed soon. => MID
    Sorry, meant to reply to you.

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Compare 4 columns

    There is no A23 in your file.

    in the 2nd worksheet you have a dot in one of the data (at the end of the data).
    so you have to check is there are no leading or trealing spaces in the second sheet.

    => a2 has a space at the end of the data.
    Last edited by oeldere; 04-29-2020 at 03:13 PM.

  12. #12
    Registered User
    Join Date
    04-28-2020
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Compare 4 columns

    Quote Originally Posted by oeldere View Post
    There is no A23 in your file.

    in the 2nd worksheet you have a dot in one of the data (at the end of the data).
    so you have to check is there are no leading or trealing spaces in the second sheet.

    => a2 has a space at the end of the data.
    Right I know there was no A23 in my sample file. I just copied and pasted a few lines. I checked my original sheet and there are no leading or trailing spaces in there. There was one in the sample but not my original.

  13. #13
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Compare 4 columns

    A couple things to note...

    1. If you have text in the cells other than the versions, you may need to strip that out first (can be part of a formula). If that text is vital, e.g. one might be A23 1.4.1 and the other A24 1.4.1, that would seem to be a "newer" version but would require a different set of test conditions.

    2. When working with numbers stored as text, a smaller number may be greater than a larger number, e.g. 1.6.1 would be considered greater than 1.16.4 because by the third character "1.6" > "1.1". You would likely need to do one of two things here: (1) split the x.y.z versions into separate strings, convert them to numeric values and compare them left-to-right, or split them into a concatenated text string and format them each as three characters long with preceeding zeros, e.g. 1.6.1 would become 001006001 while 1.16.4 would be 001016004. In that case, 1.16.4 would be seen as "greater than" because the 5th digit is larger (1 > 0).

  14. #14
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Compare 4 columns

    I took a little different approach. I used Power Query. I loaded each table to PQ. Then merged the two tables with an Inner Join. Once they were merged, I then added a column which indicated whether the two Bios matched or didn't match. It did not take into account a greater than or less than situation. Attached is the file for your review. The results shown below.

    v A B C D
    1 User's Device BIOS Table1.Current BIOS Match
    2 Latitude 5591 1.7.4 1.11.1 No Match
    3 Latitude 5480 1.10.4 1.16.0* No Match
    4 Latitude 5590 1.10.1 1.12.1 No Match
    5 Latitude 5491 1.10.1 1.11.1 No Match
    6 Latitude 5491 1.8.1 1.11.1 No Match
    7 Latitude 5491 1.6.1 1.11.1 No Match
    8 Latitude 5491 1.10.1 1.11.1 No Match
    9 Latitude 5580 1.15.1 1.16.0 No Match
    10 Latitude 5580 1.16.0 1.16.0 Match
    11 Latitude 7480 1.16.1 1.16.1 Match
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    04-28-2020
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Compare 4 columns

    Quote Originally Posted by alansidman View Post
    I took a little different approach. I used Power Query. I loaded each table to PQ. Then merged the two tables with an Inner Join. Once they were merged, I then added a column which indicated whether the two Bios matched or didn't match. It did not take into account a greater than or less than situation. Attached is the file for your review. The results shown below.

    v A B C D
    1 User's Device BIOS Table1.Current BIOS Match
    2 Latitude 5591 1.7.4 1.11.1 No Match
    3 Latitude 5480 1.10.4 1.16.0* No Match
    4 Latitude 5590 1.10.1 1.12.1 No Match
    5 Latitude 5491 1.10.1 1.11.1 No Match
    6 Latitude 5491 1.8.1 1.11.1 No Match
    7 Latitude 5491 1.6.1 1.11.1 No Match
    8 Latitude 5491 1.10.1 1.11.1 No Match
    9 Latitude 5580 1.15.1 1.16.0 No Match
    10 Latitude 5580 1.16.0 1.16.0 Match
    11 Latitude 7480 1.16.1 1.16.1 Match
    This seems to work well. Would you be able to explain how you created this so I could recreate it on my original worksheet?
    Thanks!

  16. #16
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Compare 4 columns

    Post 14 explains step by step. If you want to learn more about Power Query, click on the link in my signature. Also the book, "M is for (Data) Monkey" available on Amazon is an excellent primer for this powerful Excel feature.

  17. #17
    Registered User
    Join Date
    04-28-2020
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Compare 4 columns

    Quote Originally Posted by alansidman View Post
    Post 14 explains step by step. If you want to learn more about Power Query, click on the link in my signature. Also the book, "M is for (Data) Monkey" available on Amazon is an excellent primer for this powerful Excel feature.
    I was actually able to dissect what was going on in your example and reproduce it on mine. Thanks again for your help! Much appreciated!

  18. #18
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Compare 4 columns

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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. Replies: 3
    Last Post: 02-06-2020, 04:37 PM
  2. Replies: 6
    Last Post: 11-02-2018, 06:56 AM
  3. Replies: 2
    Last Post: 11-27-2016, 05:56 PM
  4. Replies: 6
    Last Post: 09-21-2016, 04:21 PM
  5. Macro to Compare two columns and publish sheet name in case columns dont match
    By adaws in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-06-2014, 01:21 PM
  6. Replies: 2
    Last Post: 02-05-2014, 07:30 PM
  7. Replies: 6
    Last Post: 05-30-2013, 04:58 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