Comparing two lists and extracting differences

[irbadatvba]

Hi, I have spent a significant amount of time trying to figure out how to do this before coming here, unfortunately given I rely on the macro recorder and other's code examples I am unable to resolve this so many thanks in advance!

I have two lists which 'should' reconcile. Within each list/row is several variables - buy/sell (direction), amount, rate and currency - these all need to match, among other entries that won't need to be matched.

The difficult part is that there may be duplicates within each list as not all users can see all entries. For example, say there are 3 of the same entries in only one sheet, but 2 in the other (all identical however). I need to avoid confirming they match if the 3 identical entries match against a single entry in the other list (so needs to be able to treat each entry as unique). I thought of resolving this issue by deleting a matching row every time there is a match, hence in the above example, once it matched the first 2 of the 3 entries, the script will be unable to match the last (3rd) entry given the other 2 were deleted.

I would then copy the remaining entries (the entire row of that particular list only) from the list that has deleted entries and copy them into an unmatched sheet. I would then reverse the process and do it the other way around to identify discrepancies in the initial list.

Perhaps someone can suggest a smarter way of doing this over all? Perhaps prescribe each row in each list a unique variable and then match them off, extracting any remaining unmatched variables?

Thanks

EDIT: To clarify, the comparable fields are not next to one another, so the code needs to accommodate comparing fields within a row of differing positions.

[kersplash]

You could use Data -> Get & Transform for this sort of thing.

Can you attach a small sample showing what you are trying to achieve?

Go Advanced -> Manage Attachments -> Upload

[irbadatvba]

Hi kersplash, thanks for your reply. This is my attempt so far:

Sub DeleteMatches()

'Declare variables

Dim Direction As Variant
Dim OrderType As Variant
Dim Amount As Variant
Dim CCY As Variant
Dim Rate As Variant

Dim RowCount As Integer
Dim RowCt As Integer

Dim Formula As Variant
Dim iRow As Integer

Dim colNum As Variant

Sheets("EXTRACT").Select

RowCount = Cells(Rows.Count, 1).End(xlUp).Row - 1 'creating variable for total rows in deleted data set
RowCt = Cells(Rows.Count, 11).End(xlUp).Row - 1 'creating variable for toral rows in another deleted data set
   
iRow = 2

Direction = Range("K" & iRow)
OrderType = Range("L" & iRow)
Amount = Range("M" & iRow)
CCY = Range("N" & iRow)
Rate = Range("P" & iRow)

'colNum = Application.Match(1,((B1:B2=OrderType)*(C1:C2=Direction)*(D:D=Amount)*(E:E=CCY)*(H:H=Rate)),0)
'Formula = "MATCH(1,((B:B="" & OrderType & "")*(C:C="" & Direction & "")*(D:D="" & Amount & "")*(E:E="" & CCY& "")*(H:H="" & Rate & "")),0)"
Formula = "MATCH(1,((B:B=OrderType)*(C:C=Direction)*(D:D=Amount)*(E:E=CCY)*(H:H=Rate)),0)"
'Formula = "MATCH(1,((B:B=L2)*(C:C=K2)*(D:D=M2)*(E:E=N2)*(H:H=P2)),0)"
'colNum = 0
'colNum = Worksheets("KOOLTRA RAW").Evaluate("MATCH(1,((B:B=OrderType)*(C:C=Direction)*(D:D=Amount)*(E:E=CCY)*(H:H=Rate)),0)")

None of the formulas work at the bottom when I try using the variables in order to loop through the list with iRow = iRow + 1. Once I am able to do this, I believe I will have finished. Instead colNum = Formula(Evaluate) returns an error. Any advice? I have tried searching extensively. Similarly used the Watcher tab to see what the value was while executing.

[mikerickson]

Could you post a workbook with a sample of representative data and the desired result?

[irbadatvba]

Thanks for your replies, please find attached an example.

Basically, I have two lists (there are actually many lists that are compared/reconciled daily) that need to be equal. However, as mentioned there are sometimes duplicates or even multiplies entries of the same between lists. In which case I can't double or triple count a positive match based off only one match in the other list.

This is why I have used the MATCH function (which will ultimately loop through the list) to identify and then delete the first matching entry.

Ideally, both lists would be compared to one another concurrently and mismatched entries would just be highlighted as opposed to deleted, but I can't figure out a simple way to do this.

[irbadatvba]

Bump. Please help.

[JeteMc]

Hello irbadatvba and Welcome to Excel Forum.
Here is a formula based proposal that employs multiple (formula populated) helper columns.
The gist is that columns K and S concatenate the information that needs to be matched.
Column L displays whether or not the information in the first table matches that in the second.
Column T displays how many times the information in the second table matches the first (if any).
Column U displays the first row of the match (or FALSE if there isn't one).
On the Unmatched sheet column A displays the full list of unmatched concatenated items from both tables.
Column B displays whether or not the items are unique.
Column D displays the unique list of items.
Columns E:M display variables for each item based on the column headers for the first table.
Note that F88, J88 and L:M88 are blank as there is no information pertaining to those variables in the second table.
Let us know if you have any questions.