Searching Multiple Times

[butlerr]

In column 'A' I have a list of 10 different people, but there are 130 entries, so a lot of repeating.

In column 'B' I have a list a items each person has chosen (nothing repeating)

Now in, say, column 'C' I want to list all items chosen by one particular person.

I can search for a name and find their first item, but I don't know how to find the 2nd occurrence of that persons name and entering its choice, and so on.

This is what I have for the first entry:
=VLOOKUP(G1,DraftingPlayer,2,FALSE)

"DraftingPlayer" is the name range for column A and B.
G1 is a cell that contains one of my names (Brett)

Any help would be much appreciated!

 

[sulakvea]

hi, and welcome to the board.

what do you mean by "I want to list all items chosen by one particular person." - first of all, how do you choose a person (say enter his/her name in C1?) and then, how do you want those items displayed? all of them separated by comma in C2, or in a column C2 thru say C10, or however long it is?

 

[butlerr]

thanks for the welcoming,

I would like the results listed in a column, C2 to C10

Maybe an example will help my explanation:

A----------B-----------C (labelled "Brett")
NAME------ITEM-------BRETT-------BRAD
Brett------Apple-------Apple--------Pear
Brad------Pear--------Banana-------Grape
Rick------Pickle
Rich------Orange
Brett-----Banana
Erik-------Lemon
Brad------Grape


Hopefully that makes sense!

 

[sulakvea]

it can be done with Array formulas, but i would rather use VBA. mind if i try?

so once again - lets assume you enter the persons name in C1, and you want C2 thru whatever it takes to be populated with corresponding items right

 

[butlerr]

oh and if possible without using VBA......I actually don't even know what it is. I'm willing to learn, but giving me a code for VBA would not help at this moment in time. If it's my only option, do you suggest somewhere for a beginners lesson on VBA?

 

[butlerr]

it can be done with Array formulas, but i would rather use VBA. mind if i try?

so once again - lets assume you enter the persons name in C1, and you want C2 thru whatever it takes to be populated with corresponding items right

yes you're right, go ahead with VBA if thats what it takes. I'll work around it.

Thanks!

 

[sulakvea]

here's the code. goes into Sheet1 Module level - i.e. in VBA editor, left-hand side double click Sheet1 and paste this code. it's tied to Worksheet Change event - so once smth changes in the sheet (i.e. C1 is updated) it will recalculate.

HTH

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
i = 2
With Sheet1.Range("A1:A" & LR)
 Set c = .Find(Sheet1.Range("C1").Value, LookIn:=xlValues, lookat:=xlWhole)
 If Not c Is Nothing Then
  firstaddress = c.Address
  Do
   Sheet1.Cells(i, 3).Value = c.Offset(0, 1).Value
   i = i + 1
   Set c = .FindNext(c)
  Loop While Not c Is Nothing And c.Address <> firstaddress
 End If
End With
Application.ScreenUpdating = True
End Sub

 

[butlerr]

ahh I think this is way over my head. I got it to work with my example, but now editing it to mine exact file is proving to be challenging. But at least I've got something to work with now, I should be able to figure it out with time.

Thanks for all your help!

 

[sulakvea]

here's what you would need to change in case sheet names and ranges are different.
Red = Sheet Name
Blue = Range names (Columns)
Green = Input Cell (C1)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
LR = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
i = 2
With Sheet1.Range("A1:A" & LR)
 Set c = .Find(Sheet1.Range("C1").Value, LookIn:=xlValues, lookat:=xlWhole)
 If Not c Is Nothing Then
  firstaddress = c.Address
  Do
   Sheet1.Cells(i, 3).Value = c.Offset(0, 1).Value
   i = i + 1
   Set c = .FindNext(c)
  Loop While Not c Is Nothing And c.Address <> firstaddress
 End If
End With
Application.ScreenUpdating = True
End Sub

 

[butlerr]

Hmm, still can't get it. And it keeps freezing my program.

Sheet2 (named "Draft & Trades") contains everyones names and picks.
Names are listed in Sheet2 C3:C132 and their picks are in D3:D132.

Sheet3 (named "Teams") is where I would like the results. The persons name is in cell A1 (I've written it manually). I would like the first results to appear starting in A7, A8, A9, (up until at least A23).

This is what I wrote, but its obviously wrong

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
LR = Sheet2.Range("C" & Rows.Count).End(xlUp).Row
i = 2
With Sheet2.Range("C3:C" & LR)
Set c = .Find(Sheet3.Range("A7").Value, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
firstaddress = c.Address
Do
Sheet3.Cells(i, 3).Value = c.Offset(0, 1).Value
i = i + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
Application.ScreenUpdating = True
End Sub