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.

TAG

MODIFICATION FLAG

BIC CODE

BRANCH CODE

INSTITUTION NAME

BRANCH INFORMATION

CITY HEADING

SUBTYPE INDICATION

VALUE ADDED SERVICES

EXTRA INFO

PHYSICAL ADDRESS 1

PHYSICAL ADDRESS 2

PHYSICAL ADDRESS 3

PHYSICAL ADDRESS 4

LOCATION

COUNTRY NAME

POB NUMBER

POB LOCATION

POB COUNTRY NAME

FI

U

AAAARSBG

XXX

TELENOR BANKA AD

BEOGRAD

SUPE

FIN

CB

OMLADINSKIH BRIGADA 90V

11070 BEOGRAD

SERBIA

FI

U

AAACKWKW

XXX

AL MUZAINI EXCHANGE CO. KSCC

KUWAIT

SUPE

FIN

CK

BUILDING 9

BLOCK 4

SAUD BIN ABDULAZIZ ST.

13022 KUWAIT

KUWAIT

POB 2156

13022 KUWAIT

KUWAIT

FI

U

AAADFRP1

XXX

ASSET ALLOCATION ADVISORS SA

PARIS

NSWB

NP

3 AVENUE HOCHE

CHEZ NSM

CHEZ NSM

75008 PARIS

FRANCE

FI

U

AAAGFRP1

XXX

ASSOCIATION ADMINISTRATIVE AGRR

PARIS

NSWB

NP

10 RUE DU COLISEE

75008 PARIS

FRANCE

FI

U

AAAJBG21

XXX

ARCUS ASSET MANAGEMENT JSC

PLOVDIV

NSWB

N2

BUSINESS CENTER LEGIS

6TH OF SEPTEMBER BLVD. 152

4000 PLOVDIV

BULGARIA

FI

U

AAALSARI

ALK

ALAWWAL BANK

(EASTERN AREA ALKHOBAR)

ALKHOBAR

SUPE

FINTG+

BRA CR

ALKHOBAR

SAUDI ARABIA

FI

U

AAALSARI

CTD

ALAWWAL BANK

(CENTRAL TREASURY DEPT.)

RIYADH

SUPE

FINTG+

TMK CR

RIYADH

SAUDI ARABIA

FI

U

AAALSARI

JED

ALAWWAL BANK

(WESTERN AREA JEDDAH)

JEDDAH

SUPE

FINTG+

BRA CR

JEDDAH

SAUDI ARABIA

FI

U

AAALSARI

RYD

ALAWWAL BANK

(CENTRAL AREA RIYADH)

RIYADH

SUPE

FINTG+

BRA CR

RIYADH

SAUDI ARABIA

FI

U

AAALSARI

XXX

ALAWWAL BANK

RIYADH

SUPE

FINTG+

CR

HEAD OFFICE BUILDING

DHABAB STREET

RIYADH 11431

SAUDI ARABIA

POB 1467

RIYADH 11431

SAUDI ARABIA

FI

U

AAAMFRP1

XXX

ALLIANZ ALTERNATIVE ASSET MANAGEMENT SA

PARIS

NSWB

NP

20 RUE LE PELETIER

75009 PARIS

FRANCE

FI

U

AAAOFRP1

XXX

ACA ASSET MANAGEMENT COMPANY AND ASSOCIES

PARIS

NSWB

NP

241 BOULEVARD ST GERMAIN

75007 PARIS

FRANCE

FI

U

AAAPBGS1

XXX

ALARIC CAPITAL

SOFIA

NSWB

NS

EXARCH YOSIF 7

1301 SOFIA

BULGARIA

FI

U

AAASTHB1

XXX

ASIA PLUS SECURITIES PUBLIC COMPANY LIMITED

BANGKOK

NSWB

NB

SATHORN CITY TOWER

FLOOR 1-3

175 SOUTH SATHORN ROAD

BANGKOK 10120

THAILAND

FI

U

AABAFI22

TMS

BANK OF ALAND PLC

(TREASURY DEPARTMENT)

MARIEHAMN

SUPE

ERPFIN

TMK C2

MARIEHAMN

FINLAND

FI

U

AABAFI22

XXX

BANK OF ALAND PLC

MARIEHAMN

SUPE

ERPFINTGT

C2

NYGATAN 2

22100 MARIEHAMN

FINLAND

POB 3

22101 MARIEHAMN

FINLAND

FI

U

AABASESS

TMS

ALANDSBANKEN ABP (FINLAND),SVENSK FILIAL

(TREASURY DEPARTMENT)

STOCKHOLM

SUPE

FIN

ALL CS

STUREPLAN 19

107 81 STOCKHOLM

SWEDEN

FI

U

AABASESS

XXX

ALANDSBANKEN ABP (FINLAND),SVENSK FILIAL

STOCKHOLM

SUPE

FINSSK

CS

STUREPLAN 19

107 81 STOCKHOLM

SWEDEN

FI

U

AABMIT22

XXX

AFV ACCIAIERIE BELTRAME S.P.A.

VICENZA

CORP

FIN

C2

VIALE DELLA SCIENZA 81

36100 VICENZA

ITALY

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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.