Excel VBA Compare two array and create result array

**Peter Pak** · 05-23-2019, 11:15 PM

Hi everyone
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.
And here is my image in file
Compare_array.png
And here is one of extracting of my array and result array

PHP Code:


Array 1         Array 1      Array 1       Array 2       Array 2     Array 2 
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 
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...............................

**kersplash** · 05-23-2019, 11:18 PM

Are the values in Columns A & D unique? If so then just use a VLOOKUP.

**kersplash** · 05-23-2019, 11:19 PM

Attach a workbook with your sample.

Go Advanced -> Manage Attachments -> Upload

**Peter Pak** · 05-23-2019, 11:22 PM

Values in Column A& D does not unique. And sheets have about 18.000 rows. I thinks VLOOKUP is's not suitable for my table

**Peter Pak** · 05-23-2019, 11:26 PM

I have upload file. Please view

**jindon** · 05-24-2019, 12:43 AM

See if this is how you wanted.

Please Login or Register  to view this content.

**Peter Pak** · 05-24-2019, 12:59 AM

Originally Posted by jindon

See if this is how you wanted.

Please Login or Register  to view this content.

The result of your code make something sort of column A and D
The wanted result array consists of blank lines but retains the original order
(please view that pic to see problem)

Thank for your help
Attachment 625731

**JeteMc** · 05-29-2019, 10:34 AM

Hello Peter Pak and Welcome to Excel Forum.
Selecting the attachment in post #7 only results in the following message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator"
It is better to attach an Excel file as we can't work with images.
I am confused about the result array in rows 13:17. The following worked up to that point:
1. Two helper columns (Q:R) are populated using =IF(R2="","",SUMPRODUCT(--(Q$1:Q2<>""))+1) and =IF(Q3="",Q2,IF(INDEX(A$3:A3,Q3-2)<>INDEX(D$3:D3,Q3-2),"",Q3)) respectively.
Note that columns Q:R may be moved and/or hidden for aesthetic purposes.
2. My version of the result array is displayed in columns S:X
3. Columns S:U are populated using: =IF($Q3="","",INDEX(A:A,$Q3))
4. Columns V:X are populated using: =IF($R3="","",INDEX(D:D,$R3))
Let us know if you have any questions.

Excel VBA Compare two array and create result array

LinkBack

Thread Tools

Rate This Thread

Display

Excel VBA Compare two array and create result array

Re: Excel VBA Compare two array and create result array

Re: Excel VBA Compare two array and create result array

Re: Excel VBA Compare two array and create result array

Re: Excel VBA Compare two array and create result array

Re: Excel VBA Compare two array and create result array

Re: Excel VBA Compare two array and create result array

Re: Excel VBA Compare two array and create result array

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Compare two string array and list what is not present in array 2

Compare two string array and list what is not present in array 2

Create an array for matches of a source array to any values in a match array.

[SOLVED] Help with VB code to Compare if values in one array exists in another array

[SOLVED] how to create an array of sheets and then compare them to a variable

Help with an array Formula to Compare, Sum, then Multiply result by a Constant

find each of the items in an array and save result in another array

Bookmarks

Bookmarks

Posting Permissions