Solved: Match across 2 columns

[lifeson]

Hi folks, great to see the site back up & running

I have a workbook that tracks weekly sales figures and I want add some check for duplicate entries

i.e.
Salesman 1 enters his figures for week 1
if salesman 1 then trys to enter his figures again for week 1 that is flagged as a duplicate.

A prompt should then ask if he wants to overwrite his figures

This is where I have got to:
[vba]If .Cells(rw, "A") <> Empty Then
'get the items to check
sName = .Cells(rw, 1).Value
weekNo = .Cells(rw, 6).Value
'********************************************************
'check for a duplicate record on sheet "AllRecords"
With wsH

Set rFound = wsH.Columns.Find(sName, lookat:=xlWhole)
If rFound Is Nothing Then
Application.StatusBar = sName & " is not in the history OK to add"
newRow = True
Call AddToHistory(rw, newRow, 0, wsS)
Else

fndRow = rFound.Row
'MsgBox sName & " exists on row " & fndRow & ", checking for weekno " & weekNo
If wsS.Cells(fndRow, 6) <> weekNo Then
Application.StatusBar = sName & "Has a record but not at week no " & weekNo
newRow = True
Call AddToHistory(rw, newRow, 0, wsH)
Else
msg = sName & " Already has a record for week number " & weekNo & vbNewLine
msg = msg & "Do you want to overwrite the history?"
ans = MsgBox(msg, vbYesNo, "Duplicate history on row: " & fndRow)
If ans = vbYes Then
newRow = False
Call AddToHistory(rw, newRow, fndRow, wsH)
Else
'No selected
End If 'Yes/No
End If 'weekNo check
End If 'name check
'format and protect sheet...

End With 'wsH
Else
End If
Next rw
[/vba]

which works to an extent but as more records are added it doesnt pick up the duplicate entrys correctly

[GTO]

Greetings lifeson,

Ahem... maybe you want to remove the password from the project?

Mark

[lifeson]

Greetings lifeson,

Ahem... maybe you want to remove the password from the project?

Mark

in the words of Homer Simpson "Doh!"

[lifeson]

I am still struggling with this one but I think the answer lies using INDEX and MATCH



Fred may appear in column A many times and a week number may appear in Column F many times

Basically I am trying to find a row number where Fred in "A" and week number 6 in "F" match

[Sagy]

How about creating a new column which will be the the concatenation of the columns "A" & "F". Once you have this you can to see if the new entry's "Salesman" & "Week" already appear in the new column (using Index). If index returns a number than this number is the row of the "duplicate" entry, if it doesn't then this is a new entry.

[lifeson]

How about creating a new column which will be the the concatenation of the columns "A" & "F". Once you have this you can to see if the new entry's "Salesman" & "Week" already appear in the new column (using Index). If index returns a number than this number is the row of the "duplicate" entry, if it doesn't then this is a new entry.

Thanks Sagy
I wanted to avoid adding an extra check column but in the end I did use your method

[Bob Phillips]

You could use an array formula like so

=INDEX(CA2:C200,MATCH(1,(A2:A200="Fred")*(F2:F200=6),0))