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

[cowpers]

Hi there,

Obviously I'm new here and thought I'd ask some of the experts here for a bit of help. Hope someone can assist

Background: I'm trying to create a rec report, ideally on one page, for multiple criteria. The situation is we get a report on our trade positions (investment stuff) from an outside service we use. We also have a report internally from what we've booked. We're looking to tie out are basic things like price, original par, revised par, trade date, counter-party banks, etc. I've consolidated data from both sources on one page (DATA) where all data points are lined up (say EXTERNAL is from rows 1-500, and INTERNAL is 510-1010). Now I need to create a front page showing the result of each tie-out. The lookup value will be a combination of the fund name + trade ticket number which would almost guarantee a unique ID (fund name XYZ, ticket number 12345 = XYZ12345)

Current tabs on spreadsheet: DATA, EXTERNAL, INTERNAL. I need to add a REC page

Question: What formula can I use to check on each and return TRUE/FALSE? The goal is to have about 8 columns on the REC, each for one of the tie-outs (let's use price as an example.). I know the basic VLOOKUP to return one value, but it appears in this case I will need VLOOKUP to check two matching values based on unique ID XZY12345, compare the price, and return TRUE if they match, FALSE if they don't

Here's something I saw from one of our spreadsheets that I think does the same to confirm counter-party, though I have no idea how to read this lengthy formula.

=EXACT(VLOOKUP(IF(Q2="COMPANY",R2,Q2),FUNDS!$M$1:$N$400,2,FALSE),INDEX(PENDING!$G$4:$G$3218,MATCH(CP!C2,PENDING!$N$4:$N$3218,0)))

Thanks for your time reading and helping. I feel guilty not being a part of the community and asking for your help. I would offer to compensate but I'm sure that's against guidelines. I'm willing to donate $10 to Hope4paws or the Salvation Army or something! thanks again

 

[smb_146]

does this link work? this is a rough draft of our real trade file, obviously

https://docs.google.com/file/d/0BwLZZcWGac6ANmZ6M1pXMVFLamM/edit?pli=1

front page is to bring in true/false based on matching for price, buy/sell side (will add others later - trade date, bank, par, etc.)

This one will work

=IF(VLOOKUP($A2,external,2,FALSE)=VLOOKUP($A2,internal,2,FALSE),"TRUE","FALSE")

for details here is the link to your workbook (solved version check sheet "rec (2)"

http://1drv.ms/1jyi7cV

Let me know if this is correct or not

 

[cowpers]

Hey - your formula worked really well for what i'm using it on. it's the one i'll be using on my spreadsheet so far

going through the assignment, though, i realize a lot of flaws in my earlier approach

Here are a few problems (also now in the updated text on the spreadsheet) - link below

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: 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. how can i add to your formula to ignore this?

all are highlighted in yellow on the spreadsheet with notes. if you can assist again, i'll be even more grateful! again, i'd like to chip in a few bucks to the donation bucket on this site if there is such a thing. thank you!

https://drive.google.com/file/d/0BwLZZcWGac6AREo1NmFZZGwxZ3M/edit?usp=sharing

 

[cowpers]

NBVC - thanks for your help also!

 

[NBVC]

In the Internal sheet, I2, try:

=IFERROR(IF(VLOOKUP(A2,external!$A$1:$K$13,6,FALSE)="Company",VLOOKUP(internal!A2,external!$A$1:$K$13,7,FALSE),VLOOKUP(A2,external!$A$1:$K$13,6,FALSE)),"")

copied down. Note that when you add the $ signs around references, it "freezes" those reference so that you can copy down or across without changing the references and having to come back to manually change on each row.

In the REC sheet, D2:

=IF(ABS(VLOOKUP($A2,DATA!$A$2:$F$11, 4,FALSE))=ABS(VLOOKUP($A2,DATA!$A$21:$F$30, 4,FALSE)),"TRUE","FALSE")

and similarly in E2:

=IF(ABS(VLOOKUP($A2,DATA!$A$2:$F$11, 5,FALSE))=ABS(VLOOKUP($A2,DATA!$A$21:$F$30, 5,FALSE)),"TRUE","FALSE")

both copied down.... and similarly in F2, if needed.

On the Data sheet, without using macros, the best way would be to use a formula to identify the missing rows, then you would manually copy/paste to the Internal sheet.

You can use a formula in Data, G2 like:

=IF(ISNA(MATCH(A3,internal!A:A,0)),COUNT(G$2:G2)+1,"")

copied down. This will identify and number the rows not found so you know which and how many are missing.

I am not understanding your request in the REC2 sheet about Pivot tabling... can you show an example of what you want to see?

 

[smb_146]

Hey - your formula worked really well for what i'm using it on. it's the one i'll be using on my spreadsheet so far

going through the assignment, though, i realize a lot of flaws in my earlier approach

Here are a few problems (also now in the updated text on the spreadsheet) - link below

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: 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. how can i add to your formula to ignore this?

all are highlighted in yellow on the spreadsheet with notes. if you can assist again, i'll be even more grateful! again, i'd like to chip in a few bucks to the donation bucket on this site if there is such a thing. thank you!

https://drive.google.com/file/d/0BwLZZcWGac6AREo1NmFZZGwxZ3M/edit?usp=sharing

I'm going to answer your questions in the no. you put
1. This one is very easy a simple IF solved it.
2. This is a tricky one. This kind of work is done with VBA but i've just started learning VBA so can't help it right now. But i did find another way to do it.
I modified the "Data" Sheet and re-arranged it and the data from External Sheet is Listed in Columns A:F and data from Internal Sheet in Columns K:P.
I also converted the data on "External" sheet into table and named it as "Ext_Source".
I've also assumed that the no. of Unique IDs will always be Greater in External Sheet than the Unique IDs in the Internal Sheet.

Now if you wanna enter anything new Go to Your External Sheet then move to the very lower right corner and press TAB and start entering your data or you can simply copy/paste your data.
This will automatically be reflected on "Data" Sheet and also on the "REC" Sheet. Right now this will not work for the Internal Sheet (because I'm running out of time and can't create formulas for that section). But I think if you carefully look the External Data section on your "Data" sheet you can easily automate the Internal Section.

3. This one is also very just check the formula and you'll get it.

I don't think there is any need for a pivot-table because i think this will solve your problem.

One tip: Always use tables wherever you can. To know why just move to sheet"REC2" go to cell F13 and press TAB and read the contents of new row.

Here is your Revised Sheet

http://1drv.ms/1ld2Zzc

 

[smb_146]

Here is a VBA based solution. This one is fully automated, however there is still one issue to be addressed but that can be resolved (check DATA sheet for details)

Now you only have to enter data in your External & Internal sheets. After that move to "REC2" sheet and hit enter and you are done.

(If you encounter any error message after running the MACRO just ignore it and Check the box so that you are not notified again.)

Hope this helps.

Here is your workbook
http://1drv.ms/1gdsE5v

 

[cowpers]

NBVC - thank you, sir. i used what you shared last week and it worked out for the most part. i just need to figure out how to round up a few items so that false breaks (pennies, a couple dollars) would be ignored. i'll look into this again in the next couple of days

smb - you are really good at this also. what you attached looks really advance so i'll have to look into it. i guess you majored in computer science or work in a related field

does this website have a paypal for donations? i'd like to contribute since i'm very impressed with the amount of help/feedback received. thanks again and i'll let you know if anything else comes up

 

[NBVC]

NBVC - thank you, sir. i used what you shared last week and it worked out for the most part. i just need to figure out how to round up a few items so that false breaks (pennies, a couple dollars) would be ignored. i'll look into this again in the next couple of days

Can you explain which formula you want to adjust and the logic you want?

As far as donations are concerned, I don't think this site promotes donations. We are all volunteers doing this for fun and to keep our brains fresh. If you want to donate, I guess you could do so by buying a book by Bill Jelen, the site owner, from the MrExcel Store.

 

[smb_146]

NBVC - thank you, sir. i used what you shared last week and it worked out for the most part. i just need to figure out how to round up a few items so that false breaks (pennies, a couple dollars) would be ignored. i'll look into this again in the next couple of days

smb - you are really good at this also. what you attached looks really advance so i'll have to look into it. i guess you majored in computer science or work in a related field

does this website have a paypal for donations? i'd like to contribute since i'm very impressed with the amount of help/feedback received. thanks again and i'll let you know if anything else comes up

Thanks for your generous comments.
and your guess is 50% right I'm a Management Accountant(CMA) and my major work is related to analysis. For donations I'll go with the NBVC's comments.

 

[cowpers]

a few more things i could use help on finishing up this assignment, and it's basically rounding up on small breaks so it doesn't say "false".

1) if one price says 98.50 due to the format of the cell but it's actually 98.4999 and it's comparing to another that's exactly 98.50, how can we use ROUND to ignore?

2) similar to the above, On "Revised Par", if there are penny breaks after changes are made to a trade and one party knows 999,999.55 and another knows 999,999.21, this isn't significant and i would also want to return a "TRUE" for the tie-out. is there a way to choose the thresh hold (say allow $1 difference)

3) somewhat similar again when comparing trading partners. if one says JP Morgan Chase and another says JP Morgan, this would return false. anyway to make this TRUE using a formul? the only way i know is to map it on another sheet where i would say JP Morgan Chase = JP Morgan, and then do the tie-out.

https://drive.google.com/file/d/0BwLZZcWGac6Aa3BhTWNHTDBnVGM/edit?usp=sharing

thanks again guys your talent is admired!