0

I have a table that stores imported data. Upon each import I need to check if an imported item already exists. I use a unique ID for this and it works fine. BUT the reason for the match could be that the same record is being imported (there are no controls on date ranges for the creation of import data), or the data may actually have been amended.

So my issue is that to see which type it is I was planning to run a calc on the 11 currency fields that this record has. I can't just add them up since the amendment of one field (say an increase of 10, would reduce another by 10). I thought of Field1 x1 + Field2 x2 etc. which should remove the dependency.

This isn't really a vba issue but more of a data uniqueness issue I think.

Of course I could compare 11 values but that is messy in VBA! Any ideas welcome. Many thanks.

Data example Record in current database: Receipt Payment DealValue Tax Duties etc 3,500 0 3,600 100 0 //3,600 - 100 = 3,500 net receipt

Importing record Receipt Payment DealValue Tax Duties etc 3,500 0 3,650 150 0 //3,650 - 150 = 3,500 net receipt

I'm don't wish to create an ID, just a calc that can be compared to see if there is a change.

Lester Lane
  • 3
  • 4
  • Please [edit] your question to include the same data you have, the unique ID you wish/want to generate, the location where you save the data and how you want to compare the data to import vs. the data which is already saved. Also is it possible to generate an ID at the source in any way (like a UUID)? – Progman May 09 '20 at 13:27
  • How about simply looping the values dynamically? – Vitaliy May 09 '20 at 13:30
  • Added data but it's a mess, sorry. Basically same net result but different numbers – Lester Lane May 09 '20 at 20:09
  • @Vitaliy - like the idea of this. Would I loop the fields of each recordset and simple compare? I could even update if different. Would it be along lines of: 'Dim fld1, fld2 as Field ah, can't add code here, can't get new line :-( could you please post example? Thanks – Lester Lane May 09 '20 at 20:11

1 Answers1

0

In the looping approach you would do something along the following lines:

firstColumnOfInterestIndex = ...
lastColumnOfInterestIndex = ...
duplicate = False
...
For Each currentRow In ActiveSheet
    For i=firstColumnOfInterestIndex to lastColumnOfInterestIndex  
        If Worksheet.Cells(currentRow.Row, i) <> expectedValue(i) Then
              Exit For
        End If
        If i == lastColumnOfInterestIndex Then
             ' A duplicate found! handle accordingly
             duplicate = True
        End If
    Next i
    If duplicate Then
         Exit For
    End If
Next currentRow
Vitaliy
  • 291
  • 1
  • 14
  • Many thanks. I'm using Recordsets in Access but I will follow the logic and translate. I have not used the Exit For before, which will be a neat way of getting out of that specific loop and on to the next. I also have the first group of fields in each record that have nothing to do with the other record, so I need to get the fields to the same place before running the loop of fields and comparing them, if that makes sense! – Lester Lane May 10 '20 at 11:52
  • Great, happy it helped, don't forget to accept the answer if it is indeed helpful – Vitaliy May 11 '20 at 08:26
  • I tried but I am too junior! – Lester Lane Jan 09 '23 at 16:17
  • What didn't work for you? – Vitaliy Jan 10 '23 at 18:15
  • In the end the records were finally given a ref. If it was the same, I saved record, if different, I saved record. Did away with all testing. Thanks for the looping idea tho. – Lester Lane Jan 17 '23 at 16:50