Using Pivot Table with VLookup

[Shams]

Folks,
I have two sets of Data sitting in two separate
worksheets. For the purposes of my reconciliation work, I
need to compare the sets with one another. For example,

Set 1 has the following 3 fields(and accompanying
information):

Name Ticket # Fare

Johnson/F 1692742117 2075.23
Johnson/F 1692742117 (2,075.23)
PMT/ON 5027246983 67,851.36
PMT/ON 5027246983 (67,851.36)
Singh/F 1698798564 3250.36
Mirer/G 9707266335 542.36
Mirer/G 9707266335 (542.36)

Set 2 has the following information:

Name Ticket # Fare

Johnson/F 1692742117 2,075.23
PMT/ON 5027246983 67,851.36
Singh/F 1698798564 3250.36
Mirer/G 9707266335 542.36

My goal is to separate out the unique piece of information
that is missing from each set. For example, Johnson/F
from Set 1 has $$ in/out. In Set 2, Johnson/F only shows
the debit amount (implying a timing difference). If I
were to run a simple VlookUp based on the ticket number
(as the unique field) from Set 1, I will get incorrect
information. Since the tkt.#s for both the debit/credit
is the same, it will reference and spit out the same $$
amount from Set 2. That is it will list the positive
2,075.23 twice.

Is there a way that I can combine the Pivot Table to get
around this problem. Any suggestions? Thank you.

Regards,
Shams.

 

[dave]

Shams,

add a 4th field for both tables which concatenates ticket
# and fare into one field, making it unique for the debit
and credit, and vlookup on that one.

hth,
Dave

 

[Shams]

Dave,
Hi! Thanks for your reply. Unfortunately, the vlookup
doesn't seem to work based on the concat. field. It seems
like it is not recognizing the field. I tested it on a
row that exists in both sets but is returning with N/A.
Would you have any other suggestions? Thanks for your
help.

Shams.

 

[dave]

Could you send me the file? If you'd rather not one way
to figure out why its not readnig it correctly is to
compare in a separate cell the two concatenated fields
that should be matching by hittin =c6=e6(for example) then
highlight each cell reference and hit f9 which converts
the cell to the value behind the cell. Then youi can see
the difference. Sometimes there is an extra space in one
of them or one is formattred as text instead. These are
easily fixable problems.