Solved: Macro to match name & date and move info

[Barryj]

I would like some help to create a macro that will match date in score sheet to date in stat sheet, also match name to stat sheet, if name is not on stat sheet but appears on score sheet then add the name to the stat sheet.

Once the date and names are matched, to take the corrosponding score from the score sheet column C and place it under the matching date.

If a persons name does not have a score next to their name on the score sheet then I want the letters NCR to appear in the stats sheet where the score would have been.

I have included an attachment of how the sheet looks and the result that I am looking for.

Thanks for any intrest.

[Bob Phillips]

Jesus, your data made that hard work. You had 0 for no scores (hidden), and zero in column B after the names (also hidden), and different spellings. You need to clear out the zeroes from column B.

[vba]

Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long
Dim iCol As Long
Dim iRow As Long
Dim stats As Worksheet

Set stats = Worksheets("Stats")

With Worksheets("Score Sheet")

On Error Resume Next
iCol = Application.Match(CLng(CDate(.Range("F4").Value)), stats.Rows(3), 0)
On Error GoTo 0
If iCol > 0 Then

iLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 9 To iLastRow

If .Cells(i, "B").Value <> "" Then

iRow = 0
On Error Resume Next
iRow = Application.Match(.Cells(i, "B").Value, _
stats.Columns(1), 0)
On Error GoTo 0
If iRow > 0 Then
If .Cells(i, "C").Value <> 0 Then
stats.Cells(iRow, iCol).Value = .Cells(i, "C").Value
Else
stats.Cells(iRow, iCol).Value = "NCR"
End If
Else
stats.Cells(stats.Cells(stats.Rows.Count, "A").End(xlUp).Row + 1, "A").Value = .Cells(i, "B").Value
stats.Cells(stats.Cells(stats.Rows.Count, "A").End(xlUp).Row, iCol).Value = "NCR"
End If
End If
Next i
End If
End With

Set stats = Nothing
End Sub
[/vba]

[Barryj]

Thanks for the help Xld, Yes there are hidden zero's in those to columns B & C, is there a way of making this macro ignor the zero's as the data that it is reading is linked to another sheet, other than that it works perfect.

Thanks again for your assistance.

[Barryj]

I was wondering could I further enhance this macro, so that if a name is on the stat sheet but not on the score sheet then DNP will be add to the column under the corrosponding date for that person.

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long
Dim iCol As Long
Dim iRow As Long
Dim stats As Worksheet

Set stats = Worksheets("Stats")

With Worksheets("Score Sheet")

On Error Resume Next
iCol = Application.Match(CLng(CDate(.Range("F4").Value)), stats.Rows(3), 0)
On Error GoTo 0
If iCol > 0 Then

iLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 9 To iLastRow

If .Cells(i, "B").Value <> "" And .Cells(i, "B").Value <> 0 Then

iRow = 0
On Error Resume Next
iRow = Application.Match(.Cells(i, "B").Value, _
stats.Columns(1), 0)
On Error GoTo 0
If iRow > 0 Then
If .Cells(i, "C").Value <> 0 Then
stats.Cells(iRow, iCol).Value = .Cells(i, "C").Value
Else
stats.Cells(iRow, iCol).Value = "NCR"
End If
Else
stats.Cells(stats.Cells(stats.Rows.Count, "A").End(xlUp).Row + 1, "A").Value = .Cells(i, "B").Value
stats.Cells(stats.Cells(stats.Rows.Count, "A").End(xlUp).Row, iCol).Value = "NCR"
End If
End If
Next i
End If
End With

With stats

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 5 To iLastRow
If .Cells(i, iCol).Value = "" Then .Cells(i, iCol).Value = "DNP"
Next i
End With

Set stats = Nothing
End Sub
[/vba]

[Barryj]

Thank you xld, that works fantastic, very much appreciated, thank you again for your assistance.