Comparing account numbers on two seerate sheets

[Howardc1001]

I have account number on sheet "Per TB" and account number on sheet "Per AFS"


I want to compare account numbers on sheet PER TB that is not on sheet "per AFS" and extract those that are on not on sheet "per AFS"


I also need to compare account numbers on sheet PER AFS that is not on sheet "per TB" and extract those that are on not on sheet "per TB"


your assistance in this regard is most appreciated


i have attached sample data

[AliGW]

I loaded each list to connection only and then performed two merge queries - one left anti and one right anti - to isolate the A/C numbers unique to each list.

[Howardc1001]

Thanks for the help AliGW

i am not very familiar with Power Query


It would be appreciated if you would advise me of the steps taken to get the final result

[AliGW]

I don't have time now - sorry. Just about to go to work. Someone else may be happy to walk you through. I have outlined the steps I took in my previous post. I will not be around much at all today or this evening, I'm afraid.

[Howardc1001]

No Problem. When you have a chance later in the week , you can let me have the steps

[Howardc1001]

Hi AliGW

I selected Sheet TB and then went to Data Tab and then selected From Table/Range and then selected Close & Load To and selected only create Connection. I did the same for Per AFS

However I am battling with the Merge-See screen prints

It would be appreciated if you can help me later in the week when you have a chance

[AliGW]

You are doing the same thing twice - if you swap the tables around, do both as left antis. I left the tables the same - TB first and AFS second - so used left anti then right anti.

Got to go and teach ...

[Bo_Ry]

TB not on AFS

=IFERROR(INDEX('PER TB'!A:A,AGGREGATE(15,6,ROW('PER TB'!$A$2:$A$300)/ISNA(MATCH('PER TB'!$A$2:$A$300,'PER AFS'!$A$3:$A$300,))/('PER TB'!$A$2:$A$300<>0),ROWS(A$2:A2))),"")

AFS not on TB

=IFERROR(INDEX('PER AFS'!A:A,AGGREGATE(15,6,ROW('PER AFS'!$A$3:$A$300)/ISNA(MATCH('PER AFS'!$A$3:$A$300,'PER TB'!$A$2:$A$300,))/('PER AFS'!$A$3:$A$300<>0),ROWS(A$2:A2))),"")

[Howardc1001]

Thanks AliGW


Will have a look at this later and let you know how I go

[Howardc1001]

Many Thanks for your help, Bo_Ry

[Howardc1001]

Hi AliGW

Thanks for your patience and help.

I finally go it to work. PQ is very powerful and easy to use once you know how


I have a training tutorial on power pivot & power Query and need to go through this so I become more familiar with this feature

[AliGW]

I agree - I often refer to it as Pandora's Box, and have found it an invaluable tool, well worth investing some time into learning. I take it you no longer need the walk-through? It will be much late, if you do, as I have a Parents' Consultation after lessons tonight.

[Howardc1001]

Thanks -all sorted

Many thanks for all the help. i have learnt a great deal from many people on this Forum who give up their time to help others