Skip to Content
0
May 16, 2015 at 03:31 PM

How to find if a data in one column is in the data in another column

24 Views

I have two universes, that gets data from system A and B, each of them store data of automobile. system A stores the Vin number data in its full 17 digit, whereas system B stores data either 17 or last 8 digits of the vin. Some of the data in System A is in System b but could be just stored as 8 digit vin.

my task is to find out the the total count of unique Vin in the whole system.

here is the sample data

system A

business unit date SOURCE SYSTEM vin cars 2015/04 pc VU10D95V23Z64A56 cars 2015/04 pc KW99W47D82T88S55 cars 2015/04 pc HE59I87C89N67V35 cars 2015/04 pc XG60F16U74Z38L20 cars 2015/04 pc SA68U97O56J49O23 cars 2015/04 pc ZJ60Z16I44L31R70 cars 2015/04 pc MJ28Z63H23Q49H95 cars 2015/04 pc UI53C83H31C45D10 cars 2015/04 pc RE92S32L32T19U35 cars 2015/04 pc XV99R93E37B29Z24 cars 2015/04 pc WJ11W64O56C54T86 cars 2015/04 pc TZ30N65W48X80S69 cars 2015/04 pc SL59J90R40U75R61 cars 2015/04 pc KJ20H71I70A99N18 cars 2015/04 pc QD57A19B78U91X18 cars 2015/04 pc UR40F37A82S65K75 cars 2015/04 pc SF31C46B39U14S30 cars 2015/04 pc DB35M38Y15R23M70 cars 2015/04 pc SO75R97M20X80I70 cars 2015/04 pc RD50Y14I20X80I70 cars 2015/04 pc MP48T45C52O66C66 cars 2015/04 pc IV91O88N63N23N20

Source System B

business unit date SOURCE SYSTEM vin cars 2015/04 Mac 23Z64A56 cars 2015/04 Mac KW99W47D82T88S55 cars 2015/04 Mac 92D42KD7 cars 2015/04 Mac XG60F16U74Z38L20 cars 2015/04 Mac 56J49O23 cars 2015/04 Mac 44L31R70 cars 2015/04 Mac 98U65P40 cars 2015/04 Mac 31C45D10 cars 2015/04 Mac 32T19U35 cars 2015/04 Mac XV99R93E37B29Z24 cars 2015/04 Mac 56C54T86 cars 2015/04 Mac 48X80S69 cars 2015/04 Mac 40U75R61 cars 2015/04 Mac MB82B85Y50E21D43 cars 2015/04 Mac DG59G64S76S61Q95 cars 2015/04 Mac FR71P67C50T88X90 cars 2015/04 Mac 39U14S30 cars 2015/04 Mac 15R23M70 cars 2015/04 Mac 98A67Q11 cars 2015/04 Mac 20X80I70 cars 2015/04 Mac 52O66C66 cars 2015/04 Mac 63N23N20

i need a table that is like this

Business unit Date Count Cars 2015/04 27

Thank you very much in advance