finding the column number where data is located

  • Thread starter Thread starter Ike
  • Start date Start date
I

Ike

Hello Everyone,
I have data in a range of about 200 columns and about 240
rows. I need to locate the column numbers where the first
5 smallest numbers are. So I used:

MATCH(SMALL(A1:JQ1,1),A1:JQ1,0)
MATCH(SMALL(A1:JQ1,2),A1:JQ1,0)
MATCH(SMALL(A1:JQ1,3),A1:JQ1,0)
MATCH(SMALL(A1:JQ1,4),A1:JQ1,0)
MATCH(SMALL(A1:JQ1,5),A1:JQ1,0)

Which works fine unless a row contains equal values. At
that point I am getting the column number of the first
(smallest) value for all the equal values. Is there a way
I can locate the column number for the first 5 smallest
values even if there are equal values in the row I would
still want the column numbers for those.

Thank you all in advance for all your help.
 
Hi
try the following array entered formula (entered with CTRL+SHIFT+ENTER)
=MATCH(TRUE;$A$1:$JQ$1+COLUMN($A$1:$JQ$1)/1000=SMALL($A$1:$JQ$1+COLUMN(
$A$1:$JQ$1)/1000,1),0)

or to make it easier for copying: Put the following formula in row 1 of
your sheet:
=MATCH(TRUE;$A$1:$JQ$1+COLUMN($A$1:$JQ$1)/1000=SMALL($A$1:$JQ$1+COLUMN(
$A$1:$JQ$1)/1000,ROW()),0)
and copy down
 
Hello Frank,
Thank you for your help. I tried the formula and it worked
but seeing how it works, I think my question was not 100%
clear, for that I apologize. What I am trying to do is
locate the column numbers where the 5 smallest numbers are
in each row. And if, say there are 2 equal values in the
row, I would still want the column numbers for those.
Example: Data in A1:H1
A1 B1 C1 D1 E1 F1 G1 H1 ....etc
0.260 0.263 0.260 0.330 0.278 0.773 1.512 0.167

Results in ZA1 through ZE1 should be:
ZA1 ZB1 ZC1 ZD1 ZE1
8 1 3 2 5

Again, Thanks for your help in advance.
 
Hi Ike
the formula (at least the first one with a changing index for the SMALL
function) should return exactly this numbering. Try the following
formula (adapt it to your range):
=MATCH(TRUE;$A$1:$X$1+COLUMN($A$1:$X$1)/1000=SMALL($A$1:$X$1+COLUMN($A$
1:$X$1)/1000,1),0)
and
=MATCH(TRUE;$A$1:$X$1+COLUMN($A$1:$X$1)/1000=SMALL($A$1:$X$1+COLUMN($A$
1:$X$1)/1000,2),0)
and
=MATCH(TRUE;$A$1:$X$1+COLUMN($A$1:$X$1)/1000=SMALL($A$1:$X$1+COLUMN($A$
1:$X$1)/1000,3),0)
.....
all entered as array formula (CTRL+SHIFT+ENTER) return exactly the
result from your example
 
Hello Frank,
Thanks a million one more time for your help. The first
one worked after I changed the /1000 to /100000 in the
formula, I am not sure if the fact that my data is all
less than 1 and up to 3 decimal places was the cause of
the first try failing to make it 100%! Once again, Thanks
A Million.

With Best Regards,
Ike
 
Hi Ike
yes that was the reason. The term ROW(...)/n must be smaller than the
smallest difference in your data
 
Back
Top