0

I have a list (see below) and want single entries for each booth. I can probably work out how to do in GREP and copy paste result back into Excel but want to learn how to use multiple lookups in a single forumla and compare function returned values to.

Link to full list as CSV file

List starts like this:

Auburn Auburn Auburn Auburn Auburn Auburn South Auburn South Auburn South Auburn South Auburn South Auburn South Balwyn Balwyn Balwyn Central Balwyn Central Balwyn Central

wide_eyed_pupil
  • 3,153
  • 7
  • 24
  • 35

1 Answers1

1

You have multiple options, I'll cover two (assuming you have Excel 2016):

In Excel

Advantages: quick and easy

Steps:

  1. Click file | open | browse | change to see all files | browse and select the csv file
  2. Click Data | Remove duplicates | Ok
  3. Save the file

In Excel / Power Query

Advantages: if the source file is replaced, you can update the query, and it'll show you the new results

Steps:

  1. Create a New Excel file
  2. Click Data | From text/csv | browse for the file | click import (if file appears as a binary icon, you have to right click it and select text)
  3. Click Home | Remove rows | Remove duplicates
  4. Click Home | Close and load | Close and load to
  5. Select Table | New worksheet | Ok

Another option would be to use Excel/VBA

Load a CSV to a file

Remove duplicates with VBA

Ricardo Diaz
  • 5,658
  • 2
  • 19
  • 30
  • Be careful with remove duplicates, it's known to be buggy and not always work correctly. – Michael May 22 '19 at 03:13
  • Thank you @Michael, haven't read anything about it being buggy. Could you please tell me where did you read this? – Ricardo Diaz May 22 '19 at 20:40
  • I've had it occur and there are plenty of results when you google "Remove duplicates not working" that aren't related to numbers stored as text etc. https://superuser.com/questions/572226/remove-duplicates-feature-does-not-remove-all-duplicates has sample data which you can copy into excel to reproduce the issue. The times that it seems to fail is when you have numbers and strings that are nearly numbers. Repeated attempts to remove duplicates on the entire source data won't remove the stuck duplicates, but copy those same duplicates to a new range by themselves, then remove duplicates works. – Michael May 22 '19 at 23:44
  • Thanks for the link @Michael, I googled it but couldn't find any relevant results. Your link pointed me to the right direction. I tried to reproduce the behavior, but Excel 2016/2019 removed the duplicates as expected. I guess is a bug in 2007/2010 versions. – Ricardo Diaz May 23 '19 at 03:17
  • I'm using Office 365 and the values they identify as being affected aren't removed for me. – Michael May 23 '19 at 03:47