Consolidation of data

[Chris]

Help. I am a bit lost. I have a hugh list of customer's
from two different databases. The client numbers will be
the same, but the rating will differ. I want to compare
the two and find out what are the conflicts.
Please see example:
A B C

WEXFORD AUTOMOTIVIE 9514111 B
WEXFORD AUTOMOTIVIE 9514111 B+
WILSHIRE HAIRCARE 9512211 C
WILSHIRE HAIRCARE 9512211 C-

What I want is for it to use the client numbers (B) as the
main reference point and then compare column C, if there
is a conflict between the data, I want it to kick the
client number to a seperate list.

 

[Dave Peterson]

Are the two databases in two different worksheets (I hope, I hope, I hope).

If they are, then I'd create a third worksheet.

Copy the key values from both worksheets into a giant list.
Use Data|filter|Advanced Filter to extract the unique entries
Debra Dalgleish has notes at:
http://www.contextures.com/xladvfilter01.html#FilterUR

Then use a bunch of =vlookup()'s to return the individual fields from the
original workbooks.

I'd end up with 3 columns:

CommonKey DB#1 DB#2

DB#1 contains a formula like:
=vlookup(A2,sheet1!$b$2:$c$9999,2,false)
(dragged down)

DB#2 contains
=vlookup(A2,sheet2!$b$2:$c$9999,2,false)
(dragged down)

Now copy|paste special|values
edit|replace #n/a with (leave blank) to clean up errors.

And add a fourth column (differences) with a formula like:
=if(b2=c2,"","<---")
and drag down

Then apply data|filter|autofilter and filter on that last column.

You could copy and paste to new sheets, but I really like to keep the data
together. It makes life much simpler.

 

[Chris]

Works great, thanks Dave.
C.

-----Original Message-----
Are the two databases in two different worksheets (I hope, I hope, I hope).

If they are, then I'd create a third worksheet.

Copy the key values from both worksheets into a giant list.
Use Data|filter|Advanced Filter to extract the unique entries
Debra Dalgleish has notes at:
http://www.contextures.com/xladvfilter01.html#FilterUR

Then use a bunch of =vlookup()'s to return the individual fields from the
original workbooks.

I'd end up with 3 columns:

CommonKey DB#1 DB#2

DB#1 contains a formula like:
=vlookup(A2,sheet1!$b$2:$c$9999,2,false)
(dragged down)

DB#2 contains
=vlookup(A2,sheet2!$b$2:$c$9999,2,false)
(dragged down)

Now copy|paste special|values
edit|replace #n/a with (leave blank) to clean up errors.

And add a fourth column (differences) with a formula like:
=if(b2=c2,"","<---")
and drag down

Then apply data|filter|autofilter and filter on that last column.

You could copy and paste to new sheets, but I really like to keep the data
together. It makes life much simpler.


--

Dave Peterson
(e-mail address removed)
.