Getting unique records

Hi Shannon,

Thank you for the forum question.

Yes, this can definitely be automated in Excel! There are a few ways to achieve this depending on your setup and preference:
________________________________________
Option 1: Use a Dynamic Array Formula (Excel 365 / Excel 2021)
If you have a modern version of Excel, you can use the UNIQUE() function:
1. On your second sheet, in the cell where you want the unique list to start, enter: =UNIQUE(Sheet1!A:A) (Replace Sheet1!A:A with the actual range you want to pull from.)
2. This will automatically spill the unique values into the table and update whenever new data is added.
________________________________________
Option 2: Use Power Query (Works in all modern Excel versions)
Power Query is great for automating data transformations:
1. Select your data range on the first sheet.
2. Go to Data → Get & Transform → From Table/Range.
3. In Power Query, select the column you want unique values from.
4. Use Remove Duplicates.
5. Load the result to your second sheet.
6. When new data is added, just click Refresh and the unique list updates automatically.
________________________________________
Option 3: Use VBA for Full Automation
If you want it to update automatically without clicking refresh:
• You can write a simple VBA macro that runs whenever data changes and updates the unique list on the other sheet.



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector