• 0

[Excel] Compare Values in Cells then copy over to another sheet


Question

Hey guys,

I was wondering if one of you could help me out on this:

I have two sheets in a workbook with individuals names and emails. Most individuals are duplicates on both sheets, except sheet 1 has a "Lesson Status" field. I am interested to compare the sheets using their email (unique value), and if they are present on sheet 1 and 2, then put "Completed" in sheet 1's Lesson status field, otherwise, put "Not completed". Any ideas?

Thanks in advance.

Link to comment
Share on other sites

7 answers to this question

Recommended Posts

  • 0

After i reread my statement question, i figured it might sound confusing. I am looking for something like this, but obviously in excel/vb

If T4 Is in P7:P20 then
		N4 = "Completed"
Else
		N4 = "Not Completed"
Endif
Then loop for t5,t6, etc.

Link to comment
Share on other sites

  • 0

You don't need VBA to do this. You can simply use an Excel formula in the target cell that does a lookup in the second sheet.

Something like this (I can't give the exact formula without knowing the layout of your spreadsheet):

=IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$A$4,1,FALSE)),"Not Completed","Completed")

Replace "$A$1:$A$4" with the range of the lookup values.

Replace "A1" with the range of the cell whose value you want to lookup.

You can then CTRL+D the formula all the way down your spreadsheet. Ain't Excel great!

However, I should point out that you are clearly using Excel for the wrong purpose. Microsoft Access, if you have it, would be much better suited for what you seem to be doing. Spreadsheets are not meant to function like database applications!

Edited by boogerjones
Link to comment
Share on other sites

  • 0
You don't need VBA to do this. You can simply use an Excel formula in the target cell that does a lookup in the second sheet.

Something like this (I can't give the exact formula without knowing the layout of your spreadsheet):

=IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$A$4,1,FALSE)),"Not Completed","Completed")

Replace "$A$1:$A$4" with the range of the lookup values.

Replace "A1" with the range of the cell whose value you want to lookup.

You can then CTRL+D the formula all the way down your spreadsheet. Ain't Excel great!

However, I should point out that you are clearly using Excel for the wrong purpose. Microsoft Access, if you have it, would be much better suited for what you seem to be doing. Spreadsheets are not meant to function like database applications!

Thanks! Works great!

I agree that an actual database would be great for this, but our database program spits things out in csv, so its easier to just do things in excel. Once again, thanks for your help.

My final code ended up being:

=IF(ISNA(VLOOKUP(T4,'RBST Certification'!$L$1:$L$550,1,FALSE)),"Not Completed","Completed")

Link to comment
Share on other sites

  • 0

My boss just requested another thing....

Is it possible to instead of haveing the fixed values of completed, not completed to put in whatever value is in another cell of the second sheet?

for example, if the person's email exists on sheet 1 and 2, then put value of 'Sheet 2'!F3 into 'Sheet 1'!G4

Link to comment
Share on other sites

  • 0
My boss just requested another thing....
Maybe your boss should fire you and hire someone who knows what they're doing. :ninjapirate:
Is it possible to instead of haveing the fixed values of completed, not completed to put in whatever value is in another cell of the second sheet?

for example, if the person's email exists on sheet 1 and 2, then put value of 'Sheet 2'!F3 into 'Sheet 1'!G4

Check out the VLOOKUP function. The third parameter will return a value from a different column of the lookup table. In the function you are currently using, the lookup table has only one column and we are returning a value from column 1 of that range.
Link to comment
Share on other sites

  • 0
Maybe your boss should fire you and hire someone who knows what they're doing. :ninjapirate:

Ouch. Well its not everyday that an Information Security guy has to do excel spreadsheet stuff. Its more of a "who has ANY experience with excel? You? ok, here you go..."

Link to comment
Share on other sites

  • 0
Ouch. Well its not everyday that an Information Security guy has to do excel spreadsheet stuff. Its more of a "who has ANY experience with excel? You? ok, here you go..."
I know, I'm just giving you ****.
Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.