Hi Domenic,
Your Formula below is working. However, I found a few Rows of data where it
does not Return the Column Number that I expect, that is due to my initial
explanation. I 'm sure I got my search /sort type round the wrong way. I said
Ascending for the Numeric Value and it should be Descending.
It is basically the SEARCH order: Descending order, Numeric Value 1st search/
sort key and then Ascending order for the Numeric Label to be used as the 2nd
search/ sort key.
I think it might be easier for me to explain what I'm trying to say by way of
some Sample Data:
Column Number 1 Row 10 Houses Numeric Label 0
Column Number 1 Row 11 Houses Numeric Value 16
Column Number 2 Row 10 Houses Numeric Label 2
Column Number 2 Row 11 Houses Numeric Value 12
Column Number 3 Row 10 Houses Numeric Label 1
Column Number 3 Row 11 Houses Numeric Value 10
Column Number 4 Row 10 Houses Numeric Label 4
Column Number 4 Row 11 Houses Numeric Value 9
Column Number 5 Row 10 Houses Numeric Label 6
Column Number 5 Row 11 Houses Numeric Value 9
Column Number 6 Row 10 Houses Numeric Label 12
Column Number 6 Row 11 Houses Numeric Value 7
Column Number 7 Row 10 Houses Numeric Label 8
Column Number 7 Row 11 Houses Numeric Value 6
Column Number 8 Row 10 Houses Numeric Label 3
Column Number 8 Row 11 Houses Numeric Value 5
Column Number 9 Row 10 Houses Numeric Label 5
Column Number 9 Row 11 Houses Numeric Value 5
Column Number 10 Row 10 Houses Numeric Label 9
Column Number 10 Row 11 Houses Numeric Value 5
Column Number 11 Row 10 Houses Numeric Label 11
Column Number 11 Row 11 Houses Numeric Value 5
Column Number 12 Row 10 Houses Numeric Label 7
Column Number 12 Row 11 Houses Numeric Value 4
Column Number 13 Row 10 Houses Numeric Label 13
Column Number 13 Row 11 Houses Numeric Value 4
Column Number 14 Row 10 Houses Numeric Label 15
Column Number 14 Row 11 Houses Numeric Value 3
Column Number 15 Row 10 Houses Numeric Label 10
Column Number 15 Row 11 Houses Numeric Value 2
Column Number 16 Row 10 Houses Numeric Label 14
Column Number 16 Row 11 Houses Numeric Value 2
Column Number 17 Row 10 Houses Numeric Label 18
Column Number 17 Row 11 Houses Numeric Value 2
Based on the above Sample Data the Formula currently Returns Column Number 15.
I would expect Column Number 14 to be Returned as the correct Result using
the SEARCH order: Descending for Numeric Value and Ascending for Numeric
Label.
When the Data is listed across the relevant two Rows for Numeric Labels and
their corresponding Numeric Values, Numeric Label 14 is housed in Column
Number 16 with a Numeric Value of 2. However, when that Numeric Value is
increased by 1 to 3, it should then move LEFT to Column Number 15 but because
it's also connected to its Numeric Label which will always remain the same, i.
e. Numeric Label 14 which must move LEFT in Ascending order (2nd search /
sort key) which then places both it and its Numeric Value in Column 14.
The Numeric Label 14 was originally housed in Column Number 16 and should
move LEFT past the Numeric Label 10 in Column 15, and replace Numeric Label
15 in Column Number 14; although Numeric Label 14 now has the same Numeric
Value as that of Numeric Label 15 (both Numeric Label 14 and 15 have a
Numeric Value of 3) the Numeric Label of 14 is less than that of Numeric
Label 15 and when placed in Ascending order should be in a Column Number to
the LEFT of, or before, or in front of Numeric Label 15 because both of their
Numeric Values are the same and equal. The only difference now between them
is that one Numeric Label is lower, so when placed in Ascending order the
Numeric Label with the lower Number should be listed first in the Row.
There will be occassions when a higher Number Numeric Label is placed before
a lower one: when the Numeric Value of the Higher Numeric Label exceeds that
of the Lower Numeric Label.
The Numeric Label and Numeric Value work and move as a pair. The Numeric
Label must always be placed in Ascending order with its Numeric Value in
Descending order (used as the 1st search/ sort key).
Hope it's possibe to decipher what I'm trying to say. Apologies for any
confusion.
I think it reads worst than it really is but hey ... I can't even get
Ascending and Descending right!
Further assistance very much appreciated - hope you can salvage this.
Cheers,
Sam
Domenic wrote:
Try the following...
Let T11 contain your criteria/numerical label
U11:
=MATCH(T11,$F$10:$R$10,0)
This will return the column position.
V11:
=INDEX(F11:R11,U11)+1
This will return the corresponding value in Row 11, and add 1.
W11:
=IF(U111,LOOKUP(2,1/(F11:INDEX(F11:R11,U11-1)=V11),COLUMN(F11:INDEX(F11
:R11,U11-1))-COLUMN(F11)+1)+1,#N/A)
This will search left for the first column containing a value greater
than or equal to V11, return the column position, and add 1. If the
column number representing the numeric label is 1, the formula will
return #N/A since no values exist to the left.
X11:
=IF(U111,IF(ISNA(W11),LOOKUP(2,1/(F11:INDEX(F11:R11,U11-1)=T11),COLUMN(
F11:INDEX(F11:R11,U11-1))-COLUMN(F11)+1)+1,W11),#N/A)
Here, if W11 equals #N/A, the numerical label is used to search left for
the first column containing a value greater than or equal to the numeric
value, return the column position, and add 1. Otherwise, it returns the
value in W11. And, again, if the column number representing the numeric
label is 1, the formula will return #N/A since no values exist to the
left.
Post back if I misinterpreted your intent...
Hope this helps!
Hi All,
[quoted text clipped - 41 lines]
Thanks
Sam
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200601/1