• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Reconciliation formula(s) - vlookup two cells and return TRUE/FALSE for match?

C

cowpers

New Member
Hey there - obviously i just recently signed up. as someone who isn't part of the community and looking for here, i do feel slightly guilty. i appreciate the time, knowledge, and help from others so i'm willing to tip $10 to a tipjar to support this website (or donate to hope4paws / salvation army). i hope someone can help me with this assignment!

i have a project trying to tie data from two reports out, making sure some of the key points match
  • one file comes from an outside service we use
  • the other file is internal and a record of what we've booked
it's trade/investments related. since the external file is not in the same format with the same number of rows/columns, i created a DATA tab that organized both reports (external data on top, internal on bottom - shared page)

The lookup value that both reports will share (and it's unique in most cases) will be fund name + trade ticket #. (trade ticket # + fund name, e.g 12345XYZ)

let's say for 14000CDO (found on both reports), i'm trying to come up with a formula that would look that value and compare a certain column (price, trade date, etc.). If it matches, return TRUE. if not, FALSE. can anyone help?

i attached a sample spreadsheet (not real obviously) to save the experts here inevitable frustration from back-and-forth messaging with me

thank you!
 

Attachments

Hi ,

Can you clear my confusion ?

You have formulae in the External and Internal tabs by which the data on both tabs will always match. With these formulae in place , where is the question of reconciliation ?

See the uploaded file.

Narayan
 

Attachments

Hi, cowpers!

As a new user you might want to (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, try this:
B2: =SI.ERROR(INDICE(external!$K:$K;COINCIDIR($A3;external!$A:$A;0))=INDICE(internal!$H:$H;COINCIDIR($A3;internal!$A:$A;0));FALSO) -----> in english: =IFERROR(INDEX(external!$K:$K,MATCH($A3,external!$A:$A,0))=INDEX(internal!$H:$H,MATCH($A3,internal!$A:$A,0)),FALSE)
C2: Formula error, Carlsberg are missing.

Regards!

PS: About your willing to contribute, a six-pack of Carlsberg will do the job ;)
PS2: Giving a second look to the figures in your uploaded workbook and to business nature, better six six-packs :DD
PS3: No need to do anything for Chandoo, he only drinks water and milk o_O
 
Narayank - you're really good with excel. yes, that was the case where all things matched (perfect data). however, i've ran into few real-life issues that pretty much covers what you're saying. one source having more or less data than the other, and obvious breaks that come along with it

SirJB7 - thanks. i don't get it, does your formula work too? there are so many ways to do this, apparently.
i'll have to plug and play and just stick with the one that work's best (so far i'm using a different one someone else game me)

here's a revised sheet with realistic problems that i'm encountering on our real file

1) looking up the counter-party using a better formula. my previous one was flawed. using vlookup (matching the unique ID) if seller (F column) is "Company", then return value in G column for the unique ID. if Seller is not "Company", then return F

2) missing unique ID on one side: in my previous sheet, both sources had perfect data, nearly. 9 trades each. i realize in reality that's not going to happen. this time i added 3 more to the external source that we don't have. how can i consolidate the DATA sheet which now has 21 (9+21) on the REC sheet, and show that there are 3 missing? i think a pivot table of some sort would work

3) absolute value break: another issue that came up is when we are selling, we book a NEGATIVE number (-3,000,000) instead of a neutral 3mm, which the external source has. this is creating a false break. anyone to correct this?

everything is highlighted in yellow with a note attached. anyway, it's due this week so after i'm done, i do prefer to chip in a few bucks to a donation bucket on this website (as well as others i've used). no virtual six-packs, i'm afraid :)
 

Attachments

cowpers said:
SirJB7 - thanks. i don't get it, does your formula work too? there are so many ways to do this, apparently.
i'll have to plug and play and just stick with the one that work's best (so far i'm using a different one someone else game me)
Click to expand...
Hi, cowpers!
It worked in a scratch file made from yours and changing a couple of 98.50 values to anything else so as to test it. Have you actually tested or it was a rhetoric question?
Regards!
 
SirJB7 - i've copied and pasted the three formulas received from helpers. haven't tested yet. here's another one I have that i think would also work

IF(VLOOKUP($A6,DATA!$A$2:$F$1000,2,FALSE)=VLOOKUP($A6,DATA!$A$1006:$F$2124,2,FALSE),"TRUE","FALSE")

Please let me know if there a "best" or preferred way to do this, perhaps one formula captures more and is more "adjustable" than the others

Narayank991 - appreciate your time & help!
 
Hi, cowpers!
As long as the formulas work the best is that with which you feel more comfortable and qualified to maintain. That last one doesn't handle the #N/A value returned by VLOOKUP function if doesn't find the searched argument. And please don't ask me if a formula posted by you works on your file. Just test it and eventually inform us about it behavior.
Regards!
 
Hey - sure, i'll be sure to keep that in mind. Didn't think there would be so many people offering different formulas. Didn't mean to be lazy!

Thank you
 
Hey - i like your table. it looks very organized. the only downside that it would have is that it's not saying TRUE/FALSE. this would make it a lot easier to check on FALSE items when filtering at the top. we have thousands of lines in the real file so looking side-by-side to see breaks might not be an efficient way

also still trying to figure the formula for counter-party out. i'll work on it and will let you guys know if i've figured anything out. thanks again for your help. i'll try to steal some of what i'm seeing from your REC2 work
 
You must log in or register to reply here.
Back
Top