Generating a List of Customers and Transaction Dates

Brenda has a worksheet that contains customer transaction information from the past 18 months. The data requires many rows and columns. Using this data as the source, she needs to generate a list of customer names (from column A) and transaction dates (from column D) that are unique. Thus, a customer could easily appear multiple times in the generated list, but never would the same customer appear more than once on the same date.

As with many tasks in Excel, the program provides multiple ways that you can get what you want. For the first approach, I'm going to assume that Brenda is using Microsoft 365. In that case, you could use the UNIQUE function after combining the contents of columns A and D, in this manner:

=UNIQUE(A2:A3000 &" " & TEXT(D2:D3000,"mm/dd/yy"))

This formula assumes that your data starts in row 2 and doesn't extend more than 3,000 rows. The result is a single column of text values where the name and date are combined. The drawback to this simple approach, though, is that the results are combined into a single column. If you want separate columns, then you can add the FILTER function into the mix:

=UNIQUE(FILTER(CHOOSE({1,2},A2:A3000,D2:D3000),(A2:A3000<>"")*(D2:D3000<>"")))

This formula is a good deal longer, but it provides more usable results. You end up with two columns consisting of the names and transaction dates. You will, however, need to add date formatting to the second column that is generated.

If you want a shorter formula, you can rely on the HSTACK function, which returns a horizontal "stack" (array) of values:

=UNIQUE(HSTACK(A1:A3000,D1:D3000))

You end up with two columns (the names and the transaction dates), plus it returns the column headers. You will need to apply date formatting to the second returned column, as well. To my mind, you can make the formula even better by throwing the TRIMRANGE function into the mix:

=UNIQUE(HSTACK(TRIMRANGE(A:A),TRIMRANGE(D:D)))

Now it doesn't matter how many rows you use in your data, the formula will return the unique combination of names and transaction dates that Brenda wants.

If Brenda is using an older version of Excel, then one quick way to get the desired list is to make a copy of the worksheet and, within the copy, use Remove Duplicates. Select a cell in your data, display the Data tab of the ribbon, and click the Remove Duplicates tool. Excel displays the Remove Duplicates dialog box where you can select which columns should be used to determine duplicate rows. Make sure that the Name and Date columns are the only ones selected, then click OK. You can then delete any unnecessary columns and you are left with the data you want.

Still another approach is to use a PivotTable. Select a cell within your original data and then, on the Insert tab of the ribbon, click the PivotTable tool. In the resulting PivotTable, add both the Name and Date fields to the Rows box, and you end up with a list of unique dates sorted by name.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8731) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.