someone told me this formula but i am not sure if it even tells me the right thing

[esaelias]

Hello so

two sheets with invoices numbers

they are dervied from two different systems and im trying to see which are the missing invoice numbers

both have been saved in a separate sheet

so sheet 1 is from a system elite

it has 12586 invoice numbers with duplicates (but i remove the duplicates)

sheet 2 is from IZA and it has

11115 invoice numbers


someone told me this formula

but it doesnt really show me the missing invoice numbers it shows me all the ones that are in both of the columns in the two different sheets


1=LET(list,VSTACK(P7:P20,Q7:Q20),UNIQUE(FILTER(list,list<>"")))

[TMS]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[TMS]

Are there gaps going both ways? Or does one list have all the numbers?

[esaelias]

no

there is a column A with all the invoice numbers
and column B shows all the debit credits
colmun c shows the clients


e.g.


COlumn A COlumn B Column C
12345 100 John
12345 -100 John
13456 200 Piki
13456 -200 Piki
12233 -100 gabby


problem is as you can see there is a -100 with gabby and also with john

i want the contra entries to be match but also i want to see if they match with the right clients or invocie numbers

also the invoice numbers are duplicates too so e.g.

12345 100pounds
12345 20 pounds

but some onf them are single so they seem ok

[esaelias]

i will try uploading a file but its as above

[esaelias]

so i ahve attacehd the file

i had to take client names out

if you filter on sum 995

you will see a load of 995 and some -995

my question is if its bases the formula on numbers only it will look at the numbers but i have different clients

so i ideally want to see the formula maybe say like true of false

e.g. gabby may have 995 and a -995

lets say aj has -995

what if gabbsy positive 995 contras aj's -995

[AliGW]

I have closed your duplicate thread. Please take a moment to read through the Forum Rules page.

Continue in this thread.

[AliGW]

Please provide a sample workbook with no more than 30 rows (not 30000+) of representative sample data and mocked up expected results.

[esaelias]

can you please delete this thread i have made a better one with all the data