Unique list of visitors

Arranging data sets in Excel reports can sometimes get complicated, for example when you are editing a list of visitors to your stand at a business fair or congress. Then we usually manually copy the lists, merge them, and further remove duplicates. Excel has some new functions that allow you to literally do this procedure in a couple of steps, and in the following “recipe” you will find out how to apply them in practice.

We will start with a table consisting of three columns. Each of them contains the names and surnames of people who visited our stand in a period of three days. How to find a unique list of visitors? First, it is necessary to place the data about the visitors (first and last name) in one column. We achieve this with a formula within which we will apply the TOCOL function:

=TOCOL(A2:C51,1)

Since we want a unique list, we should remove duplicates. For this purpose, the function TOCOL should be placed within the function UNIQUE. Let’s complete the formula:

=UNIQUE(TOCOL(A2:C51,1))

Finally, it would be nice if the values were arranged alphabetically in ascending order. The final formula looks like this:

=SORT(UNIQUE(TOCOL(A2:C51,1)))

As you can see, we got a unique list of visitors.

Has anyone visited the stand more than once? We can find this out using the formula:

=COUNTIF(A:A,$E2)

It should be added to the right of the cell where the names are, then copied to the three next cells to the right, and then to the remaining cells in the table. We will add ordinal day numbers description to the header.

Now we see that some visitors visited the stand several times in one day or several days in a row. If we want to mark such visitors, the easiest way is to use Conditional Formatting. After selecting all the data in the range, you should create a new Conditional Formatting rule based on the formula. We will color background of each cell to yellow where the sum, in the three days of the event, is greater than 1. When we set and apply the conditional formatting rule, each row will be colored where the condition is met.