Re: finding the column number where data is located

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 03/12/04 

Date: Sat, 13 Mar 2004 00:01:00 +0100

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

--
Regards
Frank Kabel
Frankfurt, Germany
Ike wrote:
> 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
>> -----Original Message-----
>> 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
>>
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>>
>> Ike wrote:
>>> 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.
>>>
>>>> -----Original Message-----
>>>> 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 ***:
>>>> =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
>>>>
>>>> --
>>>> Regards
>>>> Frank Kabel
>>>> Frankfurt, Germany
>>>>
>>>> Ike wrote:
>>>>> 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.
>>>>
>>>> .
>>
>> .
Quantcast