Challenge: Unique Values via Formulas
 

I was wondering if there was a way to return unique values in a column
from another column which contains duplicates. I already have a number
of macros to do this (and I'm aware of Filtering) but I was wondering if
there was a formulaic solution one might try to implement something like
this within a spreadsheet to keep this light and simple without having
to resort to these other methods. I think there should be a way.

To illustrate, say I have the following in column A:

A
A
B
C
C
D
D
D
D
E
F
F
G
G

The goal would be to return A-G IN CONTIGIUOUS cells in Column B (or
some other column). I should mention that using "helper" columns (such
as those containing the count of the value in column A for offset
purposes) would be okay, of course (as long as it's limited to a few of
them, say in B-D and my results column is D or something like that).

Thus far, I've been thinking along the lines of (for rows 2 and
greater):

Helper column B:

=COUNTIF(A:A,A2) ...[For Cell A2, and similar, copied down]

Results column C:

=OFFSET($A$1,SUM(OFFSET(B2,0,0):OFFSET(B2,(-1*ROW())+1,0)),0)

But this isn't quite doing it.

I'd love to collaborate on this, if anyone is willing.

Thanks,

-Petee


--
Peter Bernadyne
------------------------------------------------------------------------
Peter Bernadyne's Profile: http://www.excelforum.com/member.php...fo&userid=7017
View this thread: http://www.excelforum.com/showthread...hreadid=526791

Challenge: Unique Values via Formulas
 

Assuming that A2:A15 contains your data...

B1: leave empty

B2, copied down:

=INDEX(A2:$A$15,MATCH(0,COUNTIF($B$1:B1,A2:$A$15), 0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article
<Peter.Bernadyne.25c7cn_1143477602.0766@excelfor um-nospam.com,
Peter Bernadyne
<Peter.Bernadyne.25c7cn_1143477602.0766@excelfor um-nospam.com wrote:

I was wondering if there was a way to return unique values in a column
from another column which contains duplicates. I already have a number
of macros to do this (and I'm aware of Filtering) but I was wondering if
there was a formulaic solution one might try to implement something like
this within a spreadsheet to keep this light and simple without having
to resort to these other methods. I think there should be a way.

To illustrate, say I have the following in column A:

A
A
B
C
C
D
D
D
D
E
F
F
G
G

The goal would be to return A-G IN CONTIGIUOUS cells in Column B (or
some other column). I should mention that using "helper" columns (such
as those containing the count of the value in column A for offset
purposes) would be okay, of course (as long as it's limited to a few of
them, say in B-D and my results column is D or something like that).

Thus far, I've been thinking along the lines of (for rows 2 and
greater):

Helper column B:

=COUNTIF(A:A,A2) ...[For Cell A2, and similar, copied down]

Results column C:

=OFFSET($A$1,SUM(OFFSET(B2,0,0):OFFSET(B2,(-1*ROW())+1,0)),0)

But this isn't quite doing it.

I'd love to collaborate on this, if anyone is willing.

Thanks,

-Petee

Challenge: Unique Values via Formulas
 

You can download an example here

http://nwexcelsolutions.com/Download..._records. xls





--

Regards,

Peo Sjoblom

"Peter Bernadyne"
<Peter.Bernadyne.25c7cn_1143477602.0766@excelfor um-nospam.com wrote in
message news:Peter.Bernadyne.25c7cn_1143477602.0766@excelf orum-nospam.com...

I was wondering if there was a way to return unique values in a column
from another column which contains duplicates. I already have a number
of macros to do this (and I'm aware of Filtering) but I was wondering if
there was a formulaic solution one might try to implement something like
this within a spreadsheet to keep this light and simple without having
to resort to these other methods. I think there should be a way.

To illustrate, say I have the following in column A:

A
A
B
C
C
D
D
D
D
E
F
F
G
G

The goal would be to return A-G IN CONTIGIUOUS cells in Column B (or
some other column). I should mention that using "helper" columns (such
as those containing the count of the value in column A for offset
purposes) would be okay, of course (as long as it's limited to a few of
them, say in B-D and my results column is D or something like that).

Thus far, I've been thinking along the lines of (for rows 2 and
greater):

Helper column B:

=COUNTIF(A:A,A2) ...[For Cell A2, and similar, copied down]

Results column C:

=OFFSET($A$1,SUM(OFFSET(B2,0,0):OFFSET(B2,(-1*ROW())+1,0)),0)

But this isn't quite doing it.

I'd love to collaborate on this, if anyone is willing.

Thanks,

-Petee


--
Peter Bernadyne
------------------------------------------------------------------------
Peter Bernadyne's Profile:
http://www.excelforum.com/member.php...fo&userid=7017
View this thread: http://www.excelforum.com/showthread...hreadid=526791

Challenge: Unique Values via Formulas
 

Wow.

Very Impressive.

Thank you both very much for your replies, they are tremendously
appreciated.

-Peter


--
Peter Bernadyne
------------------------------------------------------------------------
Peter Bernadyne's Profile: http://www.excelforum.com/member.php...fo&userid=7017
View this thread: http://www.excelforum.com/showthread...hreadid=526791