Comparing 2 columns of numbers for differences

[silverlucky5]

Hi,
I have two lists of numbers that are supposed to be identical but are not.
Some numbers are in col 1 and not in col 2.
Some numbers are in Col 2 and not in Col 1. (see sample).
With my sample, since it is very small, it is easy enough to compare the two columns to see the differences.
But the columns I am actually comparing are thousands of rows long (and unlike the sample below, in my actual data, the "matches" are not aligned.).

Is there any way to write a macro to do this compare and tell me which numbers are in col 1 and not in col 2 and which numbers are in col 2 and not in col 1?

Thanks for any help!

<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; width: 48pt;" height="17" width="64">col 1</td> <td class="xl25" style="border-left: medium none; width: 48pt;" width="64">col 2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">1234</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">1234</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">5678</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">5678</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">10122</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">10122</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">14566</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">14566</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">19010</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">19010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">23454</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">27898</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">32342</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">32342</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">36786</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">36786</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">41230</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">41230</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">45674</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">45674</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">50118</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">54562</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">54562</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">59006</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">59006</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">63450</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">63450</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">67894</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">67894</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">72338</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">81226</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">81226</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">85670</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">85670</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">72338</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">76782</td> </tr> </tbody></table>

 

[shmop]

It is still not keeping duplicates in the results, as shown here with the figure 11900 in bold:
[TABLE="class: grid, width: 992, align: left"]
<tbody>[TR]
[TD] $ 1,116.00[/TD]
[TD] $ 11,900.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.385442[/TD]
[TD="align: right"]0.033363[/TD]
[TD="align: right"]0.012381[/TD]
[TD="align: right"]0.480851[/TD]
[TD][/TD]
[TD="align: right"]$434.23[/TD]
[TD][/TD]
[TD="align: right"]0.047912[/TD]
[TD="align: right"]0.678858[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] $ 862.00[/TD]
[TD] $ 7,659.44[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.202589[/TD]
[TD="align: right"]0.607413[/TD]
[TD="align: right"]0.894192[/TD]
[TD="align: right"]0.736121[/TD]
[TD][/TD]
[TD="align: right"]$4,836.00[/TD]
[TD="align: right"]$4,836.00[/TD]
[TD="align: right"]0.534051[/TD]
[TD="align: right"]0.791217[/TD]
[TD="align: right"]0.120874[/TD]
[TD="align: right"]0.281256[/TD]
[/TR]
[TR]
[TD] $ 4,010.81[/TD]
[TD] $ 1,050.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.094725[/TD]
[TD="align: right"]0.593258[/TD]
[TD="align: right"]0.184627[/TD]
[TD="align: right"]0.299355[/TD]
[TD][/TD]
[TD="align: right"]$794,397.74[/TD]
[TD="align: right"]$794,397.74[/TD]
[TD="align: right"]0.238954[/TD]
[TD="align: right"]0.164806[/TD]
[TD="align: right"]0.030449[/TD]
[TD="align: right"]0.788583[/TD]
[/TR]
[TR]
[TD] $ 105.63[/TD]
[TD] $ 7,875.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.130755[/TD]
[TD="align: right"]0.198909[/TD]
[TD="align: right"]0.795033[/TD]
[TD="align: right"]0.723193[/TD]
[TD][/TD]
[TD="align: right"]$4,010.81[/TD]
[TD="align: right"]$4,010.81[/TD]
[TD="align: right"]0.094725[/TD]
[TD="align: right"]0.593258[/TD]
[TD="align: right"]0.879585[/TD]
[TD="align: right"]0.480758[/TD]
[/TR]
[TR]
[TD] $ 7,659.44[/TD]
[TD] $ 885.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.666986[/TD]
[TD="align: right"]0.609514[/TD]
[TD="align: right"]0.957797[/TD]
[TD="align: right"]0.704039[/TD]
[TD][/TD]
[TD="align: right"]$13,412.68[/TD]
[TD="align: right"]$13,412.68[/TD]
[TD="align: right"]0.299117[/TD]
[TD="align: right"]0.921953[/TD]
[TD="align: right"]0.89274[/TD]
[TD="align: right"]0.20705[/TD]
[/TR]
[TR]
[TD] $ 11,900.00[/TD]
[TD] $ 5,786.95[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.05453[/TD]
[TD="align: right"]0.161659[/TD]
[TD="align: right"]0.676664[/TD]
[TD="align: right"]0.797224[/TD]
[TD][/TD]
[TD="align: right"]$773.00[/TD]
[TD][/TD]
[TD="align: right"]0.949717[/TD]
[TD="align: right"]0.603786[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] $ 7,875.00[/TD]
[TD] $ 3,134.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.300635[/TD]
[TD="align: right"]0.291135[/TD]
[TD="align: right"]0.411968[/TD]
[TD="align: right"]0.711831[/TD]
[TD][/TD]
[TD="align: right"]$862.00[/TD]
[TD="align: right"]$862.00[/TD]
[TD="align: right"]0.202589[/TD]
[TD="align: right"]0.607413[/TD]
[TD="align: right"]0.157927[/TD]
[TD="align: right"]0.244103[/TD]
[/TR]
[TR]
[TD] $ 885.00[/TD]
[TD] $ 17,154.66[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.225264[/TD]
[TD="align: right"]0.181088[/TD]
[TD="align: right"]0.80302[/TD]
[TD="align: right"]0.739839[/TD]
[TD][/TD]
[TD="align: right"]$82,721.94[/TD]
[TD="align: right"]$82,721.94[/TD]
[TD="align: right"]0.179515[/TD]
[TD="align: right"]0.391647[/TD]
[TD="align: right"]0.939701[/TD]
[TD="align: right"]0.009363[/TD]
[/TR]
[TR]
[TD] $ 13,412.68[/TD]
[TD] $ 382.75[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.299117[/TD]
[TD="align: right"]0.921953[/TD]
[TD="align: right"]0.731716[/TD]
[TD="align: right"]0.521891[/TD]
[TD][/TD]
[TD="align: right"]$1,116.00[/TD]
[TD][/TD]
[TD="align: right"]0.385442[/TD]
[TD="align: right"]0.033363[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] $ 12,353.40[/TD]
[TD] $ 28,185.26[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.671154[/TD]
[TD="align: right"]0.929144[/TD]
[TD="align: right"]0.762655[/TD]
[TD="align: right"]0.63597[/TD]
[TD][/TD]
[TD="align: right"]$11,900.00[/TD]
[TD="align: right"]$11,900.00[/TD]
[TD="align: right"]0.238418[/TD]
[TD="align: right"]0.669121[/TD]
[TD="align: right"]0.279597[/TD]
[TD="align: right"]0.287121[/TD]
[/TR]
[TR]
[TD] $ 4,836.00[/TD]
[TD] $ 56,424.88[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.534051[/TD]
[TD="align: right"]0.791217[/TD]
[TD="align: right"]0.50736[/TD]
[TD="align: right"]0.822868[/TD]
[TD][/TD]
[TD="align: right"]$7,659.44[/TD]
[TD="align: right"]$7,659.44[/TD]
[TD="align: right"]0.666986[/TD]
[TD="align: right"]0.609514[/TD]
[TD="align: right"]0.894192[/TD]
[TD="align: right"]0.736121[/TD]
[/TR]
[TR]
[TD] $ 4,339.00[/TD]
[TD] $ 17,210.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.025807[/TD]
[TD="align: right"]0.787434[/TD]
[TD="align: right"]0.311597[/TD]
[TD="align: right"]0.083433[/TD]
[TD][/TD]
[TD="align: right"]$2,059.89[/TD]
[TD="align: right"]$2,059.89[/TD]
[TD="align: right"]0.99572[/TD]
[TD="align: right"]0.068684[/TD]
[TD="align: right"]0.160489[/TD]
[TD="align: right"]0.091684[/TD]
[/TR]
[TR]
[TD] $ 3,028.00[/TD]
[TD] $ 3,264.03[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.664244[/TD]
[TD="align: right"]0.722138[/TD]
[TD="align: right"]0.943889[/TD]
[TD="align: right"]0.021488[/TD]
[TD][/TD]
[TD="align: right"]$41,108.80[/TD]
[TD="align: right"]$41,108.80[/TD]
[TD="align: right"]0.13475[/TD]
[TD="align: right"]0.461903[/TD]
[TD="align: right"]0.592935[/TD]
[TD="align: right"]0.959359[/TD]
[/TR]
[TR]
[TD] $ 2,162.50[/TD]
[TD] $ 25.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.166707[/TD]
[TD="align: right"]0.986398[/TD]
[TD="align: right"]0.025009[/TD]
[TD="align: right"]0.623448[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$377.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.299969[/TD]
[TD="align: right"]0.064391[/TD]
[/TR]
[TR]
[TD] $ 17,210.00[/TD]
[TD] $ 11,900.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.100272[/TD]
[TD="align: right"]0.823058[/TD]
[TD="align: right"]0.279597[/TD]
[TD="align: right"]0.287121[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$14,169.98[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.219447[/TD]
[TD="align: right"]0.447135[/TD]
[/TR]
[TR]
[TD] $ 13,096.81[/TD]
[TD] $ 488.83[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.592931[/TD]
[TD="align: right"]0.711051[/TD]
[TD="align: right"]0.152131[/TD]
[TD="align: right"]0.994148[/TD]
[TD][/TD]
[TD="align: right"]$2,162.50[/TD]
[TD="align: right"]$2,162.50[/TD]
[TD="align: right"]0.166707[/TD]
[TD="align: right"]0.986398[/TD]
[TD="align: right"]0.146753[/TD]
[TD="align: right"]0.959253[/TD]
[/TR]
[TR]
[TD] $ 11,900.00[/TD]
[TD] $ 3,540.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.238418[/TD]
[TD="align: right"]0.669121[/TD]
[TD="align: right"]0.665083[/TD]
[TD="align: right"]0.086757[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$3,643.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.15205[/TD]
[TD="align: right"]0.552229[/TD]
[/TR]
</tbody>[/TABLE]