Copy Columns of a row if I type a surname that already has an entry on the same spreadsheet

[Clelv]

Hi everyone,

I have a spreadsheet on which I keep an attendance record. It looks like the example below.

Every week I update the list with approx 20 names and i'm looking at finding a shortcut to my usual method of filtering the surname column and then copying the entry if it already exist's, pasting the row on the next clear line at the bottom. This is very time consuming!

[TABLE="width: 558"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Client Number[/TD]
[TD]Visit Number [/TD]
[TD]Forename[/TD]
[TD]Surname[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]22[/TD]
[TD]1[/TD]
[TD]Anon[/TD]
[TD]Anon[/TD]
[TD]05/01/2017[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]22[/TD]
[TD]1[/TD]
[TD]Anon[/TD]
[TD]Anon[/TD]
[TD]01/01/2017[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]215[/TD]
[TD]4[/TD]
[TD]Justin[/TD]
[TD]Case[/TD]
[TD]05/01/2017[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]44[/TD]
[TD]2[/TD]
[TD]Michael[/TD]
[TD]Madeup[/TD]
[TD]24/12/2016[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]44[/TD]
[TD]2[/TD]
[TD]Michael[/TD]
[TD]Madeup[/TD]
[TD]20/12/2016[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]44[/TD]
[TD]2[/TD]
[TD]Michael[/TD]
[TD]Madeup[/TD]
[TD]16/12/2016[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]44[/TD]
[TD]2[/TD]
[TD]Michael[/TD]
[TD]Madeup[/TD]
[TD]15/12/2016[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]123[/TD]
[TD]1[/TD]
[TD]Norman[/TD]
[TD]Noone[/TD]
[TD]28/12/2016[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]445[/TD]
[TD]2[/TD]
[TD]Patty[/TD]
[TD]Pretend[/TD]
[TD]28/12/2016[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]445[/TD]
[TD]2[/TD]
[TD]Patty[/TD]
[TD]Pretend[/TD]
[TD]24/12/2016[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]445[/TD]
[TD]2[/TD]
[TD]Patty[/TD]
[TD]Pretend[/TD]
[TD]20/12/2016[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

My aim is too just be able to type in the surname (which is given to me by a colleague from her own spreadsheet, No identifying Client number unfortunately).

The surname goes into column D on the next available row. So in this example in cell D13 and then the spreadsheet automatically fills in Column A, B and C if an entry already exists for a previous attendance date.

I have looked into Index-Match and Vlookup and have very very basic Macro knowledge. I really appreciate all you kind help.

p.s Also just to be cheeky if anyone knows any links to any other way of recording attendance in a workable spreadsheet i'd be interested to look! I inherited this format as an excel novice & although it works well the spreadsheet get very large and as i say are time consuming to fill in.

Thanks again

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a surname in column D and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    Dim foundName As Range
    Set foundName = Range("D:D").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundName Is Nothing Then
        Range("A" & foundName.Row & ":C" & foundName.Row).Copy Cells(Target.Row, 1)
    End If
End Sub

 

[Clelv]

Thank you Mumps,

I must be doing something wrong though. I copied the macro in, I typed the surname "Case" into D13 and it thought about it but nothing happened, I was expected Cell A13 to populate with "215", B13 with "4" and C13 with "Justin". I resaved the sheet as macro enabled in case that mattered but it still didn't work. Sorry if I'm missing something blatantly obvious! Claire

Please ignore this!!!

It was me being daft.

It works perfectly!!!

Thanks again Mumps

 

[Clelv]

There's always one more question sorry...

I just wondered if you could do this for copying a pasting a block of surnames into Column D rather that just individually?

It doesn't matter if not though as this has already cut my data entry time right down so I'm more than chuffed

 

[mumps]

What do you mean by "a block of surnames "? Can you use some examples?

 

[Clelv]

For instance to paste a block of surnames (as below) into D13, D14, D15, D16 & D17 and the any matches populate the first 3 columns as before? The reason I ask is because they already come to me on a spread sheet so I just need to copy the block and put them into my sheet.[TABLE="width: 83"]
<colgroup><col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;"> <tbody>[TR]
[TD="class: xl63, width: 111, bgcolor: transparent"]

Anon[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Case[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Madeup[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Noone[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Pretend

Thanks Claire[/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    Dim myRange As Range
    Dim myCell As Range
    Set myRange = Target
    Dim foundName As Range
    For Each myCell In myRange.Cells
        Set foundName = Range("D:D").Find(myCell, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundName Is Nothing Then
            Range("A" & foundName.Row & ":C" & foundName.Row).Copy Cells(myCell.Row, 1)
        End If
    Next myCell
End Sub

 

[Clelv]

Wow. That works. That's super clever Thanks Again Mumps, that was a half hour job that now takes about 4 clicks.

 

[mumps]

You are very welcome.