Unique formula based on a column with repeated data

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
644
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I've got a list of repeated addresess in column A of a spreadsheet.

But I need to return a unique consecutive list of the addresses in column A, into column G.

In the simple list below, I have 4 countries (England, Japan, Canada and the USA) listed 3 times each. So there are 12 rows in column A, with the countries starting in cell A2.

However, I need to put a formula in column G2 which will list the unique countries and show the results in the 'Unique List of Locations' in column G below.

I'll also need to be able to clear the data from row 3 and below, the be able to drag down the formula in column G when new data is put into the file.

Does anyone know how to do this, please?
LocationsUnique List of Locations
EnglandEngland
EnglandJapan
EnglandCanada
JapanUSA
Japan
Japan
Canada
Canada
Canada
USA
USA
USA
 
As Jason has said, the Spill error is because there is a non blank cell in col G preventing the formula from spilling down far enough.
Do you know it that relates to actual range or potential range, Fluff?
For example, using your formula from post 9, would it need 999 cells to spill to on the assumption that every cell in the range could be non blank and unique, or does it just require enough cells to return the results based on the current data?
 
Upvote 0

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
It needs enough cells for actual data returned, so if there where only 6 distinct values, you would only need 6 blank cells.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top