locked
How to automate a search for data and populate a second spreadsheet with results. RRS feed

  • Question

  • Hi! I have a nut I can't seem to crack.

    I have an excel spreadsheet which contains staff skills data. I want to use the information in this spreadsheet to populate another spreadsheet with staff information wherever a staff member has reached the top level of qualification. Qualification levels are assigned a number from 0 to 4, with 4 being the highest qualification level obtainable. see example of the spreadsheet below:

    Name Position Skill 1 Skill 2 Skill 3 Skill 4 Skill 5
    Jane Smith Team Manager 1 4 2 3 4
    John Doe Clerk 0 1 4 3 2
    Alfred Wilson Secretary 0 3 3 2 1

    In this example, every time a staff member has reached a level 4 qualification, I want their name and information to appear a second spreadsheet as follows:

    Name Position Qualification
    Jane Smith Team Manager Skill 2
    Jane Smith Team Manager Skill 5
    John Doe Clerk Skill 3

    Any ideas on how I can go about this? I thought VLOOKUP might work, but most of the information I've been able to find on how to use it hasn't gone into this level of complexity. Basically, I want excel to automatically search spreadsheet 1 for the value "4" and everytime a result is found, populate the spreadsheet 2 with the staff information.

    Thanks!

    Monday, July 20, 2020 7:25 AM

Answers

  • I'd populate the second sheet when it is activated.

    Right-click the sheet tab of the second sheet.

    Select 'View Code' from the context menu.

    Copy the following code into the worksheet module:

    Private Sub Worksheet_Activate()
        Dim wsh As Worksheet
        Dim s As Long
        Dim m As Long
        Dim c As Long
        Dim n As Long
        Dim t As Long
        Application.ScreenUpdating = False
        Range("A2:C" & Rows.Count).ClearContents
        t = 1
        Set wsh = Worksheets("Skills") ' change as needed
        m = wsh.Cells(wsh.Rows.Count, 1).End(xlUp).Row
        n = wsh.Cells(1, Columns.Count).End(xlToLeft).Column
        For s = 2 To m
            For c = 3 To n
                If wsh.Cells(s, c).Value = 4 Then
                    t = t + 1
                    Cells(t, 1).Value = wsh.Cells(s, 1).Value
                    Cells(t, 2).Value = wsh.Cells(s, 2).Value
                    Cells(t, 3).Value = wsh.Cells(1, c).Value
                End If
            Next c
        Next s
        Application.ScreenUpdating = True
    End Sub

    Change "Skills" to the name of the first worksheet.

    Switch back to Excel.

    Save the workbook as a macro-enabled workbook (.xlsm).

    Make sure that you allow macros when you open the workbook.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by KiahM Tuesday, July 28, 2020 4:43 AM
    Monday, July 20, 2020 9:45 AM

All replies

  • I'd populate the second sheet when it is activated.

    Right-click the sheet tab of the second sheet.

    Select 'View Code' from the context menu.

    Copy the following code into the worksheet module:

    Private Sub Worksheet_Activate()
        Dim wsh As Worksheet
        Dim s As Long
        Dim m As Long
        Dim c As Long
        Dim n As Long
        Dim t As Long
        Application.ScreenUpdating = False
        Range("A2:C" & Rows.Count).ClearContents
        t = 1
        Set wsh = Worksheets("Skills") ' change as needed
        m = wsh.Cells(wsh.Rows.Count, 1).End(xlUp).Row
        n = wsh.Cells(1, Columns.Count).End(xlToLeft).Column
        For s = 2 To m
            For c = 3 To n
                If wsh.Cells(s, c).Value = 4 Then
                    t = t + 1
                    Cells(t, 1).Value = wsh.Cells(s, 1).Value
                    Cells(t, 2).Value = wsh.Cells(s, 2).Value
                    Cells(t, 3).Value = wsh.Cells(1, c).Value
                End If
            Next c
        Next s
        Application.ScreenUpdating = True
    End Sub

    Change "Skills" to the name of the first worksheet.

    Switch back to Excel.

    Save the workbook as a macro-enabled workbook (.xlsm).

    Make sure that you allow macros when you open the workbook.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by KiahM Tuesday, July 28, 2020 4:43 AM
    Monday, July 20, 2020 9:45 AM
  • Perfect! Worked a charm, thanks!
    Tuesday, July 28, 2020 4:43 AM
  • Are you able to tell me what each of the variables in the code you have provided (s, m, c, n and t) are referring to?  It will help me to understand how to use similar code in future. Thanks!
    Tuesday, July 28, 2020 5:30 AM
  • wsh is the Skills worksheet.

    m is the row number of the last non-blank cell in column A on the Skills sheet.

    We loop through column A of this sheet using

    For s = 2 To m

    s is the row number that varies from 2 to m.

    n is the column number of the last non-blank cell in row 1 of the Skills sheet.

    We loop through the columns of this sheet in row s using

    For c = 3 To n

    c is the column number that varies from 3 (for column C) to n.

    t is the row number on the sheet we're populating. It starts out at 1, and we increment it by 1 each time we find skill level 4 using the line

    t = t + 1


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Tuesday, July 28, 2020 6:55 AM
  • Thanks again!  Appreciate you taking the time to help and explain this, it will be very useful :)
    Wednesday, July 29, 2020 5:39 AM