Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Combinations for Members in Meetings

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Combinations for Members in Meetings.

Bob has a worksheet that has member names down the left side and months of the year across the top. In each cell of the grid he enters the dates on which meetings occur that were attended by the member. Bob is looking for a way to tell at a glance who has not met with whom.

There are several ways that a solution to this problem can be approached. If your table design is flexible, you can "simplify" things by changing the way your table is laid out. Instead of putting months across the columns, you can simply have each column be a meeting date. Then, each cell could contain some sort of indicator (a number or a character) that indicates the person attended the meeting on that particular date. It would be a relatively easy process to figure out who had not met with whom:

  1. Choose the key member, the one you want to check, and move him/her to the top of your data table.
  2. Sort the data table horizontally on the key member row, so all the meetings that the key member attended are in the left-most columns.
  3. Sort everyone except the key member vertically on the first three meeting dates. Everyone who met the key member in those three meetings is now at the top of the data table, just below the key member.
  4. Move down the data table and select everyone who has not yet met the key member and sort on the next three meeting dates.
  5. Repeat steps 3 and 4 until all meeting dates have been sorted.
  6. Everyone remaining at the bottom of the data table (those not selected in steps 3 and 4) has never met the key member.

If you cannot change the format of your table, then a macro solution is called for. There are many approaches that could be used in a macro, but the following is perhaps the most direct:

Sub PeopleNotMet()
    Dim rTable As Range
    Dim rOutput As Range
    Dim iCols As Integer
    Dim iCol As Integer
    Dim iRows As Integer
    Dim iRow As Integer
    Dim iCompRow As Integer
    Dim sNotMet As String
    Dim sMet As String

    Set rTable = Worksheets("Sheet1").Range("A1").CurrentRegion
    Set rOutput = Worksheets("Sheet2").Range("a1")
    sNotMet = "X"
    sMet = ""

    Application.ScreenUpdating = False
    With rTable
        iRows = .Rows.Count
        iCols = .Columns.Count
        
        .Columns(1).Copy
        With rOutput
            .PasteSpecial
            .PasteSpecial Transpose:=True
            Application.CutCopyMode = False
            Range(.Offset(1, 1), .Offset(iRows - 1, _
              iRows - 1)).Value = sNotMet
            Range(.Offset(1, 1), .Offset(iRows - 1, _
              iRows - 1)).HorizontalAlignment = xlCenter
        End With
    End With
    With rTable.Cells(1)
        For iRow = 1 To iRows - 1
            For iCol = 1 To iCols - 1
                For iCompRow = 1 To iRows - 1
                    If Not (IsEmpty(.Offset(iRow, iCol))) Then
                        If Not (IsEmpty(.Offset(iCompRow, iCol))) Then
                            If .Offset(iRow, iCol).Value = _
                              .Offset(iCompRow, iCol).Value Then _
                              rOutput.Offset(iRow, iCompRow).Value = sMet
                        End If
                    End If
                Next
            Next
        Next
    End With

    Set rTable = Nothing
    Set rOutput = Nothing
    Application.ScreenUpdating = True
End Sub

This macro assumes a couple of things. First, it assumes that Bob's original data table is on Sheet1, starting in cell A1. Second, it assumes that the "who has not met with whom" table should be on Sheet2, beginning at cell A1. If these assumptions are correct, then when you run the macro, the table created on Sheet2 shows names down the left side and names across the top. The intersecting cells will contain either nothing (which means that the people have met) or a capital X (which means they have not met).

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2304) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Combinations for Members in Meetings.

Related Tips:

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.