Working with lists

[JSnader]

I have a list with several columns:
PARENT CHILD PHONE AGE NOTES...

I would like a dropdown with the parents listed one time, that would select
all their children and show the related information.

I would like a second dropdown with just their children listed so I can pick
the data from just that child.

1. I'm stumped on populating the first dropdown, listing the parents only
one time.
2. I think there should be a much better way then this code to select the
duplicate parents.

Sub pick()
'
' pick Macro
' Macro recorded 11/22/03 by James'

' MyData has the Parent's name
Sheets("Sheet2").Select
Range("e2").Select
MyData = ActiveCell.Text


' Col A has Parent's name, Col B has the Child's name
Sheets("Sheet1").Select
Range("a1").Select

' Sort data to group parents
Application.Goto Reference:="Sample"
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select

'Find 1st incident of Parent
Do While ActiveCell.Text <> MyData
ActiveCell.Offset(1, 0).Select
Loop

Set first = ActiveCell

' Find last incident of Parent
Do While ActiveCell.Text = MyData
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(-1, 15).Select
Set last = ActiveCell

Range(first, last).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A10").Select
ActiveSheet.Paste
Range("A10").Select


End Sub

 

[Dave Peterson]

It kind of sounds like you could almost accomplish what you want with
Data|Filter|autofilter.

You could filter by the parent's name to just show the family that you want.
When you use the dropdown on the child field, only those names that belong with
the visible rows will be available.

If that doesn't appeal to you, John Walkenbach has some sample code at:
http://j-walk.com/ss/excel/tips/tip47.htm

That builds a list of unique values using a collection and then sorts that
collection.

He uses it to populate a listbox, but you should be able to modify that.

I guessed that you were using a userform.

I copied John's code and this worked ok for me:

Option Explicit
Private Sub ComboBox1_Change()

Dim ChildRng As Range
Dim myCell As Range
Me.ComboBox2.Clear

If Me.ComboBox1.ListIndex > -1 Then
With Me.ComboBox2
With Worksheets("sheet1")
Set ChildRng = .Range("b2:B" & _
.Cells(.Rows.Count, "B").End(xlUp).Row)
End With
For Each myCell In ChildRng.Cells
If LCase(myCell.Offset(0, -1).Value) _
= LCase(Me.ComboBox1.Value) Then
.AddItem myCell.Value
End If
Next myCell
End With
End If

End Sub
Private Sub UserForm_Initialize()

With Me.ComboBox1
.Style = fmStyleDropDownList
End With

With Me.ComboBox2
.Style = fmStyleDropDownList
End With

Dim AllCells As Range
Dim myCell As Range
Dim NoDupes As New Collection
Dim i As Long
Dim j As Long
Dim Swap1 As Variant
Dim Swap2 As Variant
Dim myItem As Variant

With Worksheets("sheet1")
Set AllCells = .Range("A2:A" _
& .Cells(.Rows.Count, "A").End(xlUp).Row)

On Error Resume Next
For Each myCell In AllCells.Cells
NoDupes.Add myCell.Value, CStr(myCell.Value)
Next myCell
On Error GoTo 0

' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, before:=j
NoDupes.Add Swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

For Each myItem In NoDupes
Me.ComboBox1.AddItem myItem
Next myItem
End With
End Sub