Hi Everyone,
My question is very simple, but almost got me in big trouble. it figures you cannot rely totally on excel, and as much as a formula looks simple and effective it might mislead in cases the data just looks the same but formatted differently.
So I have 2 files , one for August, second for July (in my example I have put them in 2 tabs for convenience) . The real file consists of 1000 lines each.
so - I was trying to lookup values in Aug. that already existed in July in order to remove them. sound simple......
I used this formula in F2, and wherever the answer returned "1" - it means there is a match to the other tab.
=LARGE((A2=Jul.!A:A)*(Aug.!E2=Jul.!E:E),1)
Everything went well for the majority, but it missed to find a match for lines 9-10, where they do appear on July, but formatted as text.
Does anyone have any advice how to bypass this without having to change the data or review one by one to find such discrepancies?
The result was that the excel didn't recognize that these invoices were already paid last month, and we almost submitted them for a duplicate payment.
Thanks everyone for your help!
Bookmarks