Excel VBA Compare two array and create result array

[Perter Parker]

[FONT=Arial, Helvetica Neue, Helvetica, sans-serif]Hi everyone[/FONT]
[FONT=Arial, Helvetica Neue, Helvetica, sans-serif]I have two array (each array have three column , have about 18.000 row). I want to compare array1, array2 , then creat a result array. For more detail, Pleae go to my link for xlsb [/FONT]http://www.mediafire.com/file/r38m45t2s93wy5y/compare_array-.xlsb/file


Image of my array.
And here is one of extracting of my file

Array 1	     Array 1	   Array 1     Array 2   	Array 2 	Array 2Code 1	      INV 1	      Q1	  Code 2	    INV 2	Q2
2000000001206	NK 1801/001	40	2000000001206	NK 1801/001	40
8935001878711	NK 1801/001	40	8935001878711	NK 1801/001	40
8935001882381	NK 1801/001	20	8936120920343	NK 1801/001	10
9786040108203	NK 1801/001	5	9786040108203	NK 1801/001	5
9786049448577	NK 1801/002	3       9786049448577	NK 1801/003	3
9786049448607	NK 1801/002	3 	9786049448607	NK 1801/003	3
9786049448812	NK 1801/002	3 	9786049448812	NK 1801/003	3
2000000001429	NK 1801/004	5 	2000000001429	NK 1801/004	5
8934994208161	NK 1801/004	5 	8934994208161	NK 1801/004	5
6691188352292	NK 1801/005	12	2000000001429	NK 1801/004	5
2000000001206	NK 1802/006	1 	8934994208161	NK 1801/004	5
2000000001278	NK 1802/006	2 	2000000002268	NK 1801/005	1
2000000001485	NK 1802/006	3 	6691188352162	NK 1801/005	4
8936120920343	NK 1803/007	3 	6691188352216	NK 1801/005	2
8936120920350	NK 1803/007	5 	6691188352292	NK 1801/005	12
2000000002454	NK 1803/007	5 	2000000001206	NK 1802/006	1
2000000002455	NK 1803/008	5 	2000000001276	NK 1802/006	20
2000000002456	NK 1803/008	5 	2000000001485	NK 1802/006	5
9786040021854	NK 1903/009	3 	8936120920336	NK 1803/007	5
9786040021854	NK 1903/009	3 	8936120920343	NK 1803/007	5
9786040108203	NK 1903/009	5 	2000000002454	NK 1803/007	5
9786040108203	NK 1903/009	5 	2000000002455	NK 1803/008	1
9786040108234	NK 1903/009	5 	9786040021854	NK 1903/009	3
9786040108234	NK 1903/009	5 	9786040108203	NK 1903/009	5
2000000000987	NK 1801/010	42	9786040108234	NK 1903/009	5
2000000000988	NK 1801/010	42	2000000000987	NK 1801/010	42
2000000001527	NK 1801/010	30	2000000000988	NK 1801/010	42
2000000001990	NK 1801/010	1 	2000000001527	NK 1801/010	30
2000000001991	NK 1801/010	1 	2000000001990	NK 1801/010	1
...................more and more under...................
---------------------and here is my result array--------------
Result Array	Result Array	Result Array	Result Array
Code 1	      INV 1	      Q1	  Code 2	      INV 2	    Q2
2000000001206	NK 1801/001	40	2000000001206	NK 1801/001	40
8935001878711	NK 1801/001	40	8935001878711	NK 1801/001	40
8935001882381	NK 1801/001	20			
			                8936120920343	NK 1801/001	10
9786040108203	NK 1801/001	5  	9786040108203	NK 1801/001	5
9786049448577	NK 1801/002	3	9786049448577	NK 1801/003	3
9786049448607	NK 1801/002	3	9786049448607	NK 1801/003	3
9786049448812	NK 1801/002	3	9786049448812	NK 1801/003	3
2000000001429	NK 1801/004	5	2000000001429	NK 1801/004	5
8934994208161	NK 1801/004	5 	8934994208161	NK 1801/004	5
			                2000000001429	NK 1801/004	5
			                8934994208161	NK 1801/004	5
			                2000000002268	NK 1801/005	1
			                6691188352162	NK 1801/005	4
			                6691188352216	NK 1801/005	2
6691188352292	NK 1801/005	12	6691188352292	NK 1801/005	12
2000000001206	NK 1802/006	1	2000000001206	NK 1802/006	1
2000000001278	NK 1802/006	2			
			                2000000001276	NK 1802/006	20
2000000001485	NK 1802/006	3	2000000001485	NK 1802/006	5
			                8936120920336	NK 1803/007	5
8936120920343	NK 1803/007	3       8936120920343	NK 1803/007	5
8936120920350	NK 1803/007	5			
2000000002454	NK 1803/007	5       2000000002454	NK 1803/007	5
2000000002455	NK 1803/008	5	2000000002455	NK 1803/008	1
2000000002456	NK 1803/008	5			
9786040021854	NK 1903/009	3	9786040021854	NK 1903/009	3
9786040021854	NK 1903/009	3			
9786040108203	NK 1903/009	5 	9786040108203	NK 1903/009	5
9786040108203	NK 1903/009	5			
9786040108234	NK 1903/009	5	9786040108234	NK 1903/009	5
9786040108234	NK 1903/009	5			
2000000000987	NK 1801/010	42	2000000000987	NK 1801/010	42
2000000000988	NK 1801/010	42	2000000000988	NK 1801/010	42
2000000001527	NK 1801/010	30	2000000001527	NK 1801/010	30
2000000001990	NK 1801/010	1	2000000001990	NK 1801/010	1
2000000001991	NK 1801/010	1 	2000000001991	NK 1801/010	1 ........................ more and more under...............................

****** id="cke_pastebin" style="position: absolute; top: 8px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">Image of my array.
And here is one of extracting of my file
</body>

 

[alansidman]

Using Power Query, I was able to run a merge of the two tables. Results are at https://app.box.com/s/tk9smsqobdzfm1rzez4nlg9r0x64jbq0

 

[Perter Parker]

Using Power Query, I was able to run a merge of the two tables. Results are at https://app.box.com/s/tk9smsqobdzfm1rzez4nlg9r0x64jbq0

The result of Power Query make something sort of column A and D
The wanted result array consists of blank lines but retains the original order

Thank for your help