Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an Excel workbook with 2 sheets.

1st Sheet: Old User Data 2nd Sheet: New User Data

I want to add a new column to the 2nd sheet that contains the old user ID from the 1st sheet. To get this, I want to compare the email address between both sheets.

I think the pseudo for what I want is as follows:

IF (NEW!E2 equal OLD!H2) THEN (NEW!B2 becomes OLD!A2)

So far I have, in a new column on the 2nd sheet the following:

=IF(NEW!E2=OLD!H2,NEW!B2=OLD!A2)

Seems to be the logic of the IF statement that is wrong.

share|improve this question

1 Answer 1

up vote 3 down vote accepted

An excel IF formula has the following construction:

IF( <condition> , <value if condition true> , <value if condition false> )

Immediately your formula has two big problems:

  • No false clause.
  • The true clause is a conditional, not a value.

What you need is another column, with an if statement that says (in pseudo!):

IF (NEW!E2 equal OLD!H2) THEN (set this cell to OLD!A2)
                         ELSE (set this cell to NEW!B2)

Or, in Excel:

IF( NEW!E2=OLD!H2 , OLD!A2 , NEW!B2 )

Obviously, you then end up with two ID columns on your sheet. But, once you're done you should be able to copy/paste-special-values this extra column over the real one.

Update

Depending on how your data is structured this might be useless anyway.

It could be better to replace the if's condition with some form of lookup, so you get (in pseudo!):

IF( [NEW!E2] is in OLD list ) THEN (set this cell to ID of matching entry)
                              ELSE (set this cell to NEW!B2)

Or, in Excel (line breaks so it fits!):

IF( VLOOKUP(NEW!E2,OLD!A1:G1000,1) = NEW!E2 ,
    VLOOKUP(NEW!E2,OLD!A1:G1000,2) ,
    NEW!B2)

Obviously, I've made up OLD!A1:G1000 so you'd have to provide your real table dimensions, and you would need to change the matching column number (the last value in VLOOKUP), and make sure the data in OLD is sorted by the email address, and refactor OLD so the email address is the first column (VLOOKUP requires ascending order and only looks in the first column for data - then returns the data from the same row in the requested column).

Update2:

I think I mis-read your question slightly, because what you ask for is a column of just the matching old values - in which case you don't need a false clause because when there isn't a match you want "nothing". In which case you can just use "" as a "do nothing" false clause to get blank cells.

share|improve this answer
    
thankyou, I will try this now. –  Tisch Sep 5 '10 at 16:14
    
I was almost there, the data types where mismatching as well which was causing issues. But thankyou kindly for your help. –  Tisch Sep 5 '10 at 16:23
    
thank you, I actually used "no match" prior to your update, but thankyou. VLOOKUP was the one I was looking at originally, I think this is what I need to learn now! I've managed to sort that part of the spreadsheet out now though. I think VLOOKUP would have saved a lot of time. c'est la vie. –  Tisch Sep 5 '10 at 21:09
1  
VLOOKUP is king. I should have learnt this straight away. –  Tisch Sep 5 '10 at 21:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.