Compare 2 columns in one worksheet to another

[Kinez101]

I have a file with over 100K rows and was wondering if there is a quick way to compare columns C and D in Previous Month to Current Month and vice-versa then output differences to Differences worksheet.

 

[alansidman]

Can you provide a sample of your data. There is probably a solution, but we have not enough data to understand what is happening.

 

[Kinez101]

I was trying to attach file, but I don't see an option for it. Below is just copy/paste.

[TABLE="width: 2643"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]TAG[/TD]
[TD]MODIFICATION FLAG[/TD]
[TD]BIC CODE[/TD]
[TD]BRANCH CODE[/TD]
[TD]INSTITUTION NAME[/TD]
[TD]BRANCH INFORMATION[/TD]
[TD]CITY HEADING[/TD]
[TD]SUBTYPE INDICATION[/TD]
[TD]VALUE ADDED SERVICES[/TD]
[TD]EXTRA INFO[/TD]
[TD]PHYSICAL ADDRESS 1[/TD]
[TD]PHYSICAL ADDRESS 2[/TD]
[TD]PHYSICAL ADDRESS 3[/TD]
[TD]PHYSICAL ADDRESS 4[/TD]
[TD]LOCATION[/TD]
[TD]COUNTRY NAME[/TD]
[TD]POB NUMBER[/TD]
[TD]POB LOCATION[/TD]
[TD]POB COUNTRY NAME[/TD]
[/TR]
[TR]
[TD]FI[/TD]
[TD]U[/TD]
[TD]AAAARSBG[/TD]
[TD]XXX[/TD]
[TD]TELENOR BANKA AD[/TD]
[TD][/TD]
[TD]BEOGRAD[/TD]
[TD]SUPE[/TD]
[TD]FIN[/TD]
[TD] CB[/TD]
[TD]OMLADINSKIH BRIGADA 90V[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11070 BEOGRAD[/TD]
[TD]SERBIA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FI[/TD]
[TD]U[/TD]
[TD]AAACKWKW[/TD]
[TD]XXX[/TD]
[TD]AL MUZAINI EXCHANGE CO. KSCC[/TD]
[TD][/TD]
[TD]KUWAIT[/TD]
[TD]SUPE[/TD]
[TD]FIN[/TD]
[TD] CK[/TD]
[TD]BUILDING 9[/TD]
[TD]BLOCK 4[/TD]
[TD]SAUD BIN ABDULAZIZ ST.[/TD]
[TD][/TD]
[TD]13022 KUWAIT[/TD]
[TD]KUWAIT[/TD]
[TD]POB 2156[/TD]
[TD]13022 KUWAIT[/TD]
[TD]KUWAIT[/TD]
[/TR]
[TR]
[TD]FI[/TD]
[TD]U[/TD]
[TD]AAADFRP1[/TD]
[TD]XXX[/TD]
[TD]ASSET ALLOCATION ADVISORS SA[/TD]
[TD][/TD]
[TD]PARIS[/TD]
[TD]NSWB[/TD]
[TD][/TD]
[TD] NP[/TD]
[TD]3 AVENUE HOCHE[/TD]
[TD]CHEZ NSM[/TD]
[TD]CHEZ NSM[/TD]
[TD][/TD]
[TD]75008 PARIS[/TD]
[TD]FRANCE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FI[/TD]
[TD]U[/TD]
[TD]AAAGFRP1[/TD]
[TD]XXX[/TD]
[TD]ASSOCIATION ADMINISTRATIVE AGRR[/TD]
[TD][/TD]
[TD]PARIS[/TD]
[TD]NSWB[/TD]
[TD][/TD]
[TD] NP[/TD]
[TD]10 RUE DU COLISEE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]75008 PARIS[/TD]
[TD]FRANCE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FI[/TD]
[TD]U[/TD]
[TD]AAAJBG21[/TD]
[TD]XXX[/TD]
[TD]ARCUS ASSET MANAGEMENT JSC[/TD]
[TD][/TD]
[TD]PLOVDIV[/TD]
[TD]NSWB[/TD]
[TD][/TD]
[TD] N2[/TD]
[TD]BUSINESS CENTER LEGIS[/TD]
[TD]6TH OF SEPTEMBER BLVD. 152[/TD]
[TD][/TD]
[TD][/TD]
[TD]4000 PLOVDIV[/TD]
[TD]BULGARIA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FI[/TD]
[TD]U[/TD]
[TD]AAALSARI[/TD]
[TD]ALK[/TD]
[TD]ALAWWAL BANK[/TD]
[TD](EASTERN AREA ALKHOBAR)[/TD]
[TD]ALKHOBAR[/TD]
[TD]SUPE[/TD]
[TD]FINTG+[/TD]
[TD]BRA CR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ALKHOBAR[/TD]
[TD]SAUDI ARABIA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FI[/TD]
[TD]U[/TD]
[TD]AAALSARI[/TD]
[TD]CTD[/TD]
[TD]ALAWWAL BANK[/TD]
[TD](CENTRAL TREASURY DEPT.)[/TD]
[TD]RIYADH[/TD]
[TD]SUPE[/TD]
[TD]FINTG+[/TD]
[TD]TMK CR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]RIYADH[/TD]
[TD]SAUDI ARABIA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FI[/TD]
[TD]U[/TD]
[TD]AAALSARI[/TD]
[TD]JED[/TD]
[TD]ALAWWAL BANK[/TD]
[TD](WESTERN AREA JEDDAH)[/TD]
[TD]JEDDAH[/TD]
[TD]SUPE[/TD]
[TD]FINTG+[/TD]
[TD]BRA CR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]JEDDAH[/TD]
[TD]SAUDI ARABIA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FI[/TD]
[TD]U[/TD]
[TD]AAALSARI[/TD]
[TD]RYD[/TD]
[TD]ALAWWAL BANK[/TD]
[TD](CENTRAL AREA RIYADH)[/TD]
[TD]RIYADH[/TD]
[TD]SUPE[/TD]
[TD]FINTG+[/TD]
[TD]BRA CR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]RIYADH[/TD]
[TD]SAUDI ARABIA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FI[/TD]
[TD]U[/TD]
[TD]AAALSARI[/TD]
[TD]XXX[/TD]
[TD]ALAWWAL BANK[/TD]
[TD][/TD]
[TD]RIYADH[/TD]
[TD]SUPE[/TD]
[TD]FINTG+[/TD]
[TD] CR[/TD]
[TD]HEAD OFFICE BUILDING[/TD]
[TD]DHABAB STREET[/TD]
[TD][/TD]
[TD][/TD]
[TD]RIYADH 11431[/TD]
[TD]SAUDI ARABIA[/TD]
[TD]POB 1467[/TD]
[TD]RIYADH 11431[/TD]
[TD]SAUDI ARABIA[/TD]
[/TR]
[TR]
[TD]FI[/TD]
[TD]U[/TD]
[TD]AAAMFRP1[/TD]
[TD]XXX[/TD]
[TD]ALLIANZ ALTERNATIVE ASSET MANAGEMENT SA[/TD]
[TD][/TD]
[TD]PARIS[/TD]
[TD]NSWB[/TD]
[TD][/TD]
[TD] NP[/TD]
[TD]20 RUE LE PELETIER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]75009 PARIS[/TD]
[TD]FRANCE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FI[/TD]
[TD]U[/TD]
[TD]AAAOFRP1[/TD]
[TD]XXX[/TD]
[TD]ACA ASSET MANAGEMENT COMPANY AND ASSOCIES[/TD]
[TD][/TD]
[TD]PARIS[/TD]
[TD]NSWB[/TD]
[TD][/TD]
[TD] NP[/TD]
[TD]241 BOULEVARD ST GERMAIN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]75007 PARIS[/TD]
[TD]FRANCE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FI[/TD]
[TD]U[/TD]
[TD]AAAPBGS1[/TD]
[TD]XXX[/TD]
[TD]ALARIC CAPITAL[/TD]
[TD][/TD]
[TD]SOFIA[/TD]
[TD]NSWB[/TD]
[TD][/TD]
[TD] NS[/TD]
[TD]EXARCH YOSIF 7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1301 SOFIA[/TD]
[TD]BULGARIA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FI[/TD]
[TD]U[/TD]
[TD]AAASTHB1[/TD]
[TD]XXX[/TD]
[TD]ASIA PLUS SECURITIES PUBLIC COMPANY LIMITED[/TD]
[TD][/TD]
[TD]BANGKOK[/TD]
[TD]NSWB[/TD]
[TD][/TD]
[TD] NB[/TD]
[TD]SATHORN CITY TOWER[/TD]
[TD]FLOOR 1-3[/TD]
[TD]175 SOUTH SATHORN ROAD[/TD]
[TD][/TD]
[TD]BANGKOK 10120[/TD]
[TD]THAILAND[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FI[/TD]
[TD]U[/TD]
[TD]AABAFI22[/TD]
[TD]TMS[/TD]
[TD]BANK OF ALAND PLC[/TD]
[TD](TREASURY DEPARTMENT)[/TD]
[TD]MARIEHAMN[/TD]
[TD]SUPE[/TD]
[TD]ERPFIN[/TD]
[TD]TMK C2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MARIEHAMN[/TD]
[TD]FINLAND[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FI[/TD]
[TD]U[/TD]
[TD]AABAFI22[/TD]
[TD]XXX[/TD]
[TD]BANK OF ALAND PLC[/TD]
[TD][/TD]
[TD]MARIEHAMN[/TD]
[TD]SUPE[/TD]
[TD]ERPFINTGT[/TD]
[TD] C2[/TD]
[TD]NYGATAN 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22100 MARIEHAMN[/TD]
[TD]FINLAND[/TD]
[TD]POB 3[/TD]
[TD]22101 MARIEHAMN[/TD]
[TD]FINLAND[/TD]
[/TR]
[TR]
[TD]FI[/TD]
[TD]U[/TD]
[TD]AABASESS[/TD]
[TD]TMS[/TD]
[TD]ALANDSBANKEN ABP (FINLAND),SVENSK FILIAL[/TD]
[TD](TREASURY DEPARTMENT)[/TD]
[TD]STOCKHOLM[/TD]
[TD]SUPE[/TD]
[TD]FIN[/TD]
[TD]ALL CS[/TD]
[TD]STUREPLAN 19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]107 81 STOCKHOLM[/TD]
[TD]SWEDEN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FI[/TD]
[TD]U[/TD]
[TD]AABASESS[/TD]
[TD]XXX[/TD]
[TD]ALANDSBANKEN ABP (FINLAND),SVENSK FILIAL[/TD]
[TD][/TD]
[TD]STOCKHOLM[/TD]
[TD]SUPE[/TD]
[TD]FINSSK[/TD]
[TD] CS[/TD]
[TD]STUREPLAN 19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]107 81 STOCKHOLM[/TD]
[TD]SWEDEN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FI[/TD]
[TD]U[/TD]
[TD]AABMIT22[/TD]
[TD]XXX[/TD]
[TD]AFV ACCIAIERIE BELTRAME S.P.A.[/TD]
[TD][/TD]
[TD]VICENZA[/TD]
[TD]CORP[/TD]
[TD]FIN[/TD]
[TD] C2[/TD]
[TD]VIALE DELLA SCIENZA 81[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]36100 VICENZA[/TD]
[TD]ITALY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[alansidman]

Now that you have provided some sample data, which columns are you trying to compare since you did not label any with column letters? And I don't see any that appear to match.

 

[Kinez101]

They would be column C&D, BIC and Branch Code.

 

[alansidman]

What are they being compared to. I see only one set of BIC and Branch Code. Show us what your expected solution would be if you did this manually for the sample you have supplied.

 

[Kinez101]

It’s 2 worksheets, Previous Month and Current month, and it would compare column C&D between sheets and output differences to third worksheet called Variance.

BIC and Branch Codes aren’t going to be row for row. I think columns C&D should be concat to make it faster to go through 100K+ rows.

 

[alansidman]

Will a VBA or Power Query solution work for you. If so, it may take me awhile to produce same. I don't have a formula solution for you.

 

[Kinez101]

Unfortunately I don’t have the luxury of Power Query so only VBA in this case.

 

[alansidman]

Will a Power Query solution work for you. I don't have a formula solution for you. This is a fairly easy issue to resolve using Power Query.

1. Bring each table into PQ
2. Create a Full Outer Join Merge of the two tables. Join on the BIC and Branch.
3. Filter all rows except those holding a null value in the second merged table.
4. Close and Load to Excel Native

If you are not familiar with PQ, then look at my signature block. Also, look at obtaining the book. M is (Data) Monkey. This is very powerful addin to Excel.