Written by Allen Wyatt (last updated January 29, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
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).
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (248) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Combinations for Members in Meetings.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
US ZIP Codes can be of two varieties: five-digits or nine-digits. Here's how to convert longer ZIP Codes to the shorter ...
Discover MoreIf you have a series of values and you want to get a total of just the values that meet a specific criteria, then you ...
Discover MoreWhen you need to pull information from a lot of different worksheets into a single worksheet, it can be baffling to ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-01-30 07:09:58
Peter
There is a formulaic solution.
From a list of members attending each meeting, you can calculate a table that charts the number of meetings that any pair of members attended.
List the meetings in consecutive rows and have a different column for each member. Place a 1 under a member against a meeting that was attended.
The number of meetings attended in common between two members is the sum of the product of the column of 1's for the two members. You can visualise the two columns of 1's and when they are both 1 for the same meeting, that 1 * 1 is added to the sum. If the final sum is Zero, then obviously the two have not met in a meeting.
With suitable use of absolute addresses, the formula can be copied to give the number of meetings in common between one member and all the others.
With a bit of tweaking a complete table can be prepared that shows the meetings in common between all members with the name of each member across the top and down the left. Each cell in this table contains a reference to the attendance list columns for just two members using a range offset from the first column for each member in the attendance list. The two offset values are conveniently placed to the left and above the table of meetings in common and referenced by formula. The offsets start with zero, increase by 1 per row or column and run up to one less than the number of members. The formula for the cell at the intersection of the table for members 4 and 5 (counting from member 0) would be
=SUM(OFFSET(Member0AttendanceColumn,0,4)*OFFSET(Member0AttendanceColumn,0,5)).
In my test of 10 members and 14 meetings, the actual formula for one cell looked like =SUM(OFFSET($C$2:$C$15,0,$A27)*OFFSET($C$2:$C$15,0,C$17)) and was copied to all cells in the table. Note the absolute addressing.
This table is symmetrical about the diagonal and gives the number of meetings attended by each member along that diagonal. If this offends, those formulas can be deleted or hidden in one way or another.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments