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.

Using the answer to this question as my starting point, I have a slightly different scenario that I'm stuck on.

Consider:

File 1: Incident# (key), foo1, foo2, foo3

File 2: Incident# (key), status, foo3, foo4, foo5, foo6

Other considerations:

An incident number may appear in File 1, but not File 2 (no status on incident; assumed open and unassigned).

An incident number may appear in File 2, but not File 1 (an incident that opened prior to the data's scope, but resolved after.

Right now, I'm tring to create File 3: by doing a VLOOKUP on Incident#, and I would like the following fields to appear, based on the following rules.

  1. Incident# (if incident# is in File 1 but NOT file 2, populate status with 'pending'. if incident# is in File 2 but NOT file 1, append the record to the file.
  2. foo1
  3. foo2
  4. foo3 (if status is not null, then UPDATE the value for foo3 to the value from File2
  5. foo4
  6. foo5
  7. foo6

However, because my result file is based on comparing values from BOTH sets of keyfields (not jsut a 'one-way' lookup), I fear I'll end up with Circular References and inaccurate data.

Any hints on how to make this happen?

share|improve this question
1  
This can be difficult to automate 100%, but if you’re willing to do 5% of the work, you can automate the rest. See this question. –  Scott Jan 29 '13 at 1:26
add comment

1 Answer

up vote 0 down vote accepted

Although Scott proposed a great solution (which I've got bookmarked 'cause I know I'll need it in the future...), we ended up being able to complete this with 'VLOOKUP' because the user suddenly "discovered" additional data columns in the excel files AND changed their requirements.

share|improve this answer
add comment

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.