Building a Meeting Schedule from Data

[rjbinney]

I suspect I've read lots of answers that are close, but I'm still not figuring it out. I keep trying various Pivots, Indexes, and Sumproducts, and still not quite getting what I want.

My data is simple:

Column 1 - Person
Column 2 - Division
Column 3 - Scheduled Day of Group Meeting

(In other words:
Donaghy, Finance, Wednesday
Draper, Creative, Tuesday
Schrute, Sales, Tuesday
Simpson, QC, Wednesday
Smith, Marketing, Wednesday)

I'd like to automatically run a report that will spit out:

TUESDAY
-------------
Draper
Schrute

WEDNESDAY
-----------
Donaghy
Simpson
Smith

Thoughts?

 

[ChrisM]

Seems like this should be a pivot table with only two row items, nothing else:

row 1 - date
row 2 - name

Are you sure that won't work?

 

[rjbinney]

Seems like this should be a pivot table with only two row items, nothing else:

row 1 - date
row 2 - name

Are you sure that won't work?

It was giving me counts of people, not lists.


ETA: No, I'm not sure that WON'T work. I am sure that I haven't gotten it to work!

 

[ChrisM]

Yes, but that's if you put a field in the "Value" section of the pivot table, I'm talking about just the Row section. Don't put anything in the value section at all.

This is what I get out of a pivot table (just pasted it, you can't see the formatting):

Row Labels
Tuesday
Draper
Schrute
Wednesday
Donaghy
Simpson
Smith
Grand Total

 

[Desu Nota from Columbus]

Do you have anything else on this sheet? In columns D-Z?

 

[rjbinney]

Do you have anything else on this sheet? In columns D-Z?

Yes, but not important for this calendar. (Things like phone number, manager name, blood type, direct reports, mail stop, etc.)

I know I'm missing something totally basic here, but my brain is wrapped around a tree.

 

[Desu Nota from Columbus]

You can use this macro to see the answers in column form: (there are better ways to do this, but this is the best I can do)

Sub Schedule()

 Dim i As Integer
 Dim r As Long
 

Application.ScreenUpdating = False
With Sheets("Sheet1")
Cells(1, 26).Value = "Monday"
Cells(1, 27).Value = "Tuesday"
Cells(1, 28).Value = "Wednesday"
Cells(1, 29).Value = "Thursday"
Cells(1, 30).Value = "Friday"

For i = 2 To 1000
    Cells(i, 26).FormulaR1C1 = "=IF(ISERROR(FIND(""Monday"",RC3)),0,RC1)"
Next i
For i = 2 To 1000
    Cells(i, 27).FormulaR1C1 = "=IF(ISERROR(FIND(""Tuesday"",RC3)),0,RC1)"
Next i
For i = 2 To 1000
    Cells(i, 28).FormulaR1C1 = "=IF(ISERROR(FIND(""Wednesday"",RC3)),0,RC1)"
Next i
For i = 2 To 1000
    Cells(i, 29).FormulaR1C1 = "=IF(ISERROR(FIND(""Thursday"",RC3)),0,RC1)"
Next i
For i = 2 To 1000
    Cells(i, 30).FormulaR1C1 = "=IF(ISERROR(FIND(""Friday"",RC3)),0,RC1)"
Next i
For r = 1000 To 2 Step -1
    If Range("Z" & r).Value = 0 Then
        Range("Z" & r).Delete Shift:=xlUp
    End If
Next r
For r = 1000 To 2 Step -1
    If Range("AA" & r).Value = 0 Then
        Range("AA" & r).Delete Shift:=xlUp
    End If
Next r
For r = 1000 To 2 Step -1
    If Range("AB" & r).Value = 0 Then
        Range("AB" & r).Delete Shift:=xlUp
    End If
Next r
For r = 1000 To 2 Step -1
    If Range("AC" & r).Value = 0 Then
        Range("AC" & r).Delete Shift:=xlUp
    End If
Next r
For r = 1000 To 2 Step -1
    If Range("AD" & r).Value = 0 Then
        Range("AD" & r).Delete Shift:=xlUp
    End If
Next r
End With

 Application.ScreenUpdating = True

End Sub

This macro creates 5 column headers Mon-Fri in columns Z-AD. It then places each person under the correct day of their meeting. As long as there aren't blanks in your data in A and C, it works.

And I should mention this macro as it is right now, only searches 1000 rows. If your data pool is >1000 then everywhere a 1000 exists needs to be changed to suit your needs (that is if you even use this macro!! )

 

[ChrisM]

I said to put Date on row 1 and name on row 2. You did it backwards. Reorder those.

 

[sugrue]

Yes, but not important for this calendar. (Things like phone number, manager name, blood type, direct reports, mail stop, etc.)

I know I'm missing something totally basic here, but my brain is wrapped around a tree.

Just reverse the order of the fields in the Row Labels - Put Day before Name and it should give you what you want.

 

[rjbinney]

I said to put Date on row 1 and name on row 2. You did it backwards. Reorder those.

Just reverse the order of the fields in the Row Labels - Put Day before Name and it should give you what you want.

I believe the word I'm looking for is, "Oh. Duh."

Thanks, guys.

I KNEW it was easy. I just couldn't get there.