Lookup Name in Multiple Fields

[Rcaaa]

Hello,

I am working on a spreadsheet that has the following setup. What would be the best way to find out which projects each person is on? I was thinking of doing a vlookup, but I would like to know if there is a better alternative.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Project
[/TD]
[TD]Resource 1
[/TD]
[TD]Resource 2
[/TD]
[TD]Resource 3
[/TD]
[TD]Resource 4
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Ana
[/TD]
[TD]Rob
[/TD]
[TD]Jim
[/TD]
[TD]Erica
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Rob
[/TD]
[TD]Susan
[/TD]
[TD]Tim
[/TD]
[TD]Kyle
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks,

RCA

 

[alansidman]

With a worksheet that looks like this

[table="class:thin_grid"]
[tr][td]v[/td]
[td="bgcolor:#ECF0F0, align:center"]A[/td]
[td="bgcolor:#ECF0F0, align:center"]B[/td]
[td="bgcolor:#ECF0F0, align:center"]C[/td]
[td="bgcolor:#ECF0F0, align:center"]D[/td]
[td="bgcolor:#ECF0F0, align:center"]E[/td]
[/tr][tr][td="bgcolor:#ECF0F0, align:center"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Project[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Resource 1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Resource 2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Resource 3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Resource 4[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Ana[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Rob[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Jim[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Erica[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Rob[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Susan[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Tim[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Kyle[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Ana[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Rob[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Jim[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Erica[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Rob[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Susan[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Tim[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Kyle[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Ana[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Rob[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Jim[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Erica[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]7[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Rob[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Susan[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Tim[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Kyle[/td]
[/tr]
[/table]

The following code will tell you on Sheet 2 which projects a person is working on when you enter this code and follow the prompts

Option Explicit


Sub projectX()
    Dim lr As Long, lc As Long, c As Range
    Dim rng As Range, lr2 As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    Set rng = Range(Cells(2, 1), Cells(lr, lc))
    Dim crit As String, trow As Long
    crit = InputBox("Which person to search?")
    For Each c In rng
        If c = crit Then
            trow = c.Row
            lr2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
            Range("A" & trow).Copy Sheets("Sheet2").Range("A" & lr2 + 1)    'make sure you have set up a sheet2
        End If
    Next c
End Sub

 

[Rcaaa]

Thanks for sharing. Would you be able to recommend a resource to learn how to create VBA coding similar to this. I would like to learn how to do it for myself.

Thanks,

RCAAA

 

[alansidman]

Check out Mr Excel's books. I used one from 2010 but I am sure he has updated them. Go to the home page for this site.