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]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Colleagues with 3 or more sickness within 6 months[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

First Name​

[/TD]
[TD]

Surname​

[/TD]
[TD]

Occasions of sickness​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Name 3[/TD]
[TD]Surname 3[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Name 6[/TD]
[TD]Surname 6[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Name 10[/TD]
[TD]Surname 10[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

18​

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



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