Return SEARCHED Column Number of Numeric Label and Value

[Sam via OfficeKB.com]

Hi All,

I would like a flexible Formula to Return the Column Number of individual
Numeric Labels and their Numeric Value. I have a 2-Row by many Columns
chart/ grid layout. My 1st Column, Column Number "1" is Excel Column Letter
"F".

Numeric Label 17 is housed in Column Number "12" per my chart , its Numeric
Value 5 is housed on the next Row - directly below the Numeric Label.

Sample Data:

---------------------Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9 Col10 Col11
Col12 Col13 etc
-Numeric Label 3 2 6 0 8 4 5 9
11 1 10 17 7
-Numeric Value 18 15 12 11 8 7 7 6 6
5 5 5 4


Scenario:
To Return the correct Column Number: locate the Numeric Label 17 and its
Numeric Value on the Row below, Numeric Value is 5. The Numeric Value is to
be increased by a Value of 1 (one) - NEW Numeric Value = 6. The Column
Number Returned should reflect Numeric Label of 17 remains the same but the
Numeric Value is increased by one.

To Return a Column Number representing the Numeric Label 17 and its original
Numeric Value +1 (plus one): the Numeric Label is still 17 but the Numeric
Value is NOW 6. From the oringal Column Number that housed Numeric Label 17,
that is Column Number "12", SEARCH in Ascending order: moving LEFT from
Column Number "12" and using the Numeric Value as the 1st (first) search
order and the Numeric Label as the 2nd (second) Ascending search order.
SEARCH the Numeric Value Row until the first Numeric Value either equal to 6
or more than 6 is found. Then Return the Column Number of the Column to the
Right of that Numeric Value.

Required Result:
Return the Column Number based on the Numeric Label 17 and the NEW Numeric
Value of 6, search in Ascending order - First search order based on Numeric
Value 6 - Second search order based on Numeric Label 17.

Returned Result should be Column Number 10.

Thanks
Sam

 

[Sam via OfficeKB.com]

Hi All,

Apologies for mis-alignment of Sample Data:

Column Number 1 Row 10 Houses Numeric Label 3
Column Number 1 Row 11 Houses Numeric Value 18

Column Number 2 Row 10 Houses Numeric Label 2
Column Number 2 Row 11 Houses Numeric Value 15

Column Number 3 Row 10 Houses Numeric Label 6
Column Number 3 Row 11 Houses Numeric Value 12

Column Number 4 Row 10 Houses Numeric Label 0
Column Number 4 Row 11 Houses Numeric Value 11

Column Number 5 Row 10 Houses Numeric Label 8
Column Number 5 Row 11 Houses Numeric Value 8

Column Number 6 Row 10 Houses Numeric Label 4
Column Number 6 Row 11 Houses Numeric Value 7

Column Number 7 Row 10 Houses Numeric Label 5
Column Number 7 Row 11 Houses Numeric Value 7

Column Number 8 Row 10 Houses Numeric Label 9
Column Number 8 Row 11 Houses Numeric Value 6

Column Number 9 Row 10 Houses Numeric Label 11
Column Number 9 Row 11 Houses Numeric Value 6

Column Number 10 Row 10 Houses Numeric Label 1
Column Number 10 Row 11 Houses Numeric Value 5

Column Number 11 Row 10 Houses Numeric Label 10
Column Number 11 Row 11 Houses Numeric Value 5

Column Number 12 Row 10 Houses Numeric Label 17
Column Number 12 Row 11 Houses Numeric Value 5

Column Number 13 Row 10 Houses Numeric Label 7
Column Number 13 Row 11 Houses Numeric Value 4

Thanks
Sam

 

[Domenic]

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(U11>1,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(U11>1,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!

 

[Sam via OfficeKB.com]

Hi Domenic,

Your Formula has certainly done the job - thank you very much for all your
help.

I tried to adapt your Formula using a Named Range (NLabels) created for the
Numeric Labels - but used with an Offset of ONE Row to point to the Numeric
Values below the Numeric Labels: cell Ranges F11:R11 (per your Formula).
However, I cannot get the INDEX and OFFSET Functions to return the correct
values.

Is it possible for you to re-vamp the Formula below using the Named Range
NLabels with an Offset of "one" for the Row to replace Cell Ranges F11:R11.

=IF(U11>1,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.

Cheers,
Sam

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(U11>1,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(U11>1,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

 

[Domenic]

Try the following...

Select U11 first

Insert > Name > Define

Name: NLabels

Refers to:

=Sheet1!$F$10:$R$10

Click Add

Name: NValues

Refers to:

=Sheet1!$F11:$R11

Click Add

Name: NValues2

Refers to:

=Sheet1!$F11:INDEX(Sheet1!$F11:$R11,Sheet1!$U11-1)

Click Ok

*Change the sheet reference accordingly.

Then use the following formulas...

U11:

=MATCH(T11,NLabels,0)

V11:

=INDEX(NValues,U11)+1

W11:

=IF(U11>1,LOOKUP(2,1/(NValues2>=V11),COLUMN(NValues2)-MIN(COLUMN(NValues2
))+1)+1,#N/A)

X11:

=IF(U11>1,IF(ISNA(W11),LOOKUP(2,1/(NValues2>=T11),COLUMN(NValues)-MIN(COL
UMN(NValues2))+1)+1,W11),#N/A)

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you so much - Great!

Cheers,
Sam

Try the following...

Select U11 first

Insert > Name > Define

Name: NLabels

Refers to:

=Sheet1!$F$10:$R$10

Click Add

Name: NValues

Refers to:

=Sheet1!$F11:$R11

Click Add

Name: NValues2

Refers to:

=Sheet1!$F11:INDEX(Sheet1!$F11:$R11,Sheet1!$U11-1)

Click Ok

*Change the sheet reference accordingly.

Then use the following formulas...

U11:

=MATCH(T11,NLabels,0)

V11:

=INDEX(NValues,U11)+1

W11:

=IF(U11>1,LOOKUP(2,1/(NValues2>=V11),COLUMN(NValues2)-MIN(COLUMN(NValues2
))+1)+1,#N/A)

X11:

=IF(U11>1,IF(ISNA(W11),LOOKUP(2,1/(NValues2>=T11),COLUMN(NValues)-MIN(COL
UMN(NValues2))+1)+1,W11),#N/A)

Hope this helps!

Hi Domenic,

[quoted text clipped - 9 lines]

Is it possible for you to re-vamp the Formula below using the Named Range
NLabels with an Offset of "one" for the Row to replace Cell Ranges F11:R11.

 

[Sam via OfficeKB.com]

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

Try the following...

Let T11 contain your criteria/numerical label


This will return the column position.


This will return the corresponding value in Row 11, and add 1.


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.


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

 

[Domenic]

Sam,

I'm having a difficult time trying to understand the process involved.
Can you please provide a few examples under the differing situations,
along with the step-by-step thought process involved?

 

[Sam via OfficeKB.com]

Hi Domenic,

I'll try to clarify.

Your original Formula provided the correct answer based on SEARCH/ sort type
Ascending For Numeric Label and Ascending for Numeric Value. However, I
require the Formula to use SEARCH/ sort type Ascending for Numeric Label and
Descending for Numeric Value. That is the only change or difference to the
original Formula you provided.

Cheers,
Sam

Sam,

I'm having a difficult time trying to understand the process involved.
Can you please provide a few examples under the differing situations,
along with the step-by-step thought process involved?

[quoted text clipped - 103 lines]

Cheers,
Sam

 

[Domenic]

In that case, change the formula for W11 to the following...

=IF(U11>1,MATCH(TRUE,INDEX(NValues2>=V11,0),0),#N/A)

or

=IF(U11>1,MATCH(TRUE,NValues2>=V11,0),#N/A)

....confirmed with CONTROL+SHIFT+ENTER.

 

[Sam via OfficeKB.com]

Hi Domenic,

I'm not getting the expected Results. I may have done something incorrectly
with the Named Ranges but I don't think so.

If it is not too much to ask, could you re-produce your original A1notation
cell referenced Formula below with the changes you've just made based on the
SEARCH/ sort type for Numeric Labels being Ascending and Numeric Values
Descending.

This will return the corresponding value in Row 11, and add 1.

=IF(U11>1,LOOKUP(2,1/(F11:INDEX(F11:R11,U11-1)>=V11),COLUMN(F11:INDEX(F11:R11,U11-1))->COLUMN(F11)+1)+1,#N/A)

Cheers,
Sam

In that case, change the formula for W11 to the following...

=IF(U11>1,MATCH(TRUE,INDEX(NValues2>=V11,0),0),#N/A)

or

=IF(U11>1,MATCH(TRUE,NValues2>=V11,0),#N/A)

...confirmed with CONTROL+SHIFT+ENTER.

Hi Domenic,

[quoted text clipped - 8 lines]

Cheers,
Sam

 

[Domenic]

Okay, I think I see the problem, I forgot the +1 bit...

The original notation...

=IF(U11>1,MATCH(TRUE,F11:INDEX(F11:R11,U11-1)>=V11,0)+1,#N/A)

....confirmed with CONTROL+SHIFT+ENTER.

The named ranges...

=IF(U11>1,MATCH(TRUE,INDEX(NValues2>=V11,0),0)+1,#N/A)

or

=IF(U11>1,MATCH(TRUE,NValues2>=V11,0)+1,#N/A)

....confirmed with CONTROL+SHIFT+ENTER.

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you for further assistance. I'm not sure what has happened but I'm
still not getting the Expected Results from either of the current Formulae.

Okay, I think I see the problem, I forgot the +1 bit...

The original notation...

...confirmed with CONTROL+SHIFT+ENTER.

The named ranges...



...confirmed with CONTROL+SHIFT+ENTER.

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
Column Number of 14 using the current Formulae.

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, I would expect Numeric Label 14 to Return
Column Number 14 as the correct Result using the SEARCH order: Descending for
Numeric Value and Ascending for Numeric Label.

Your original A1 style notation Formula provided a Result closer to the
Expected Result and made reference to the COLUMN Function (not sure if
relevant).

The A1 style notation Formula below (still using sample data above) will
Return a Column Number Result of 15 for Numeric Label 14. However, the
correct Result required is Column Number 14 using the SEARCH order:
Descending for Numeric Value and Ascending for Numeric Label.

This will return the corresponding value in Row 11, and add 1.

W11:
=IF(U11>1,LOOKUP(2,1/(F11:INDEX(F11:R11,U11-1)>=V11),COLUMN(F11:INDEX(F11:R11,U11-1))-COLUMN(F11)+1)+1,#N/A)

Cheers,
Sam

Okay, I think I see the problem, I forgot the +1 bit...

The original notation...

=IF(U11>1,MATCH(TRUE,F11:INDEX(F11:R11,U11-1)>=V11,0)+1,#N/A)

...confirmed with CONTROL+SHIFT+ENTER.

The named ranges...

=IF(U11>1,MATCH(TRUE,INDEX(NValues2>=V11,0),0)+1,#N/A)

or

=IF(U11>1,MATCH(TRUE,NValues2>=V11,0)+1,#N/A)

...confirmed with CONTROL+SHIFT+ENTER.

Hi Domenic,

[quoted text clipped - 5 lines]

SEARCH/ sort type for Numeric Labels being Ascending and Numeric Values
Descending.

 

[Domenic]

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.

2) The Numeric Label 14 is found at Column 16.

3) The corresponding Numeric Value is 2.

4) You'd like to add 1 to that value, which gives you 3.

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.

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?

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).

8) You'd like to searching for the Numeric Label (14), starting from
Column 1 and moving right.

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)?

 

[Sam via OfficeKB.com]

Hi Domenic,

Thanks for reply.

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.

 

[Domenic]

Assumptions:

F10:FV10 contains the Numeric Labels

F11:V11 contains the Numeric Values

X11 contains the criteria

Formulas:

First, define the following names...

Select Y11

Insert > Define > Name

Name: NLabels

Refers to: =Sheet1!$F$10:$V$10

Click Add

Name: NNV

Refers to: =INDEX(NValues,Pos)+1

Click Add

Name: NValues

Refers to: =Sheet1!$F11:$V11

Click Add

Name: Pos

Refers to: =MATCH(Sheet1!$X11,NLabels,0)

Click Add

Name: SubRange

Refers to:

=INDEX(NLabels,MATCH(NNV,NValues,0)):INDEX(NLabels,MATCH(2,1/(NValues=NNV
)))

Click Ok

*Change the sheet reference accordingly.

Then, enter the following formula in Y11, and copy down if necessary...

=IF(ISNUMBER(MATCH(NNV,NValues,0)),(MAX(IF(SubRange<$X11,COLUMN(SubRange)
-MIN(COLUMN(SubRange))+1))+1)+(MATCH(NNV,NValues,0)-1),MATCH($X11,NLabels
,0))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you so very much for all your assistance and perseverance.

Your Formula has done the job Brilliantly!

Sorry it took me so many attempts to explain.

Cheers,
Sam

Assumptions:

F10:FV10 contains the Numeric Labels

F11:V11 contains the Numeric Values

X11 contains the criteria

Formulas:

First, define the following names...

Select Y11

Insert > Define > Name

Name: NLabels

Refers to: =Sheet1!$F$10:$V$10

Click Add

Name: NNV

Refers to: =INDEX(NValues,Pos)+1

Click Add

Name: NValues

Refers to: =Sheet1!$F11:$V11

Click Add

Name: Pos

Refers to: =MATCH(Sheet1!$X11,NLabels,0)

Click Add

Name: SubRange

Refers to:

=INDEX(NLabels,MATCH(NNV,NValues,0)):INDEX(NLabels,MATCH(2,1/(NValues=NNV
)))

Click Ok

*Change the sheet reference accordingly.

Then, enter the following formula in Y11, and copy down if necessary...

=IF(ISNUMBER(MATCH(NNV,NValues,0)),(MAX(IF(SubRange<$X11,COLUMN(SubRange)
-MIN(COLUMN(SubRange))+1))+1)+(MATCH(NNV,NValues,0)-1),MATCH($X11,NLabels
,0))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Extraction of previous Data Sample:

[quoted text clipped - 64 lines]

Cheers,
Sam

 

[Sam via OfficeKB.com]

Hi Domenic,

First-off: apologies for re-opening Thread.

Your Formulae provided below works but there are some exceptions where it
does not provide the Expected Results.

Searching for Numeric Label 10 and returning the Column Number of its
corresponding New Numeric Value based on the agreed method for searching and
increasing its original Numeric Value by 1 (one) should Return Column Number
6 as the correct result . Using the Layout and Sample Data below it Returns
Column Number 10.

I appreciate any further help that you may be able to provide.

Example Layout:
Row10 Numeric Labels Column Number 1("F") to Column Number 17("V")
Row11 Numeric Values Column Number 1 ("F") to Column number 17("V")

Sample Data:
Row10 Col1 = 0
Row11 Col1 = 13
Row10 Col2 = 1
Row11 Col2 = 12
Row10 Col3 = 2
Row11 Col3 = 11
Row10 Col4 = 5
Row11 Col4 = 11
Row10 Col5 = 3
Row11 Col5 = 8
Row10 Col6 = 4
Row11 Col6 = 6
Row10 Col7 = 6
Row11 Col7 = 6
Row10 Col8 = 8
Row11 Col8 = 6
Row10 Col9 = 9
Row11 Col9 = 6
Row10 Col10 = 10
Row11 Col10 = 6
Row10 Col11 = 12
Row11 Col11 = 15
Row10 Col12 = 7
Row11 Col12 = 4
Row10 Col13 = 11
Row11 Col13 = 3
Row10 Col14 = 13
Row11 Col14 = 2
Row10 Col15 = 15
Row11 Col15 = 2
Row10 Col16 = 21
Row11 Col16 = 2
Row10 Col17 = 31
Row11 Col17 = 2

Thanks
sam

Assumptions:

F10:FV10 contains the Numeric Labels

F11:V11 contains the Numeric Values

X11 contains the criteria

First, define the following names...

Select Y11

Insert > Define > Name

Name: NLabels

Refers to: =Sheet1!$F$10:$V$10

Click Add

Name: NNV

Refers to: =INDEX(NValues,Pos)+1

Click Add

Name: NValues

Refers to: =Sheet1!$F11:$V11

Click Add

Name: Pos

Refers to: =MATCH(Sheet1!$X11,NLabels,0)

Click Add

Name: SubRange

Refers to:

Click Ok

*Change the sheet reference accordingly.

Then, enter the following formula in Y11, and copy down if necessary...

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Extraction of previous Data Sample:

[quoted text clipped - 64 lines]

Cheers,
Sam

 

[Domenic]

Hi Sam!

No apologies necessary. It looks like we're not in the same
wavelength...

When I gave you some sample criteria, along with what I thought would be
the correct result, you confirmed them to be correct. But, according to
your last example, this would not be the case.

I provided the following example where the first six columns had the
following Numeric Labels and corresponding Numeric Values...

0 2 1 4 6 12
16 12 10 10 10 7

....and where the criteria and results were as follows...

Criteria ----------> Result

1 ----------> Column 3

4 ----------> Column 4

6 ----------> Column 5

But according to your last post, the correct results should be...

Criteria ----------> Should Be

1 ----------> Column 3

4 ----------> Column 3

6 ----------> Column 3

Is this correct? And just to be sure, let's go through a few more
examples using the values in your last post...

Criteria ----------> Result

1 ----------> Column 14

7 ----------> Column 11

4 ----------> Column 6

3 ----------> Column 5

12 ----------> Column 11

Are these correct? Now, let's replace 5 with 14, and 12 with 5. If the
criteria is 5, is Column 7 the correct answer? And lastly, if the
criteria is 0, what should the correct answer be?

Hi Domenic,

First-off: apologies for re-opening Thread.

Your Formulae provided below works but there are some exceptions where it
does not provide the Expected Results.

Searching for Numeric Label 10 and returning the Column Number of its
corresponding New Numeric Value based on the agreed method for searching and
increasing its original Numeric Value by 1 (one) should Return Column Number
6 as the correct result . Using the Layout and Sample Data below it Returns
Column Number 10.

I appreciate any further help that you may be able to provide.

Example Layout:
Row10 Numeric Labels Column Number 1("F") to Column Number 17("V")
Row11 Numeric Values Column Number 1 ("F") to Column number 17("V")

Sample Data:
Row10 Col1 = 0
Row11 Col1 = 13
Row10 Col2 = 1
Row11 Col2 = 12
Row10 Col3 = 2
Row11 Col3 = 11
Row10 Col4 = 5
Row11 Col4 = 11
Row10 Col5 = 3
Row11 Col5 = 8
Row10 Col6 = 4
Row11 Col6 = 6
Row10 Col7 = 6
Row11 Col7 = 6
Row10 Col8 = 8
Row11 Col8 = 6
Row10 Col9 = 9
Row11 Col9 = 6
Row10 Col10 = 10
Row11 Col10 = 6
Row10 Col11 = 12
Row11 Col11 = 15
Row10 Col12 = 7
Row11 Col12 = 4
Row10 Col13 = 11
Row11 Col13 = 3
Row10 Col14 = 13
Row11 Col14 = 2
Row10 Col15 = 15
Row11 Col15 = 2
Row10 Col16 = 21
Row11 Col16 = 2
Row10 Col17 = 31
Row11 Col17 = 2

Thanks
sam

Assumptions:

F10:FV10 contains the Numeric Labels

F11:V11 contains the Numeric Values

X11 contains the criteria

First, define the following names...

Select Y11

Insert > Define > Name

Name: NLabels

Refers to: =Sheet1!$F$10:$V$10

Click Add

Name: NNV

Refers to: =INDEX(NValues,Pos)+1

Click Add

Name: NValues

Refers to: =Sheet1!$F11:$V11

Click Add

Name: Pos

Refers to: =MATCH(Sheet1!$X11,NLabels,0)

Click Add

Name: SubRange

Refers to:

Click Ok

*Change the sheet reference accordingly.

Then, enter the following formula in Y11, and copy down if necessary...

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Extraction of previous Data Sample:

[quoted text clipped - 64 lines]

Cheers,
Sam

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you so much for reply.

Unfortunately, I can confuse the issue without even trying! I forget my own
"Method" for searching for the Numeric Labels and returning the Column Number
for the Numeric Value - forgot to increment the value by one when looking
where the Numeric Label and Numeric Value should be placed in the Rows.

However, I think I'm with it now - well almost!

But according to your last post, the correct results should be...

Criteria ----------> Should Be

1 ----------> Column 3

4 ----------> Column 3

6 ----------> Column 3

Is this correct?
Yes

And just to be sure, let's go through a few more examples using the values in your last post...

Criteria ----------> Result

1 ----------> Column 14 Should be Column 2

7 ----------> Column 11

4 ----------> Column 6

3 ----------> Column 5

12 ----------> Column 11

Are these correct?

Almost, Numeric Label 1(one) should be Column 2

Now, let's replace 5 with 14, and 12 with 5.
If the criteria is 5, is Column 7 the correct answer?

Not sure what you mean?

And lastly, if the criteria is 0, what should the correct answer be?

Column Number 1.

Based on the Sample Data Column Number 1 (because it is already in Column
Number 1 and has the highest Numeric Value, so it will stay in Column number
1 - nowhere else for it to move). But criteria 0 (Numeric Label 0) may not
always be in Column Number 1, it could be in Column Number 2 or 3 etc and its
Numeric Value will decide what Column Number it moves to - like other
criteria (Numeric Label). Criteria 0 (Numeric Label 0) is the first Numeric
Label in ascending order.

Cheers,
Sam

Hi Sam!

No apologies necessary. It looks like we're not in the same
wavelength...

When I gave you some sample criteria, along with what I thought would be
the correct result, you confirmed them to be correct. But, according to
your last example, this would not be the case.

I provided the following example where the first six columns had the
following Numeric Labels and corresponding Numeric Values...

0 2 1 4 6 12
16 12 10 10 10 7

...and where the criteria and results were as follows...

Criteria ----------> Result

1 ----------> Column 3

4 ----------> Column 4

6 ----------> Column 5

But according to your last post, the correct results should be...

Criteria ----------> Should Be

1 ----------> Column 3

4 ----------> Column 3

6 ----------> Column 3

Is this correct? And just to be sure, let's go through a few more
examples using the values in your last post...

Criteria ----------> Result

1 ----------> Column 14

7 ----------> Column 11

4 ----------> Column 6

3 ----------> Column 5

12 ----------> Column 11

Are these correct? Now, let's replace 5 with 14, and 12 with 5. If the
criteria is 5, is Column 7 the correct answer? And lastly, if the
criteria is 0, what should the correct answer be?

Hi Domenic,

[quoted text clipped - 120 lines]