1,4,1,3,3,1,2=>1,4,3,2

B

bridgerbell

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!
 
J

JMB

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))))
 
M

MartinW

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
 
M

Max

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
 
B

bridgerbell

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

Max

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

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)
 
B

bridgerbell

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
 
M

Max

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

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?
Click to expand...

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
 
H

Harlan Grove

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
Click to expand...
....

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.
 
Top