comparing data in 2 or more columns and return diferences in a different column

  • I am new to this forum and it is my first time posting. I hope I'm in the right place.


    I have 2 worksheets containing several columns with numerous rows of data.
    I have attached a sample of the data. I need to have excel search for the value in cell C3 sheet 2 in column C on sheet 1. If there are no matches then return the text in cell b3 sheet 2 on sheet 3 column b3.
    I need excel to compare all the values in column c sheet 2 against the valules in column c sheet 1 and give the differences in column b sheet 3 as described above.


    I have tried several different ways to do this within Excel but have not been able to do it. I do not know much about VBS so I need some help.
    Can this be done without using VBS? If not what would the formula in VBS be?


    Thanks


    Sorry for posting this here. I got here by searching and did not notice that questions were not supposed to be posted here. I don't know how to change threads.

  • Welcome to OzGrid


    I am not sure I have understood your question correctly, but have a look at the attached.


    It uses the following in cell B3 of Sheet3.


    =IF(SUMPRODUCT((Sheet2!C3=Sheet1!$C$3:$C$50)*1)=0,Sheet2!B3,Sheet2!J3)


    This will return Sheet2 cell B3 if there is no match of Sheet2 C3 in column C of Sheet 1. I wasn't sure what you wnated to reurn if there was a match, so I arbitarily reurned the value in Sheet2 Column J of the same row.


    The formula is copied down column B in Sheet3 to check each value in Sheet 2 column C.

  • I kinda have the same problem. My worksheet has many rows and I want the formula to be based on the row your in. But I dont want to copy and paste and change the code to refect the row because there are hundreds of rows.


    for example i have: =if(And(B3 = C3, C3 = D3), "pass", "fail")


    but i would like this code to work on hundreds of rows without having to manually change the row number Thanks in advance.

  • Re: comparing data in 2 or more columns and return diferences in a different column


    Hi All,


    It's been a great amount of value addition to me, going through all these posts.


    Could someone explain how to start new threads(new posts).


    Thanx
    PRadeep

  • Re: comparing data in 2 or more columns and return diferences in a different column


    kevin562,


    If you just highlight the first cell and the subsequent cells in the same column, then click Edit > Fill > Down, Excel will automatically increment the row numbers for you. I hope that's what you are after.


    pradeep,


    Welcome to the ozgrid forum.


    At the top left of the list of threads is a box titled New Thread; just click this and follow the instructions.


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!