Macro to draw in and compare 2 datas

[kekstrom]

Let me try to set up the scenario...

We buy something, say 10 units of it.

We then take those 10 units that we bought, and send them out to 5 different people, 2 units each.

We have 2 tables on a worksheet, one which lists everything we've bought, how many, the price we paid, and when we will pay for it.

the other table has the same info, but it says who we are shipping the units off to.


we are trying to build a way that we can compare the buys to the allocations. we need to make sure that we aren't trying to ship off more than we purchased, make sure the price is correct, make sure the q is correct, and the date.

right now we use vlookups to compare the columns, but it is still confusing to use and comprehend the values that are being returned...

Here is what we would like:

I have created a 3rd table which is to compare the buy to the allocation.

Right now i do the following:
highlight and copy all of the allocations and paste them into the compare table, a vlookup then brings in the buy of the item.

formulas then sum and average all of the data so we can see any differences in quantity, price, and date.


The problem is that we can't do the reverse of this, as in, paste in the buy to draw in allocations. ALSO, WHEN WE DO PASTE IN THE ALLOCATIONS, IT IS ONLY RETURNING 1 BUY ORDER, WHEN THERE COULD BE MORE THAN 1.



I want to be able to select by clicking/highlighting the item in either the buy or the allocation table, and then press a button that launches a macro.

the macro will draw in any buys on the item along with the amount, price, and date... and also draw in any allocations on the item.

From there, I can set up the formulas to compare the items, but the goal is to automate the process of finding the items associated with the selected item #, then bringing them to the table to be viewed/compared.




I know this might sound really confusing and complex but it isn't as bad as it seems, I would be more than happy to answer questions or do anything to help. THANKS

 

[dannac]

You may get some help/advice if you post a screen shot of the sheet

 

[kekstrom]

Sorry I had to blot out the account information and stuff, hope this helps.

 

[Jerry Sullivan]

Hi kekstrom,

This can probably be done most efficiently with autofilters or advanced filter.

Please clarify a couple of details:
1. Do you prefer highlighting or selecting the cell(s) to indicate which transactions (and all associated buys/allocations) you want copied?

2. Will you always be highlighting/selecting one CUSIP (which I understand could have many buys/allocations), or do you need to be able to select multiple CUSIP's?

 

[kekstrom]

1) I am indifferent. highlighting just seemed like the easiest way. right now I am copy and pasting the cusip into another sheet.

2) it will always be just 1 cusip

 

[Jerry Sullivan]

You can try the code below after doing some setup:

The code uses Advanced Filter. You’ll need to change your header names slightly so they match between the Data Tables and Compare Trade Results Tables as shown in the screen shot. Likewise you’ll need to change your header in A1 from Symbol/Cusip to CUSIP so this Key matches everywhere.

Then define these Named Ranges:
"Buys_Data" – Your Buys Table including Header Row
"Allocations_Data" - Your Buys Table including Header Row
"Cusip_Criteria – Range W7:W8 on screen shot
"Buys_Results" – Range V17:Y20 on screen shot
"Allocations_Results" – Range V23:AB51 on screen shot

Excel Workbook
	V	W	X	Y	Z	AA	AB
7		CUSIP	Q	Px	S/D
8	Buy	271371QT6		Add formulas
9
10	Allocation	271371QT6		Add formulas
11			0	0	0	Difference
12
13
14
15
16	Buy
17	CUSIP	Quantity	Price	Settlement Date
18	271371QT6	25000	$ 100.73	8/15/2011
19
20
21
22	Allocation
23	Account Name	Account #	CUSIP	Price	Trade	Settle	Par
24	XXX10	656046167	271371QT6	100.733	8/10/2011	8/15/2011	25
25
Allocation Check

Here is the code which you can link to a button on your worksheet.
It allows you to either type in a Cusip or pick a single cell with a Cusip value.
If you have a Cell selected before you click your macro button, it will use that Cusip value as the default.

Sub Compare_Trade()
    Dim varRes As Variant
    Dim strDefault As String
    On Error GoTo ErrorHandler
    
    'If Cusip already selected, use as default
    If Selection.Count = 1 Then
        If Len(Selection.Value) = 9 Then
            strDefault = Selection.Value
        End If
    End If

    varRes = Application.InputBox("Enter Cusip or select Cell with Cusip", _
        "Compare Trade", strDefault, , , , , 10)
    If varRes = False Then Exit Sub
    
    'Clear previous reporting
    Range("Buys_Results").ClearContents
    Range("Allocations_Results").ClearContents
    Range("Cusip_Criteria")(2) = varRes
    
    'Copy Allocations and Buys
    With Worksheets("Allocation Check")
        .Range("Buys_Data").AdvancedFilter _
            Action:=xlFilterCopy, CriteriaRange:=Range("Cusip_Criteria"), _
            CopyToRange:=Range("Buys_Results"), Unique:=False
        .Range("Allocations_Data").AdvancedFilter _
            Action:=xlFilterCopy, CriteriaRange:=Range("Cusip_Criteria"), _
            CopyToRange:=Range("Allocations_Results"), Unique:=False
         Range("Buys_Results")(1).Select
    End With
    Exit Sub
ErrorHandler:
    MsgBox Err.Number & " - " & Err.Description
End Sub

Please give it a try and let me know if this does what you wanted.

 

[kekstrom]

Jerry, I am going to test and work on this at the end of the day today.

I'll let you know how it goes.

I did a quick test on the sheet you email just running Friday's #'s through it and I picked an allocation that had 50 allocations and it only drew in the first 14. Not sure why but I'm going to play around with it.

Thanks, you the man Jerry.

 

[kekstrom]

Been testing it this week. One of my coworkers made a few changes too it, but it works great. You are awesome Jerry!

 

[Jerry Sullivan]

Thanks for the feedback.

Did you figure out why this happened?

I did a quick test on the sheet you email just running Friday's #'s through it and I picked an allocation that had 50 allocations and it only drew in the first 14. Not sure why but I'm going to play around with it.

This was my first use of AdvancedFilter, (previously I always used AutoFilter for this function). So if the changes your co-worker made were an error in the code, please let me know what that was. If the changes were just tailoring it to your use, then no need to post that change.

Cheers!