Excelers/VBA'ers,i have 2 sheets in excel,each contains 6 columns containing trade attributes ,e.g matrurity,trade date,index,etc.....i need to identify similar trades,what is the best way to automate the procedue.note that i do not have a common identifier such as trade number,but common attributes.in order for me to confirm that 2 trades are indistinguishable is to have a least 3 attribures the same.Thanks,

How fast does it need to be? VBA will be dead slow for something like this but and XLL suing map or Excel4Dna + C# dictionary or some other content based construct might be fast enough to be effective.

Thanks Hansi.speed is not important at the moment,filtering out similar trades will be the prime objective.My tech skills are not advanced.I am going to google "XLL suing Map",and "Excel4Dna",to get further info .do you have any leads to read or code to go through.cheers.

QuoteOriginally posted by: SamsaveelExcelers/VBA'ers,i have 2 sheets in excel,each contains 6 columns containing trade attributes ,e.g matrurity,trade date,index,etc.....i need to identify similar trades,what is the best way to automate the procedue.note that i do not have a common identifier such as trade number,but common attributes.in order for me to confirm that 2 trades are indistinguishable is to have a least 3 attribures the same.Thanks,It sounds like you're trying to reconcile trades, presumably with a one-for-one matching.I notice that you say SIMILAR trades rather than IDENTICAL trades. Identical trades is easy. Concatenate the 6 fields together and assuming that your data is in the same format on the other sheet, by concatenating each field you will have created a unique identifier to match using vlookups or VBA code or xlls or whatever, depending on your level of expertise and need for speed/ other requirements. Remember as a control to count the instances of each unique identifier to take into account any dupe/ identical trades you might have.For SIMILAR trades, you will need to be a bit more clever. My experience has shown that you can usually still match most of the data using the above method,(these are your strongest matches) and then focus on a sample of the remaining non-matches and find what is causing them to break. If there is a small difference in price for example, you can round both values and regenerate the unique key so they both match. If the name spellings are slightly different you can standardise/ clean up the text fields. Only weaken the matching criteria for fields that you know might have some variation on them. If you know that the date,(for example) MUST be correct, then keep that as a strict rule and make sure it is always included in your unique identifier.Remember, the more "cleaning up" you do, the weaker the match and the greater your chance of creating false positives (incorrect matching). Avoid 100% forced matching and instead aim to report any exceptions at the end which you can deal with manually. Again, Excel has formulas and pivots that you can do this semi-manually with, or you can code it in VBA, or you can use more sophisticated methods.All of the above is relatively straight forward to do in VBA and if speed really isn't important, I would recommend VBA for automation. If your VBA skills aren't up to scratch or this is only going to be done a few times then there's no harm in using Excel to speed up some of the manual processes. It's a good way to learn and understand the data you're working with.

Concatenate your 6 fields using a unique field separator (i.e. whatever char or string that won't appear in your data) and then use regular expression to match similar trades. It is going to be slow, but as long as you can write the correct regex, the code is going to be very simple.Another point is you can use all standard .NET/C# classes in VBA code (e.g. Dictionary, RegularExpression etc). So the above can be achieved using VBA if you are more confident with it than other technologies.

i am actually neither an expert nor a begginer in VBA .I can write functions,and use vba workbook functions,but thats about it.can you share a code for the above purpose for me to study and expand?Thanks

This may get you started:Let's assume you have Sheet1 and Sheet2 with data on both sheets in columns A through FStep 1: Copy the function below into a VBA code moduleFunction Mtch(sourcerow As Range, ansrows As Range) As Long Application.Volatile True Dim rw As Range, n For Each rw In ansrows.Rows n = Evaluate("=SUM(IFERROR(IF(COLUMN(A:F)=MATCH(" & sourcerow.Address & ",Sheet2!" & rw.Address & ",0),1,0),0))") If n > 2 Then Mtch = rw.Row Exit Function End If Next Mtch = 0End FunctionStep 2:On Sheet 1 Enter the following formula on in cell H2((Assuming row 1 has column headings) on Sheet1 and copy down as far as you need it: =mtch(A2:F2,Sheet2!$A$1:$F$10000)Be sure to adjust the $F10000 to the row where your data ends on Sheet2.The formula will return the row where three or more items match and 0 if a match is not found.Edit: This assumes Excel 2007 or above

Many Thanks jpsnj.