Next name down a list

**djfatboyfats** · 11-23-2008, 09:12 PM

Hi

I need a formula/vba function that will select the next name down on a list and place it in another cell, lets say for example we have names in cells A1:A20 names go as follows:

DJ
DJ
DJ
AC
AC
FB
FB

Now starting in column b i want a formula/vba that will look for the next name down column a and place it in the cells so i get

DJ
AC
FB

Thanks in advance

DJ

**Mikeopolo** · 11-23-2008, 09:35 PM

See http://www.cpearson.com/excel/DistinctValues.aspx for a VBA function which will do this.

Regards
Mike

**djfatboyfats** · 11-23-2008, 09:37 PM

page not working

**Mikeopolo** · 11-23-2008, 09:40 PM

Curious, works for me; try going to Chip's home page and follow the links from there.

**djfatboyfats** · 11-23-2008, 09:45 PM

no still not working, keep getting "Configuration error" tried it on firefox and Internet explorer, thanks anyway. do you know the answer so you could post on here or is to complex?

Thanks

DJ

**djfatboyfats** · 11-23-2008, 09:49 PM

Sorted, thanks very much

**Ron Coderre** · 11-23-2008, 09:43 PM

With
A1:A10 containing this list, with A1 as the heading:

OrigList
Alpha
Alpha
Alpha
Bravo
Charlie
Charlie
Delta
Delta
Delta

and
B1: SeqList

If the list will not contain any all-numeric entries, this formula begins listing
each unique name, as it is encountered

B2: =LOOKUP(CHAR(255),CHOOSE({1,2},"",INDEX($A$2:$A$20,MATCH(0,
IF($A$2:$A$20<>"",INDEX(COUNTIF($B$1:B1,$A$2:$A$20),0)),0))))

Copy that formula down as far as you need.

(If the list will include numeric entries, there's longer formula to use)

In the above example, the following values are returned by the formulas
in B2:B5