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.
by Allen Wyatt
(last updated June 25, 2018)
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:
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.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
You can hide a bunch of worksheets at the same time, but Excel makes it impossible to unhide a bunch at once. You can, ...Discover More
As your macro processes information in a worksheet, you may want to make sure that it skips over rows that are hidden. ...Discover More
If you've got a list of potential words, and you want to know which of those potential words are real, you'll appreciate ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.