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

[bridgerbell]

i have integer values in column A with unwanted zeroes mixed in.

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

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

basically, i want to shift all the zeroes down below the other integers.

Is this possible?

Any help would be greatly appreciated!

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

"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
>
>

[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

[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
--
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
>
>

[bridgerbell]

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

[Max]

"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
---

[bridgerbell]

i actually figured out how to exclude zeroes after i posted but couldn't load the forum for some reason to remove my question. 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? THANKS MUCH!

[Max]

"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
---

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

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.

[bridgerbell]

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

[Max]

"bridgerbell" wrote:
> this has been a great help thank you all. i am well on my way now.

Good to hear that. Bon voyage ! <g>
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---