Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


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
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
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .