Finding the Column with the MAX Height in comparson wth othr Columns of UNEVEN Height

Finding the Column with the MAX Height in comparison with other Columns of UNEVEN Height

I need two things :
1. I have several columns starting from Column B till Column F, each column having values starting from the third ROW.
Ex: -
Lets say Column B contains two values in B3 and B4, Col C three values in C3 C4 and C5, Col D four values D3,D4,D5 and D6, Col E two values in E3 and E4, Column F five values F3,F4,F5,F6 and F7.

So, the answer is F3:F7.

2.I am using 10^{4,3,2,1,0} in a particular portion of a formula, Now the number of elements in the array or in the Curly Braces depends on the number of Columns filled from Column B as explained in the Point 1.
Now, since I have five columns under consideration I have this order as mentioned here {4,3,2,1,0} , I would like to know whether I could make this dynamic, as in if there were only four columns then this would be {3,2,1,0} and if more this array could self-fill and expand..
If that's possible, then how do we use it in the formula, Is it by the virtue of the INDIRECT function?

Warm regards
e4excel

e4excel, I'm sure one of the whizzes here can improve on this but I believe the below would work:

Please Login or Register  to view this content.

A sample file would be useful...

I think in all honesty given no. of columns is variable you will be best served either storing the count of values in each column in a common row across all columns or make use of a UDF...

Also, can we assume no blanks are interspersed amongst the ranges ... ie you don't have values in F3:F4 then blank F5 and values in F6:F10 for ex...

The return of 11 from the prior formula would imply that the formula found a max of 2 values in the ranges specified... ie 10^0 + 10^1

I'm still not sure I follow what the intention is but I did a few things...

On Options Sheet I altered the formulae in A2 onwards such that:

Please Login or Register  to view this content.

I then inserted a new Dynamic Named Range:

Please Login or Register  to view this content.

I then used the following (entered as array given TRANSPOSE) to generate your Calculated Model:

Please Login or Register  to view this content.

As you add values to C2:C11 the ID should change (assuming different options selected - eg setting B6 to Config and B7 to Bravo should generate a new Model No.... also deleting / adding a column to Options should change the calc also (adding a further digit etc...)

As I say I'm not sure exactly what you want to do but I hope it points you in the right direction...

Figured I would post up a working sample just in case you come online early in the morning -- you're a few hours ahead of us here in the UK.

The formulae is in cell E5 on sheet Main.

If you add/remove columns/rows on Options you should find the resulting ID value updates accordingly (increasing/decreasing no. of digits) ... eg if you clear contents of Options!F1 the value in E5 on Main sheet will alter, conversely if you add a value to Options!G1 the number should extend by 1 digit etc to reflect the new "Option" available.

You should be able to achieve the Sumproduct without needing the Transpose (ie without need for CSE array) but in all honesty I'm too tired to review now, will do so in the morning.

Below is a revised formula which does not require CTRL + SHIFT + ENTER so therefore can be considered slightly more "robust" from end-user perspective...

Please Login or Register  to view this content.

I hope that helps.

Have you checked you're on Auto Calculate ??

In 2003: Go to Tools -> Options -> Calculation Tab -> check set to Auto

(Sounds as though it is on Manual - hitting F9 will force a calculation)

Probably best to break down into separate posts...

First: creating dynamic named range (DNR) _OptionsTbl

The purposes of this DNR is to create a dynamic reference table such that as rows / columns are added/removed so the reference table resizes automatically without need for manual intervention.

The formula we use to create this is as you state:

Please Login or Register  to view this content.

Let's break out into separate parts... first dealing with the MATCH statements

This:

Please Login or Register  to view this content.

Will return for us the Row number of the last cell in Column A containing a number, this is done utilising Binary Search algorithm and BigNum (9.99...)

Per our example file let's assume result is 6
(row 6 is last row containing any "choices)

This:

Please Login or Register  to view this content.

Will return the Column number of the last cell in Row 1 containing Text value, this also utilises Binary Search algorithm but based on a big text value (z repeated 255 times) as opposed to a big number.

Per our example let's assume the result is 6
(column 6 (F) being the last column to contain an "Option")


We can use the resulting values in a subsequent INDEX function (Reference form) to return for us a Range. INDEX can be viewed as having 3 key components (we will ignore Area_Num as it's not relevant and is optional argument)

Please Login or Register  to view this content.

So using our example values where row & column are both 6:

Please Login or Register  to view this content.

Essentially references row 6 and column 6 of our entire sheet... namely F6... this is the cell that represents the bottom right hand corner reference of our entire reference table.

Were we to enter:

Please Login or Register  to view this content.

into a cell this would simply return the contents of Options!F6, however we are looking to utilise our INDEX in reference form, ie to return a range and we can use in this manner as illustrated below:

Please Login or Register  to view this content.

Thereby creating a Range of Options!B3:F6

And there you have your Dynamic reference table... as you add rows so the row value used in the INDEX will adjust, likewise with Columns ... thus you're range B3:?? will adjust as you add/remove data.... and you can now reference this evolving table elsewhere via the DNR without having to alter references to it directly.

Assuming you "get" the Named Range bit in the prior post we can move on to the formula itself on sheet "Main".

Mods: I am using Code tags for non-code purely for presentational purposes

Before continuing I would make the following points:

Please Login or Register  to view this content.

AIM OF FORMULA

The aim of the formula is to generate an ID "Number" based upon the "Choices" made by the end user for each given "Option".

The "size" of the number is determined by the number of "Options" listed in Row1 on Options sheet.
That is to say if we have 4 options to choose from the resulting value should be 4 digits in length (eg 1111), if there were 5 "options" the resulting value should be 5 digits in length (eg 11111) etc...

The digits themselves represent the Index position of the "Choice" within the parent list.
So if for "Option 1" the user selects the first listed value in the resulting validation list the digit used in the number should be 1, were the end user were to choose the 4th "Choice" from the validation list the digit should be 4 etc...

The ordering of the digits in the resulting number is ordered based upon the order of the Options list.
ie the option listed in B1 on Options shall dictate the first digit in our number, the last option listed in row 1 shall generate the last digit in our number... the ordering of the options on sheet "Main" is on this basis not important... it is the order in row 1 on Options that is the predetermining factor.

So we have:

=SUMPRODUCT((ISNUMBER(MATCH(_OptionsTbl,$C$2:$C$11,0)))*ROW(INDIRECT("1:"&ROWS(_OptionsTbl)))*10^((C OLUMNS(_OptionsTbl)-COLUMN(INDIRECT("A1:"&ADDRESS(1,COLUMNS(_OptionsTbl)))))))

This looks worse than it is ...

Firstly we want to validate the Choices made in C to ensure they can be found in our DNR (_OptionsTbl)

Please Login or Register  to view this content.

We then want to multiply that by the Row being iterated (at which point the Boolean return of TRUE/FALSE will be coerced to Integer equivalent of 1/0 respectively)...

To get the ROW being iterated we can use:

Please Login or Register  to view this content.

Breaking the above down further...

ROWS:
if our DNR refers to Options!B3:F6 then it follows that ROWS(_OptionsTbl) will return 4 as there are 4 rows within that range, namely rows 3,4,5 & 6

INDIRECT:
The INDIRECT will generates a reference to 1:4

ROW:
As we iterate the INDIRECT reference ROW will in turn generate {1,2,3,4}

So at this point we have

(Booleans)*({1,2,3,4}) ... which is essentially returning for us the INDEX positions of the "Choices" made...

If we consider the sample file where the "Choices" index positions were:

Please Login or Register  to view this content.

If at this point we executed just:

Please Login or Register  to view this content.

The result would be 9 ...

Please Login or Register  to view this content.

However this will not work for us as the value we want to generate is in fact:


11214 -- ie 11,214

So we must take the index position and multiply by 10 ^ x ... eg

Please Login or Register  to view this content.

which if analysed further becomes

Please Login or Register  to view this content.

which if analysed further becomes

Please Login or Register  to view this content.

which gives us 11214 ... this method is provided for us by Ron C.

We know from your prior post we can get the result for 5 "Option" setup using:

Please Login or Register  to view this content.

However we want the { } to be variable given we want to vary the number of columns we have... if we had 6 columns we would need 10^5 down to 10^0 ... if we had only 2 columns we would simply want 10^1 to 10^0 ....

Given we know that this calculation is tied directly to the no. of "Options" we have (ie columns in our DNR) we can use that info to generate this calculation:

Please Login or Register  to view this content.

We can ignore the 10^ as this is fixed ... what varies as we iterate is the power be it 4 -> 0, 5 -> 0 etc etc...

COLUMNS(_OptionsTbl) will work like ROWS but return for us the number of COLUMNS present in our range... in our case we have 5 Columns (B3:F6)

We know that the power should decrease as we iterate... ie first digit is to to be the biggest & last digit the smallest ... so we use in short the COLUMNS value and subtract from it with each iteration, eg:

Please Login or Register  to view this content.

so as to generate:

Please Login or Register  to view this content.

this will in turn generate

Please Login or Register  to view this content.

To analyse this part further

Please Login or Register  to view this content.

Working backwards

Please Login or Register  to view this content.

will return a reference in traditional notation, eg:

Please Login or Register  to view this content.

So we use ADDRESS to generate for us a reference based on the number of columns in our DNR (5), using the example:

Please Login or Register  to view this content.

We then use INDIRECT as before to create a Range through which we can iterate:

Please Login or Register  to view this content.

We use COLUMN as we did ROW before to return for us the COLUMN number of those cells as they are iterated:

Please Login or Register  to view this content.

Thus we get our

Please Login or Register  to view this content.

And we have our formula...

PHEW...

I'm not maths whizz so the terminology I've used may be incorrect, apologies, hopefully someone will correct me where I've used terms inaccurately.

I would point out at this juncture that what you're doing is really quite complex (for me at least) ... so I've explained it as best I can ... if you don't understand it all then I'm afraid there's not much more I can do.

The approaches being used here are not for the faint hearted and are not the sort of thing you will come across often ... I would consider myself pretty adept at this stuff but I found this one quite a challenge to resolve....

To put it another way Ron Coderre from whom you obtained the original concept is a Microsoft MVP Excel of which there are only around 100 in the world... from that I think you can deduce how complex/challenging your question was/is... on that basis don't be disheartened if you don't understand all facets of the proposed solution.