i have integer values in column A where some integers repeat.

i want column B to list the unique integers of column A in the order
they appear in column A ignoring repeats.

So if column A has the integers: 1,4,1,3,3,1,2
I want my formula to give me the integers: 1,4,3,2,0,0,0

Is this possible?

Any help would be greatly appreciated!


--
bridgerbell
------------------------------------------------------------------------
bridgerbell's Profile: http://www.excelforum.com/member.php...o&userid=36092
View this thread: http://www.excelforum.com/showthread...hreadid=558725

One way, probably the long way (array entered using Control+Shift+Enter).
Assuming your data is in A1:A7 and the formula is entered in cell F1 (change
ranges as needed):

=IF(ROWS($F$1:F1)>SUM(1/COUNTIF($A$1:$A$7,$A$1:$A$7)),0,INDEX($A$1:$A$7,SMALL(IF((FREQUENCY($A$1:$A$7,$A$1:$A$7)>0),ROW(INDIRECT("1:"&ROWS($A$1:$A$7))),""),ROWS($F$1:F1))))

"bridgerbell" wrote:

>
> i have integer values in column A where some integers repeat.
>
> i want column B to list the unique integers of column A in the order
> they appear in column A ignoring repeats.
>
> So if column A has the integers: 1,4,1,3,3,1,2
> I want my formula to give me the integers: 1,4,3,2,0,0,0
>
> Is this possible?
>
> Any help would be greatly appreciated!
>
>
> --
> bridgerbell
> ------------------------------------------------------------------------
> bridgerbell's Profile: http://www.excelforum.com/member.php...o&userid=36092
> View this thread: http://www.excelforum.com/showthread...hreadid=558725
>
>

Hi bridgerbell,

You could goto Data>Filter>Advanced Filter
Check 'copy to another location'
Specify the range
Specify the destination
And click 'Unique records only'

You may need to put a name at the top of your column,
a single letter will be enough.

HTH
Martin

Just another play which can extract the uniques list using non-array formulas
...

Data assumed running in A1 down

In B1:
=IF(ROW(A1)>COUNT(C:C),"",INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))

In C1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",ROW()))

Select B1:C1, fill down as far as data is expected in col A
Col B will return the required results, all neatly bunched at the top
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"bridgerbell" wrote:
>
> i have integer values in column A where some integers repeat.
>
> i want column B to list the unique integers of column A in the order
> they appear in column A ignoring repeats.
>
> So if column A has the integers: 1,4,1,3,3,1,2
> I want my formula to give me the integers: 1,4,3,2,0,0,0
>
> Is this possible?
>
> Any help would be greatly appreciated!
>
>
> --
> bridgerbell
> ------------------------------------------------------------------------
> bridgerbell's Profile: http://www.excelforum.com/member.php...o&userid=36092
> View this thread: http://www.excelforum.com/showthread...hreadid=558725
>
>

i like using max's function but it includes zeroes. is there a way to
adjust max's function to exclude zeroes?


--
bridgerbell
------------------------------------------------------------------------
bridgerbell's Profile: http://www.excelforum.com/member.php...o&userid=36092
View this thread: http://www.excelforum.com/showthread...hreadid=558725

"bridgerbell" wrote:
> i like using max's function but it includes zeroes. is there a way to
> adjust max's function to exclude zeroes?

Glad you liked it.

Think we could just tweak the criteria formula in col C

Try instead in C1, copied down:
=IF(OR(A1="",A1=0),"",IF(COUNTIF($A$1:A1,A1)>1,"",ROW()))

(No change to formulas in col B)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

i actually figured out how to exclude zeroes after i posted but couldn'
load the forum for some reason to remove my question. but, i have on
more question: i have a header row, so my first entries are in row 2.
your formulas only work if my entries start in row 1. if i put you
formulas in my spreadsheet with the header row, i get the second numbe
i want at the top instead of the first. how can i adjust the formula t
account for the numbers starting in row 2? THANKS MUCH

--
bridgerbel
-----------------------------------------------------------------------
bridgerbell's Profile: http://www.excelforum.com/member.php...fo&userid=3609
View this thread: http://www.excelforum.com/showthread.php?threadid=55872

"bridgerbell" wrote:
> i actually figured out how to exclude zeroes after i posted but couldn't
> load the forum for some reason to remove my question.

Do not edit after you have posted. If you wish to clarify your own earlier
post, just post again as a reply to it. That way, your clarifications will
appear and be visible to all readers out there, not just to readers of
excelforum.

> but, i have one more question:
> i have a header row, so my first entries are in row 2.
> your formulas only work if my entries start in row 1. if i put your
> formulas in my spreadsheet with the header row, i get the second number
> i want at the top instead of the first. how can i adjust the formula to
> account for the numbers starting in row 2?

If source data in col A starts in A2 down ...

In B2:
=IF(ROW(A1)>COUNT(C:C),"",INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))

[ Same formula as previous. No change. ROW(A1) is always used in the
starting formula cell, irrespective of the row that the cell is in. ROW(A1)
is used as an incrementer when we copy down. It simply produces the number
series: 1,2,3 ... for the SMALL to pick off ]

In C2:
=IF(OR(A2={"",0}),"",IF(COUNTIF($A$2:A2,A2)>1,"",ROW()))

[ OR(A2={"",0}) is just a shorter rendition of: OR(A2="",A2=0) ]

Then just select B2:C2, fill down as far as data is expected in col A
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

bridgerbell wrote...
>i have integer values in column A where some integers repeat.
>
>i want column B to list the unique integers of column A in the order
>they appear in column A ignoring repeats.
>
>So if column A has the integers: 1,4,1,3,3,1,2
>I want my formula to give me the integers: 1,4,3,2,0,0,0
....

With your original data in A1:A7, you could try the following array
formulas in B1:B7.

B1 [array formula]:
=INDEX(A1:A7,MATCH(TRUE,A1:A7<>0,0))

B2 [array formula]:
=IF(OR(COUNTIF(B$1:B1,A2:A$7)=0),
INDEX(A2:A$7,MATCH(1,(A2:A$7<>0)*(COUNTIF(B$1:B1,A2:A$7)=0),0)),0)

Fill B2 down into B3:B7.

this has been a great help thank you all. i am well on my way now.


--
bridgerbell
------------------------------------------------------------------------
bridgerbell's Profile: http://www.excelforum.com/member.php...o&userid=36092
View this thread: http://www.excelforum.com/showthread...hreadid=558725