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
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.
- 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.
- foo3 (if
statusis not null, then UPDATE the value for foo3 to the value from File2
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?