Join Data and Verify Info Down the Column

[Kristie_Pires]

Hello there,

I am trying to validate data across two systems with multiple borrowers and guarantors. Let's say ABC Corp, first guarantor "Ana M" needs to match in the second system (system B). So, I am checking if that guarantor name is spelled correctly or if it's even included in the second system. The problem is the data isn't in order and I can't guarantee that just sorting the fields will work. Any ideas where I can verify the guarantor's info across systems when the data isn't in the right order? 

System A
Borrower Name	Guarantor Name	Guarantor Address
ABC Corp	Ana M	123 Happy St
ABC Corp	Paula A	400 Paula St
ABC Corp	Peter J	100 Peter Av
Smile Inc	Paul J	333 Paul Av
Smile Inc	Ana N	777 Ana St
Bank Inc	Bank J	1 Bank Dr

System B
Borrower Name	Guarantor Name	Guarantor Address
Bank Inc	Bank J	1 Bank Dr
ABC Corp	Paula A	400 Paula St
ABC Corp	Peter J	100 Peter Av
ABC Corp	Ana M	123 Happy St
Smile Inc	Ana N	777 Ana St
Smile Inc	Paul J	333 Paul Av

Thank you so much!!

[Prometheus]

@Kristie_Pires You can use a Join tool. Just choose which fields you want to join on. Since System A data is the same as System B data, it'll all match up.

 

[Kristie_Pires]

@Prometheus Thank you - maybe I oversimplified in my example. In reality, the data isn't as clean, so it won't match up exactly. For example, the Borrower Name on System A is "Paula A" and on System B, it's "Paula AA". I need to be able to look at those two names and say on my validation "Data Do Not Match".

[Prometheus · Accepted solution]

@Kristie_Pires You can use a Join tool and flag anything that doesn't join. You can also add Fuzzy Matching to the mix for those records that didn't join. You can configure it to output unmatched records along with the match score.