**Please Note: **
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), *this tip may not work for you*. For a version of this tip written specifically for earlier versions of Excel, click here: Combinations for Members in Meetings.

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:

- Choose the key member, the one you want to check, and move him/her to the top of your data table.
- 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.
- 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.
- Move down the data table and select everyone who has not yet met the key member and sort on the next three meeting dates.
- Repeat steps 3 and 4 until all meeting dates have been sorted.
- 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).

*Note:*

If you would like to know how to use the macros described on this page (or on any other page on the *ExcelTips* sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

*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**.

**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!

If you define your named ranges after you create your formulas, you can have Excel update those formulas to reflect the ...

Discover MoreIf you have a range of numbers that contain both integers and decimal numbers, you may have a need to determine the ...

Discover MoreThe FIND and SEARCH functions are great for finding the initial occurrence of a character in a text string, but what if ...

Discover More**FREE 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

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