Compare Worksheets, Add Data if Matched

[sd2173]

Good evening all,

Please excuse my inexperience. I am trying to find a way to systematically go row by row on one worksheet, and for each ID, see below, add data to another worksheet in the row with a matching ID. Below is information on how my worksheets are setup.


Worksheet “10” data organized as follows:
A_________B_________C_________D_________E_________
123456______Doe________John_______10__________3.5________
654321______Jane________Doe_______12__________12_________


For each ID in Column 'A', I want to go to Worksheet “Data”, search for that ID (also in Column 'A'), if found add an “X” to Column 'E' on Worksheet “Data”. So for example, if Doe Jane was on Worksheet “10”, I would like Worksheet “Data” to reflect an 'X' for her in Column 'E'. Below is another example, John Doe was not given an 'X' because his ID was not found on Worksheet “10”.


Worksheet “Data” hoped to reflect this:
A_________B_________C_________D_________E_________
123456______Doe________John_______10__________ __________
654321______Jane________Doe_______12__________X__________


Worksheet “Data” will contain multiple entries with the same ID so I need to re-search row by row for each ID found on Worksheet “10”. I am hoping this is a simple enough task, but have little to no experience searching row by row and comparing to other worksheets and adding to found rows.


Any insight/guidance appreciated, thanks for taking the time to read this.


Regards,
Spencer

 

[Trebor76]

Hi Spencer,

You don't need a macro for this - just put the following formula into the starting cell (assumed to be E2) of the 'Data' tab, change the lookup reference to the '10' tab and copy down as required:

=IF(ISERROR(VLOOKUP(A2,'10'!$A$2:$A$3,1,FALSE)),"","x")

Note that John Doe will return a 'x' as his ID is in the '10' tab.

HTH

Robert

 

[sd2173]

Robert,

Thank you for your help, this works fantastic! Further down the road I may try to incorperate this method to return information back to another sheet but do addition in the cells in the data sheet.

Example:
=IF(ISERROR(VLOOKUP(A2,'10'!$A$2:$A$3,1,FALSE)),"", e2+1)

Roughly running this I get a circular error, is this request not possible and the best way is to run the math in a dummy column then copy over with a macro? Or is there a way to do so with a similar formula?

Also, if possible can you break down what the different sections of the formula are doing? The next piece I want to do is look at rows of info and transfer is another sheet + add according to info found in columns but still use the ID number search/add method between sheets.

Example:
Sheet AAA
A_________B_________C_________D_________E_________
123456______Doe________John_______MA__________F_________
123456______Doe________John_______MG__________F_________
654321______Jane________Doe_______DG__________F_________
123456______Doe________John_______LL__________F_________

this info is searched and then transfered to another sheet...

Example:
Sheet BBB
A_______B_______C_______D_______E_______F______G_______
123456____Doe______John_____1________2_______ ________ ________
654321____Jane______Doe_____ ________ ________ ________1_______

The results showing 2 for John in E because he had 2 "M" tags in Column D one "L" etc. etc.

I'm hoping a break down of your formula may help in this next task so I don't have to keep asking questions, but figure that is unevitable.

Thank you so much again for your time and suggestions.

Regards,
SD

 

[Trebor76]

Roughly running this I get a circular error, is this request not possible and the best way is to run the math in a dummy column then copy over with a macro? Or is there a way to do so with a similar formula?

You're getting a circular reference because the formula is trying to add to itself in Col E. It seems that (and correct me if I'm wrong) that if a ID is found you're trying to increment it by one. If so try this:

=IF(ISERROR(VLOOKUP(A2,'10'!$A$2:$A$3,1,FALSE)),"", VLOOKUP(A2,'10'!$A$2:$A$3,1,FALSE)+1)

Also, if possible can you break down what the different sections of the formula are doing?

It is simply looking up the value in cell A2 of the current sheet to the range $A$2:$A$3 (adjust to suit) of the "10" tab and if there's no error increment the ID by one (in my revised formula). If there's no match a blank (instead of an error message) is returned.

The next piece I want to do is look at rows of info and transfer is another sheet + add according to info found in columns but still use the ID number search/add method between sheets.

As this is totally different from you're original request, you'll need to start a new thread for it.

Robert

 

[sd2173]

Hey Robert,

I am trying to expand the formula you suggested to include searching from two other worksheets. I want to do this because if I run three seperate formulas and drag them down the column I lose my 'X's from the previous formula.

I've tried the formula below, but it says I've entered too many arguments:

=IF(ISERROR(VLOOKUP(A2,'10TH'!$A$2:$A$3,1,FALSE)),"","X", IF(ISERROR(VLOOKUP(A2,'11TH'!$A$2:$A$3,1,FALSE)),"","X",IF(ISERROR(VLOOKUP(A2,'12TH'!$A$2:$A$3,1,FALSE)),"","X"))

Any help or suggestions much appreciated.

 

[sd2173]

Another thing I just discovered... I though the formula ran great, but now I just noticed it is only X'ing on the Data sheet the first match from the 10 sheet and no others...?

I ran the same formula for sheets 10, 11, and 12 and got the same result. So now I guess I need to tweak the formula and see if I can't run if to review all three sheets still.

Spencer

 

[sd2173]

Ok... I've figured out the second problem I thought I'd discovered... I forgot to extend the search range for the 10 sheet. I accidently left it on $A$3 instead of changing to 65,000.

=IF(ISERROR(VLOOKUP(A2,'10'!$A$2:$A$3,1,FALSE)),"","x")

Still wondering though if possible to use this same formula/paste down but searching 2 other sheets.