Comparing 2 columns of numbers for differences

S

silverlucky5

New Member
Joined
Sep 8, 2009
Messages
35
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>
 
Sort by date Sort by votes
Allow me to start again. I am Comparing columns a & b. They have corresponding data in offset columns d-g. d-e corresponds to a and f-g to b.
Now with the results in col I, the offsets need to be "carried along" starting col K. Like this:
Excel 2010
ABCDEFGHIJKLMN
etc

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"][/TD]
[TD="align: center"]A Results[/TD]
[TD="align: center"]B Results[/TD]
[TD="align: center"]Col D[/TD]
[TD="align: center"]Col E[/TD]
[TD="align: center"]Col F[/TD]
[TD="align: center"]Col G[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]53.25[/TD]
[TD="align: right"]-7713.13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.043145[/TD]
[TD="align: right"]0.452047[/TD]
[TD="align: right"]0.307503[/TD]
[TD="align: right"]0.41653[/TD]
[TD="align: right"][/TD]
[TD="align: right"]53.25[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.043145[/TD]
[TD="align: right"]0.452047[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]53.33[/TD]
[TD="align: right"]-6136.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.67035[/TD]
[TD="align: right"]0.140147[/TD]
[TD="align: right"]0.516348[/TD]
[TD="align: right"]0.448563[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-7713.13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.307503[/TD]
[TD="align: right"]0.416530[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]100.00[/TD]
[TD="align: right"]-570.39[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.60863[/TD]
[TD="align: right"]0.724874[/TD]
[TD="align: right"]0.989515[/TD]
[TD="align: right"]0.289381[/TD]
[TD="align: right"][/TD]
[TD="align: right"]53.33[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.670350[/TD]
[TD="align: right"]0.140147[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]170.06[/TD]
[TD="align: right"]-464.42[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.23031[/TD]
[TD="align: right"]0.8398[/TD]
[TD="align: right"]0.436437[/TD]
[TD="align: right"]0.703325[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-6136[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.516348[/TD]
[TD="align: right"]0.448563[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]232.76[/TD]
[TD="align: right"]232.76[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.180643[/TD]
[TD="align: right"]0.613169[/TD]
[TD="align: right"]0.656859[/TD]
[TD="align: right"]0.995036[/TD]
[TD="align: right"][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.608630[/TD]
[TD="align: right"]0.724874[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]525.00[/TD]
[TD="align: right"]315.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.166184[/TD]
[TD="align: right"]0.579066[/TD]
[TD="align: right"]0.956614[/TD]
[TD="align: right"]0.972315[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-570.39[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.989515[/TD]
[TD="align: right"]0.289381[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]581.46[/TD]
[TD="align: right"]402.82[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.594594[/TD]
[TD="align: right"]0.480623[/TD]
[TD="align: right"]0.313821[/TD]
[TD="align: right"]0.966376[/TD]
[TD="align: right"][/TD]
[TD="align: right"]170.06[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.230310[/TD]
[TD="align: right"]0.839800[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]646.00[/TD]
[TD="align: right"]403.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.20255[/TD]
[TD="align: right"]0.345902[/TD]
[TD="align: right"]0.506189[/TD]
[TD="align: right"]0.145632[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-464.42[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.436437[/TD]
[TD="align: right"]0.703325[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]787.50[/TD]
[TD="align: right"]525.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.510139[/TD]
[TD="align: right"]0.786573[/TD]
[TD="align: right"]0.093826[/TD]
[TD="align: right"]0.197823[/TD]
[TD="align: right"][/TD]
[TD="align: right"]232.76[/TD]
[TD="align: right"]232.76[/TD]
[TD="align: right"]0.180643[/TD]
[TD="align: right"]0.613169[/TD]
[TD="align: right"]0.656859[/TD]
[TD="align: right"]0.995036[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]854.00[/TD]
[TD="align: right"]581.46[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.672223[/TD]
[TD="align: right"]0.55915[/TD]
[TD="align: right"]0.087175[/TD]
[TD="align: right"]0.240598[/TD]
[TD="align: right"][/TD]
[TD="align: right"]525[/TD]
[TD="align: right"]525[/TD]
[TD="align: right"]0.166184[/TD]
[TD="align: right"]0.579066[/TD]
[TD="align: right"]0.093826[/TD]
[TD="align: right"]0.197823[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]921.25[/TD]
[TD="align: right"]787.50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.49978[/TD]
[TD="align: right"]0.875971[/TD]
[TD="align: right"]0.505803[/TD]
[TD="align: right"]0.534559[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]315[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.956614[/TD]
[TD="align: right"]0.972315[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]1102.50[/TD]
[TD="align: right"]921.25[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.992174[/TD]
[TD="align: right"]0.045855[/TD]
[TD="align: right"]0.56331[/TD]
[TD="align: right"]0.581046[/TD]
[TD="align: right"][/TD]
[TD="align: right"]581.46[/TD]
[TD="align: right"]581.46[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]1102.50[/TD]
[TD="align: right"]1102.50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.671875[/TD]
[TD="align: right"]0.006319[/TD]
[TD="align: right"]0.941837[/TD]
[TD="align: right"]0.577278[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]402.82[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]1535.80[/TD]
[TD="align: right"]1102.50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.607139[/TD]
[TD="align: right"]0.416728[/TD]
[TD="align: right"]0.031742[/TD]
[TD="align: right"]0.18767[/TD]
[TD="align: right"][/TD]
[TD="align: right"]646[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]1890.00[/TD]
[TD="align: right"]1122.14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.146482[/TD]
[TD="align: right"]0.688489[/TD]
[TD="align: right"]0.740222[/TD]
[TD="align: right"]0.527684[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]403[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]1937.93[/TD]
[TD="align: right"]1281.17[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.089278[/TD]
[TD="align: right"]0.308212[/TD]
[TD="align: right"]0.805144[/TD]
[TD="align: right"]0.424913[/TD]
[TD="align: right"][/TD]
[TD="align: right"]787.5[/TD]
[TD="align: right"]787.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]1978.00[/TD]
[TD="align: right"]1316.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.856479[/TD]
[TD="align: right"]0.761812[/TD]
[TD="align: right"]0.443385[/TD]
[TD="align: right"]0.462617[/TD]
[TD="align: right"][/TD]
[TD="align: right"]854[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]2181.74[/TD]
[TD="align: right"]1516.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.392771[/TD]
[TD="align: right"]0.651484[/TD]
[TD="align: right"]0.657276[/TD]
[TD="align: right"]0.049818[/TD]
[TD="align: right"][/TD]
[TD="align: right"]921.25[/TD]
[TD="align: right"]921.25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]2597.17[/TD]
[TD="align: right"]1594.48[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.995058[/TD]
[TD="align: right"]0.65662[/TD]
[TD="align: right"]0.713947[/TD]
[TD="align: right"]0.966186[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1102.5[/TD]
[TD="align: right"]1102.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]2638.14[/TD]
[TD="align: right"]1600.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.350692[/TD]
[TD="align: right"]0.49981[/TD]
[TD="align: right"]0.776559[/TD]
[TD="align: right"]0.02403[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1102.5[/TD]
[TD="align: right"]1102.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]3101.55[/TD]
[TD="align: right"]1890.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.781371[/TD]
[TD="align: right"]0.816597[/TD]
[TD="align: right"]0.316359[/TD]
[TD="align: right"]0.581553[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1535.8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]3101.85[/TD]
[TD="align: right"]1937.93[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.99391[/TD]
[TD="align: right"]0.083133[/TD]
[TD="align: right"]0.194838[/TD]
[TD="align: right"]0.814776[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1890[/TD]
[TD="align: right"]1890[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]3101.85[/TD]
[TD="align: right"]1960.54[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.061758[/TD]
[TD="align: right"]0.044115[/TD]
[TD="align: right"]0.746524[/TD]
[TD="align: right"]0.025798[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1122.14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]3155.00[/TD]
[TD="align: right"]1978.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.08855[/TD]
[TD="align: right"]0.417551[/TD]
[TD="align: right"]0.301308[/TD]
[TD="align: right"]0.744783[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1937.93[/TD]
[TD="align: right"]1937.93[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]3330.50[/TD]
[TD="align: right"]2181.74[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.509971[/TD]
[TD="align: right"]0.465358[/TD]
[TD="align: right"]0.982045[/TD]
[TD="align: right"]0.585837[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1281.17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]3515.43[/TD]
[TD="align: right"]3101.55[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.833034[/TD]
[TD="align: right"]0.334891[/TD]
[TD="align: right"]0.990682[/TD]
[TD="align: right"]0.489945[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1978[/TD]
[TD="align: right"]1978[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]3879.00[/TD]
[TD="align: right"]3101.85[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.108474[/TD]
[TD="align: right"]0.507727[/TD]
[TD="align: right"]0.04918[/TD]
[TD="align: right"]0.796254[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1316[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]4122.00[/TD]
[TD="align: right"]3101.85[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.568063[/TD]
[TD="align: right"]0.704015[/TD]
[TD="align: right"]0.97679[/TD]
[TD="align: right"]0.147102[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2181.74[/TD]
[TD="align: right"]2181.74[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]4250.00[/TD]
[TD="align: right"]3128.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.192804[/TD]
[TD="align: right"]0.746141[/TD]
[TD="align: right"]0.417373[/TD]
[TD="align: right"]0.8646[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1516[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]4282.00[/TD]
[TD="align: right"]3155.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.445725[/TD]
[TD="align: right"]0.616985[/TD]
[TD="align: right"]0.624919[/TD]
[TD="align: right"]0.511568[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2597.17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]5136.00[/TD]
[TD="align: right"]3180.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.331798[/TD]
[TD="align: right"]0.220772[/TD]
[TD="align: right"]0.260407[/TD]
[TD="align: right"]0.340416[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1594.48[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]5600.00[/TD]
[TD="align: right"]3290.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.39058[/TD]
[TD="align: right"]0.200629[/TD]
[TD="align: right"]0.996093[/TD]
[TD="align: right"]0.104528[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2638.14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​
 
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Try this:-
Results start "I2"
Code: 
[COLOR="Navy"]Sub[/COLOR] MG13Nov16
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] G [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nCol [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] aCol [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp)).Resize(, 2)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]


ReDim Ray(1 To .Count, 1 To 6)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    c = c + 1
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] G [COLOR="Navy"]In[/COLOR] .Item(K)
      Ray(c, G.Column) = G
      nCol = IIf(G.Column = 1, 4, 5)
      aCol = IIf(G.Column = 1, 3, 5)
      Ray(c, aCol) = G(, nCol)
      Ray(c, aCol + 1) = G(, nCol + 1)
    [COLOR="Navy"]Next[/COLOR] G
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With
Range("I2").Resize(c, 6).Value = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
It works, but the matching part does not work on duplicates - it does not keep duplicates. Also I have column B longer than column A, and its not matching the the longer part at all. The original code from this thread worked for those two scenarios.
Besides for that, what needs to be changed in the code if I wanted to add more columns to the offset part?
 
Upvote 0
Try this for Duplicate problem :-
Please supply Example of extra colums and Expected results.
Code: 
[COLOR=Navy]Sub[/COLOR] MG14Nov32
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] K [COLOR=Navy]As[/COLOR] Variant, Temp [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer,[/COLOR] P [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] G [COLOR=Navy]As[/COLOR] Range, c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] nCol [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer,[/COLOR] aCol [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer,[/COLOR] Rw [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
Rw = ActiveSheet.Range("A2").CurrentRegion.Rows.Count
    [COLOR=Navy]Set[/COLOR] Rng = Range("A2").Resize(Rw, 2)
        [COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
        [COLOR=Navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
            .Add Dn.Value, Dn
        [COLOR=Navy]Else[/COLOR]
            [COLOR=Navy]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR=Navy]End[/COLOR] If
    
[COLOR=Navy]Next[/COLOR]


ReDim ray(1 To Rng.Count, 1 To 6)
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] K [COLOR=Navy]In[/COLOR] .keys
   [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] G [COLOR=Navy]In[/COLOR] .Item(K)
      c = c + 1
      [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] P [COLOR=Navy]In[/COLOR] G.Areas
            ray(c, P.Column) = P 
            nCol = IIf(P.Column = 1, 4, 5)
            aCol = IIf(P.Column = 1, 3, 5)
            ray(c, aCol) = P(, nCol)
            ray(c, aCol + 1) = P(, nCol + 1)
      [COLOR=Navy]Next[/COLOR] P
  [COLOR=Navy]Next[/COLOR] G
[COLOR=Navy]Next[/COLOR] K
[COLOR=Navy]End[/COLOR] With
Range("I2").Resize(c, 6).Value = ray
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
 
Upvote 0
Both problems seem to be fixed, but now in the results, the matches are not aligned, like this:

Excel 2010
IJKLMN

<tbody>
[TD="align: center"]80[/TD]
[TD="align: right"]6750.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.685558[/TD]
[TD="align: right"]0.899959[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]81[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6750.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.293358[/TD]
[TD="align: right"]0.71212[/TD]

[TD="align: center"]82[/TD]
[TD="align: right"]6870.78[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.037762[/TD]
[TD="align: right"]0.769452[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]83[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6870.78[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.009154[/TD]
[TD="align: right"]0.911406[/TD]

[TD="align: center"]84[/TD]
[TD="align: right"]6930.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.081241[/TD]
[TD="align: right"]0.115466[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]85[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6930.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.741219[/TD]
[TD="align: right"]0.081336[/TD]

[TD="align: center"]86[/TD]
[TD="align: right"]7560.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.940537[/TD]
[TD="align: right"]0.448011[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]87[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7560.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.489304[/TD]
[TD="align: right"]0.0708[/TD]

[TD="align: center"]88[/TD]
[TD="align: right"]7679.86[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.65625[/TD]
[TD="align: right"]0.91493[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]89[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7679.86[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.572393[/TD]
[TD="align: right"]0.211133[/TD]

[TD="align: center"]90[/TD]
[TD="align: right"]7800.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.463792[/TD]
[TD="align: right"]0.374015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]91[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7800.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.479078[/TD]
[TD="align: right"]0.708827[/TD]

[TD="align: center"]92[/TD]
[TD="align: right"]7818.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.797551[/TD]
[TD="align: right"]0.811147[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]93[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7818.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.488477[/TD]
[TD="align: right"]0.55011[/TD]

[TD="align: center"]94[/TD]
[TD="align: right"]9200.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.168261[/TD]
[TD="align: right"]0.318786[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]95[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9200.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.575405[/TD]
[TD="align: right"]0.805239[/TD]

</tbody>
 
Upvote 0
You need to show an example before and after ,with expected result and actual result, so I can see what the problem is !!!!!
 
Upvote 0
The way the results showed before (and is the expected result), was with the matches aligned in col I and J like this:
[TABLE="width: 422"]
<tbody>[TR]
[TD]I[/TD]
[TD="align: right"]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD="align: right"]M[/TD]
[TD="align: right"]N[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1122.14[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.740222[/TD]
[TD="align: right"]0.527684[/TD]
[/TR]
[TR]
[TD="align: right"]1937.93[/TD]
[TD="align: right"]1937.93[/TD]
[TD="align: right"]0.089278[/TD]
[TD="align: right"]0.308212[/TD]
[TD="align: right"]0.194838[/TD]
[TD="align: right"]0.814776[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1281.17[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.805144[/TD]
[TD="align: right"]0.424913[/TD]
[/TR]
[TR]
[TD="align: right"]1978.00[/TD]
[TD="align: right"]1978.00[/TD]
[TD="align: right"]0.856479[/TD]
[TD="align: right"]0.761812[/TD]
[TD="align: right"]0.301308[/TD]
[TD="align: right"]0.744783[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1316.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.443385[/TD]
[TD="align: right"]0.462617[/TD]
[/TR]
[TR]
[TD="align: right"]2181.74[/TD]
[TD="align: right"]2181.74[/TD]
[TD="align: right"]0.392771[/TD]
[TD="align: right"]0.651484[/TD]
[TD="align: right"]0.982045[/TD]
[TD="align: right"]0.585837[/TD]
[/TR]
</tbody>[/TABLE]

The way the matches in the results are showing now, are jagged like this:
[TABLE="width: 422"]
<tbody>[TR]
[TD]I[/TD]
[TD="align: right"]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD="align: right"]M[/TD]
[TD="align: right"]N[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1122.14[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.740222[/TD]
[TD="align: right"]0.527684[/TD]
[/TR]
[TR]
[TD="align: right"]1937.93[/TD]
[TD][/TD]
[TD="align: right"]0.089278[/TD]
[TD="align: right"]0.308212[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1937.93[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.194838[/TD]
[TD="align: right"]0.814776[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1281.17[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.805144[/TD]
[TD="align: right"]0.424913[/TD]
[/TR]
[TR]
[TD="align: right"]1978.00[/TD]
[TD][/TD]
[TD="align: right"]0.856479[/TD]
[TD="align: right"]0.761812[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1978.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.301308[/TD]
[TD="align: right"]0.744783[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1316.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.443385[/TD]
[TD="align: right"]0.462617[/TD]
[/TR]
[TR]
[TD="align: right"]2181.74[/TD]
[TD][/TD]
[TD="align: right"]0.392771[/TD]
[TD="align: right"]0.651484[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2181.74[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.982045[/TD]
[TD="align: right"]0.585837[/TD]
[/TR]
</tbody>[/TABLE]

Hope that's clear enough
 
Upvote 0
Try moving the variable "c", between "K" and "G" rather that "G" & "P":- see below
Code: 
For Each K In .keys
[B][COLOR=#ff0000]   c = c + 1[/COLOR][/B]
   For Each G In .Item(K)
      For Each P In G.Areas
 
Upvote 0
Okay, did that, but now its not keeping duplicates, like in this example there is only one match for 1102.50:
[TABLE="width: 1005"]
<tbody>[TR]
[TD="align: right"]A[/TD]
[TD="align: right"]B[/TD]
[TD]C[/TD]
[TD="align: right"]D[/TD]
[TD="align: right"]E[/TD]
[TD="align: right"]F[/TD]
[TD="align: right"]G[/TD]
[TD]H[/TD]
[TD="align: right"]I[/TD]
[TD="align: right"]J[/TD]
[TD="align: right"]K[/TD]
[TD="align: right"]L[/TD]
[TD="align: right"]M[/TD]
[TD="align: right"]N[/TD]
[/TR]
[TR]
[TD="align: right"]1102.50[/TD]
[TD="align: right"]581.46[/TD]
[TD][/TD]
[TD="align: right"]0.672223[/TD]
[TD="align: right"]0.55915[/TD]
[TD="align: right"]0.087175[/TD]
[TD="align: right"]0.240598105[/TD]
[TD][/TD]
[TD="align: right"]525.00[/TD]
[TD="align: right"]525.00[/TD]
[TD="align: right"]0.166184[/TD]
[TD="align: right"]0.579066[/TD]
[TD="align: right"]0.093826[/TD]
[TD="align: right"]0.197823[/TD]
[/TR]
[TR]
[TD="align: right"]921.25[/TD]
[TD="align: right"]787.50[/TD]
[TD][/TD]
[TD="align: right"]0.49978[/TD]
[TD="align: right"]0.875971[/TD]
[TD="align: right"]0.505803[/TD]
[TD="align: right"]0.534559497[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]315.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.956614[/TD]
[TD="align: right"]0.972315[/TD]
[/TR]
[TR]
[TD="align: right"]1102.50[/TD]
[TD="align: right"]921.25[/TD]
[TD][/TD]
[TD="align: right"]0.992174[/TD]
[TD="align: right"]0.045855[/TD]
[TD="align: right"]0.56331[/TD]
[TD="align: right"]0.58104563[/TD]
[TD][/TD]
[TD="align: right"]581.46[/TD]
[TD="align: right"]581.46[/TD]
[TD="align: right"]0.594594[/TD]
[TD="align: right"]0.480623[/TD]
[TD="align: right"]0.087175[/TD]
[TD="align: right"]0.240598[/TD]
[/TR]
[TR]
[TD="align: right"]854.00[/TD]
[TD="align: right"]1102.50[/TD]
[TD][/TD]
[TD="align: right"]0.671875[/TD]
[TD="align: right"]0.006319[/TD]
[TD="align: right"]0.941837[/TD]
[TD="align: right"]0.577277632[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]402.82[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.313821[/TD]
[TD="align: right"]0.966376[/TD]
[/TR]
[TR]
[TD="align: right"]1535.80[/TD]
[TD="align: right"]1102.50[/TD]
[TD][/TD]
[TD="align: right"]0.607139[/TD]
[TD="align: right"]0.416728[/TD]
[TD="align: right"]0.031742[/TD]
[TD="align: right"]0.18767006[/TD]
[TD][/TD]
[TD="align: right"]646.00[/TD]
[TD][/TD]
[TD="align: right"]0.20255[/TD]
[TD="align: right"]0.345902[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1890.00[/TD]
[TD="align: right"]1122.14[/TD]
[TD][/TD]
[TD="align: right"]0.146482[/TD]
[TD="align: right"]0.688489[/TD]
[TD="align: right"]0.740222[/TD]
[TD="align: right"]0.527683586[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]403.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.506189[/TD]
[TD="align: right"]0.145632[/TD]
[/TR]
[TR]
[TD="align: right"]1937.93[/TD]
[TD="align: right"]1281.17[/TD]
[TD][/TD]
[TD="align: right"]0.089278[/TD]
[TD="align: right"]0.308212[/TD]
[TD="align: right"]0.805144[/TD]
[TD="align: right"]0.424913164[/TD]
[TD][/TD]
[TD="align: right"]787.50[/TD]
[TD="align: right"]787.50[/TD]
[TD="align: right"]0.510139[/TD]
[TD="align: right"]0.786573[/TD]
[TD="align: right"]0.505803[/TD]
[TD="align: right"]0.534559[/TD]
[/TR]
[TR]
[TD="align: right"]1978.00[/TD]
[TD="align: right"]1316.00[/TD]
[TD][/TD]
[TD="align: right"]0.856479[/TD]
[TD="align: right"]0.761812[/TD]
[TD="align: right"]0.443385[/TD]
[TD="align: right"]0.462617014[/TD]
[TD][/TD]
[TD="align: right"]1102.50[/TD]
[TD="align: right"]1102.50[/TD]
[TD="align: right"]0.992174[/TD]
[TD="align: right"]0.045855[/TD]
[TD="align: right"]0.031742[/TD]
[TD="align: right"]0.18767[/TD]
[/TR]
</tbody>[/TABLE]

The expected result is to compare all values in col A & B, including duplicates.
 
Last edited:
Upvote 0
Try this:-
Code: 
[COLOR=Navy]Sub[/COLOR] MG17Nov50
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] K [COLOR=Navy]As[/COLOR] Variant, temp [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] P [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] G [COLOR=Navy]As[/COLOR] Range, c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] nCol [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer,[/COLOR] aCol [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer,[/COLOR] Rw [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] col [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer[/COLOR]
Rw = ActiveSheet.Range("A2").CurrentRegion.Rows.Count
    [COLOR=Navy]Set[/COLOR] Rng = Range("A2").Resize(Rw, 2)
        [COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
        [COLOR=Navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
            .Add Dn.Value, Dn
        [COLOR=Navy]Else[/COLOR]
            [COLOR=Navy]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR=Navy]End[/COLOR] If
    
[COLOR=Navy]Next[/COLOR]
c = 1
ReDim ray(1 To Rng.Count, 1 To 6)
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] K [COLOR=Navy]In[/COLOR] .keys
   temp = c
   [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] G [COLOR=Navy]In[/COLOR] .Item(K)
       c = IIf(Not G.Column = col, temp, c)
       ray(c, G.Column) = G 
            nCol = IIf(G.Column = 1, 4, 5)
            aCol = IIf(G.Column = 1, 3, 5)
            ray(c, aCol) = G(, nCol)
            ray(c, aCol + 1) = G(, nCol + 1)
            col = G.Column
            c = c + 1
  [COLOR=Navy]Next[/COLOR] G
[COLOR=Navy]Next[/COLOR] K
[COLOR=Navy]End[/COLOR] With
Range("I2").Resize(c, 6).Value = ray
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
C
  • Question Question
Formula to find Max number of items in orders
Replies
1
Views
691
fjns
F

Forum statistics

Threads
1,224,586
Messages
6,179,719
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top