HELP!

[Bill]

I have the 3 columns of data in columns "m", "n", and "o" (No headings, data
starts in row 1). There are 216 rows starting in row 1. From the example
below, I will create in column "r", the heading FB2. Under the heading, I
want to see each 3 digit number that corresponds to FB2, with out repeating
the same 3 digit number.
M N O p Q
R
000 0.198407405 PO3 FB2
001 0.207502916 CV3 003
002 0.984589896 CV3
003 0.715903627 FB2
....
253 0.874352341 FB2 253
....
345 0.345343556 FB2 345

 

[T. Valko]

Try this...

Assuming there are no empty cells in column M. The entries in column M are
numbers and those with leading zeros have been formatted to display leading
zeros.

R1 = FB2 (or any other code from column O)

Enter this array formula** in cell S1. This will return the count of unique
numbers in column M that meet the criteria.

=COUNT(1/FREQUENCY(IF(O1:O216=R1,M1:M216),M1:M216))

Enter this array formula** in cell R2:

=IF(S1>0,MIN(IF(O1:O216=R1,M1:M216)),"")

Enter this array formula** in R3 and copy down until you get blanks:

=IF(ROWS(R$2:R3)<=S$1,MIN(IF((O$1:O$216=R$1)*(M$1:M$216>R2),M$1:M$216)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Bill]

{=COUNT(1/FREQUENCY(IF(O1:O216=R1,M1:M216),M1:M216))}
No matter what data poi nt is in R1, this formula returns 0.
Then the second formula does not find anything, because s1=0

 

[Bill]

{=COUNT(1/FREQUENCY(IF(O1:O216=R1,M1:M216),M1:M216))}
Whether entered as an array or not, this formula returns 0. With this
being 0, the second formula finds nothing because S1 is not greater than 0.

I'm sure I missed something, but I just can't figure it out.

 

[T. Valko]

That tells me that either column M doesn't contain numeric numbers or column
O may contain entries like FB2 but might also have unseen characters like
spaces. It's possible that FB2 is actually FB2<space>.

In the sample data you postd, the first thing I think of when I see numbers
with leading zeros is these are not really numeric numbers but are TEXT
numbers.

Try this formula in some cell:

=COUNT(M1:M216)

What result do you get? If you get 0 then your numbers are text.

 

[Bill]

They were entered as text. So is column "O", when Iswitched them to numeric,
column N equals 216, but I can't get a value for the count in Column "O"

 

[T. Valko]

Try this...

Select an empty that has never been used or been formatted. It can be any
empty cell.
Copy that empty cell: Edit>Copy
Select the range of numbers in M1:M216
Then, Edit>Paste Special>Add>OK

That will usually convert text numbers to numeric numbers.