PDA

View Full Version : Solved: Match across 2 columns



lifeson
02-04-2009, 02:39 AM
Hi folks, great to see the site back up & running :clap:

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:
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


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

GTO
02-04-2009, 03:32 AM
Greetings lifeson,

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

Mark

lifeson
02-04-2009, 03:51 AM
Greetings lifeson,

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

Mark

:o: :o:
in the words of Homer Simpson "Doh!"

lifeson
02-04-2009, 10:08 AM
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 : pray2: : pray2:

Sagy
02-04-2009, 04:21 PM
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
02-08-2009, 01:05 AM
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

:thumb

Bob Phillips
02-08-2009, 03:20 AM
You could use an array formula like so

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