Microsoft Excel: Use Query To Get A Unique Set Of Records
written by: Mr Excel•edited by: Tricia Goss•updated: 7/23/2008
Problem: You’ve seen many different ways to get a unique list of customers from a dataset in Excel. You are sitting back right now, thinking, couldn’t Bill Jelen offer just one more way to get a unique list of customers?
I have to bring this up, because it is fast and fairly cool. It is also a way to get a list of customers from a closed file. The trick is to use Microsoft Query. Now, Microsoft Query is not in the default install of Excel. So, if you didn’t do a complete install, you are going to have to find the installation CDs to add Microsoft Query to your installation of Excel.
Basically, Microsoft Query is a way to run some SQL against an external file. I am sure that Microsoft envisioned this would be an Access table or an ODBC datasource, but, of course, someone figured out that the external file could be another Excel workbook. Here is how you would set this up.
1) Let’s say you have a workbook called SalesData.xls. It has one worksheet called Data. The worksheet contains a bunch of records in columns A through H, as shown in Fig. 1003. Select the data in A:H. Give the table a range name of MyData.
2) Save and Close SalesData.xls.
3) In a new workbook, select Data – Get External Data – New Database Query, as shown in Fig. 1004.
4) In the Choose Data Sources dialog, choose Excel Files, as shown in Fig. 1005. Choose OK.
5) Laugh at the retro Select Workbook dialog. Then, browse to find SalesData.xls, as shown in Fig. 1006. Choose OK.
6) As shown in Fig. 1007, select the Customer field in the left list and hit the Right Arrow button to move Customer over to the Query.
7) Choose the Next button, as shown in Fig. 1008.
8) Choose Next to skip the Filter section. See Fig. 1009.
9) Choose to Sort Ascending by Customer, as shown in Fig. 1010. Click Next.
10) Since you want unique records only, you need to edit the query in Microsoft Query. As shown in Fig. 1011, choose this option and then choose Finish.
11) The Microsoft Query window opens. From the Microsoft Query menu, select View – Query Properties, as shown in Fig. 1012.
12) As shown in Fig. 1013, choose Unique Values Only and then OK.
13) As shown in Fig. 1014, select File – Return Data to Microsoft Excel.
14) Choose where you would like to place the imported data, as shown in Fig. 1015. Choose OK.
You have a unique list of customers from the closed Excel file. See Fig. 1016.
Under Data – Import External Data – Data Range Properties, you can set this query up to update every time the workbook is opened, as shown in Fig. 1017.
The Data Query tool provides a way to import data from Access, Excel, or ODBC data sources.