Hi Domenic,
Thanks for reply.
Domenic wrote:
The reason my formula returns 15 instead of 14 is that I understood you
wanted to add 1 to the result. In any case, let's see if I understand
you correctly...
1) You'd like to search for the Numeric Label 14.
Yes, correct.
2) The Numeric Label 14 is found at Column 16.
Yes, correct.
3) The corresponding Numeric Value is 2.
Yes, correct.
4) You'd like to add 1 to that value, which gives you 3.
Yes, correct.
5) You'd like to search for the first column that contains a value that
is greater than or equal to 3 (Numeric Value +1), starting from Column
15 and moving to the left.
Yes, correct.
6) This brings us to Column 14. But here I thought you wanted to add 1,
which would give you a result of 15. Did you in fact want to add 1?
No.
7) If there's no value greater than or equal to 3 (Numeric Value +1),
you'd like to search for the Numeric Label (14).
I think there will always be a value equal to or greater than the Numeric
Value.
8) You'd like to searching for the Numeric Label (14), starting from
Column 1 and moving right.
Based on answer to above Q7 probably no need.
9) Now here's where I think I may have misunderstood. When searching
for the Numeric Label (14), are you still checking Row 11 (starting from Column 1 and moving right) or are you now checking Row 10 (starting from Column 1 and moving right)?
Now checking Row 10 Numeric Label (starting from original Numeric Label
Column Number = Column 16 and moving LEFT) to find a Column where the Numeric
Label is correctly placed in Ascending order dependent on New Numeric Value
in strict descending order. The Numeric Label and Numeric Value must stay
together. Based on Sample Data below Column Number 14 is the correct Result.
Numeric Label 14 would replace the current 15 and 3 in Column Number 14 with
14 and 3. A NEW Numeric Value (original value plus one) cannot move to a
Column Number that has a Numeric Value that is greater than the NEW Numeric
Value but it can be the same.
Extraction of previous Data Sample:
----------------------------------Col12 Col13 Col14 Col15 Col16
Row10 Numeric Label 07 13 15 10 14
Row11 Numeric Value 04 04 03 02 02
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
If Column Number 14 had Numeric Label 6 and Numeric Value 3 (instead of 15
and 3) then Numeric Label 14 and its new Numeric Value of 3 would be placed
in Column Number 15 because of the Ascending order requirement - 6 before 14.
BUT remembering the Numeric Values must sit in strict Descending order across
their Row - the very first SEARCH/ sort to locate where the NEW Numeric
Value will be initially placed is based on Descending order. The Numeric
Label Row will not sit in strict Ascending order because of the preferred
SEARCH/ sort (1st sort key) given to the Numeric Values. Using the larger
Sample Data in the previous Post will show that Row 10's Numeric Labels
Column Number is governed by the strict descending order of their paired
Numeric Value.
----------------------------------Col12 Col13 Col14 Col15 Col16
Row10 Numeric Label 07 13 06 10 14
Row11 Numeric Value 04 04 03 02 02
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 6
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
May be a better way of saying it is: if my Row of Numeric Values were all of
the same value eg: 3 and there is nothing to define or distinguish which of
the identical Numeric Values should be located in a specific Column Number,
this is the purpose of the Numeric Label. The Numeric Labels for the Numeric
Values in this case will be in strict Ascending order beacuse all the Numeric
Values are the same, that is 3.
For a Row of varying Numeric Values as in the Sample Data: the Numeric Label
will position and place itself with its Numeric Value as far as possible in
Ascending order; baring in mind the Ascending order is secondary to the
Numeric Value which will always sit in strict descending order. The Ascending
order of the Numeric Label is dependent on the strict Descending order of the
Numeric Value.
Cheers,
Sam
Based on the Sample Data below: can you see if you get Numeric Label 14
(currently housed in Column Number 16) and its Numeric Value to Return a
[quoted text clipped - 64 lines]
correct Result required is Column Number 14 using the SEARCH order:
Descending for Numeric Value and Ascending for Numeric Label.
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200601/1