Lookup a number, return a name

[bobbybrown]

Hi all,
I’m working on a simple sheet where I have a few columns. The important ones are:
A = first name
B = surname
F = points

On another sheet within the same workbook I am trying to get it to display any person who has 3 or more points.
I want it to display first name, surname and points if they have 3 or more points.
This sheet is a summary sheet if that makes sense.

I’ve tried with Vlookup but I just keep getting an error or a 0, so thought I should ask the experts for guidance.

Many thanks

 

[alansidman]

Option Explicit


Sub BobbyBrown()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("ABF")    'Change name to your sheet name
    Set s2 = Sheets("Summary")    'Change name to your summary Sheet name
    Dim i As Long, lr As Long, lr2 As Long
    Application.ScreenUpdating = False
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    With s1
        For i = 1 To lr
            lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row + 1
            If .Range("F" & i) >= 3 Then
                Application.Union(.Range("A" & i), .Range("B" & i), .Range("F" & i)).Copy
                s2.Range("A" & lr).PasteSpecial xlPasteValues
            End If
        Next i
    End With
   Application.CutCopyMode = False
   Application.ScreenUpdating = True
   MsgBox "completed action"

    End Sub

 

[bobbybrown]

Thanks I’ll give that a try!
It will eventually be going on to google sheets as that is what my employer uses, if they don’t support the code is there a way to do the same job with formulas?

I’ll let you know if that works.

 

[bobbybrown]

It is saying completed action, but the summary sheet is blank.
Columns ABC are highlighted but there’s nothing in them.

 

[alansidman]

Suggest you post your worksheet to a location like Box.net or Dropbox for testing. Cannot help without actually seeing your worksheet and determining the issue.

This line of code s2.Range("A" & lr).PasteSpecial xlPasteValues

needs to read s2.Range("A" & lr2).PasteSpecial xlPasteValues

My apologies

 

[MARZIOTULLIO]

Hi

Can you provide some sample data and what the expected outcome is?

 

[bobbybrown]

Sample worksheet is on the way.
What I am trying to do is get the first name, surname and amount of sickness occasions to display on the summary sheet.
It should become clear once you see the sheets.

 

[bobbybrown]

https://www.dropbox.com/s/za352z5wnnhej57/demo sickness tracker.xlsx?dl=0

 

[MARZIOTULLIO]

	B​	C​	D​
2​	Colleagues with 3 or more sickness within 6 months
3​
4​	First Name​	Surname​	Occasions of sickness​
5​	Name 3	Surname 3	3​
6​	Name 6	Surname 6	4​
7​	Name 10	Surname 10	4​
8​
9​
10​
11​
12​
13​
14​
15​
16​
17​
18​

<tbody>
</tbody>

B5=IFERROR(INDEX('Sickness Tracker'!A$6:A$129,SMALL(IF('Sickness Tracker'!$F$6:$F$129>=3,ROW('Sickness Tracker'!A$6:A$129)-ROW('Sickness Tracker'!A$6)),ROWS('Sickness Tracker'!A$6:'Sickness Tracker'!A6))),""

control+shift+enter

copy across and down

 

[bobbybrown]

That is so close! It seems to be copying the row above. If name 4 should be copied then it is copying name 3