Combining rows that have corresponding values

[TonytheEE]

Hey all,

I'm doing a project that is requiring me to assess changes to a system. Basically, at different network addresses, there are device tags. We have an old database pull that we've updated with our notes, and are about to pull their database again. These will NOT be the same. Things have been altered, removed and added from both, and I'm trying to find a way to isolate what is different (what tags have changed, what's been added/removed. I can compare the results, but the challenge is in combining both databases.


Something like this:


OLD DB

Address Tag

R1S1 Valve 3

R1S2 Valve 4

R1S3 Valve 5

R1S4 Pump 1


NEW DB

Address Tag

R1S1 Valve 3

R1S2 Valve 9

R1S3 Valve 5

R1S6 Pump 2


Combined to make this:


Address OLD Tag NEW Tag

R1S1 Valve 3 Valve 3

R1S2 Valve 4 Valve 9

R1S3 Valve 5 Valve 5

R1S4 Pump 1

R1S6 Pump 2


Note there are addresses that are exclusive and changing values.

Is there a simple method to doing this in excel, and possibly a macro solution?


Thanks!


Tony

 

[kchiba]

Hi Tony,


This is a long-winded solution.


1. Combine the Address column of both lists into one list

2. Remove duplicates from this list (possible with a click in Excel 2007 and above)

3. On the new reduced list use VLOOKUP to lookup the value in the OLD DB and in the next column VLOOKUP the values in the NEW DB. This will give you the two tags side by side or #N/A where it does not exist in either DB.


Please let us know if you need help on this solution, alternatively all the above steps can be placed in a Macro.

 

[TonytheEE]

I think that'll do it. Thanks kchiba!