Unique formula based on a column with repeated data

[Mr2017]

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?

Locations						Unique List of Locations
England						England
England						Japan
England						Canada
Japan						USA
Japan
Japan
Canada
Canada
Canada
USA
USA
USA

 

[jasonb75]

As you have office / excel 365, you could use the UNIQUE function if you have it.

UNIQUE function - Microsoft Support

Learn about the UNIQUE function, which returns a list of unique values in a list or range. UNIQUE is in a class of functions called dynamic arrays.

support.microsoft.com

 

[Mr2017]

Ok.

I tried this, but it didn't work?

Any ideas on how to change it?

=UNIQUE(A2:A13,TRUE)

 

[mehidy1437]

Try with this at G2.

=IFERROR(INDEX($A$2:$A$12, MATCH(0, INDEX(COUNTIF($g$1:g1, $A$2:$A$12), 0, 0), 0)), "")

 

[Fluff]

Just use

=UNIQUE(A2:A13)

You are telling it to return unique cols & you only have one column.

 

[jasonb75]

Reading the link that I posted, I would say

=UNIQUE(A2:A13)

I might be reading it wrong, but the TRUE argument appears to be used to return a list of entries that are unique (only appear once in the source list) not to show a list of distinct results.
edit:- I was reading it wrong, that is with 2 commas, not one.

 

[Mr2017]

Ok, thanks @mehidy1437 , @Fluff and @jasonb75

Your suggested solutions worked!

I do have two further questions, however.

With the real data, the number of rows will vary with each file.

So I was planning to use =unique(a2:a1000) as there won't ever be more than 1,000 rows.

However, if I extend the number of rows in the example I gave above ie dragging the word 'USA' in cell A13 down until cell A20, then going to cell G2 and typing in =UNIQUE(A2:A1000) I get a #SPILL! error.

Does anyone know why that would happen?

And is there a way to modify the formula in cell G2 using VBA so that it only goes up to the number of rows in column A each time new data is pasted into column A?

I'm sure @Fluff knows how to do that!

 

[jasonb75]

You don't need vba for that, just make the range dynamic, either by formula or by using a table. There might be a neater method with 365 that I don't know of.

=UNIQUE($A$2:INDEX($A:$A,MATCH("zzz",$A:$A)))

I believe that #SPILL! means that there are not enough consecutive empty cells where you have entered the formula for it to return all of the results, again, this is not something that I know for certain, just my (perhaps incorrect) understanding of the problem based on things that I've read in other threads.

 

[Fluff]

If you will never have more than 1000 rows you can use

=UNIQUE(FILTER(A2:A1000,A2:A1000<>""))

which will prevent getting a 0 in the list.
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.

 

[Mr2017]

Ok, thank you both!

Both suggestions worked!

I didn't know that #SPILL errors occur because of non-blank cells below - so that info is useful! I came across it for the first time a few days ago!