Getting the Last Filled Row#/Cell Addres for a Multi-Column Range With/Without Arrays

[e4excel]

Getting the Last Filled Row#/Cell Address for a Multi-Column Range With/Without Arrays

Dear Forum,

I have searched for several threads on getting the Last Filled Row for a Single Columnar Range However if the Range is Multi-Columnar and the Data is scattered so that any Column is this Matrix can have the Data Filled..

Example:
In the Columns A:D
the Col A can have the Max Data filled however it can be also that the other columns can have the Data in the Max Row..

SO I need to create an Index Funtion which starts from A2: but the Max Data Needs to be like Dn where "n" is the Max no of the Filled data in any of the above 4 Columns.

Is this possible to achieve with a formula With/Without Arrays

Possible Usage : A2:Index(A:D, LastFilledNo ,4)


Warm Regards
e4excel

[e4excel]

Some Formulas I cam across and Modified..for a Single Range

=LOOKUP(2,1/(A:A<>""),A:A)) ------- This gives the Last Filled Cell's Content 
 
=LOOKUP(2,1/(A:A<>""),ROW(A:A))  ------- This gives the Last Filled Cell's Row#  

But How do I get the Row# or the Cell Address or Even the Cells Content for that matter using a formula with or without Arrays..

Warm REgards
e4excel

[sweep]

Hi,

To obtain the last populated row in your range, you could use this:

=MAX(ROW(A2:D20000)*(A2:D20000<>""))

entered as an array (with CTRL, SHIFT and ENTER)

[Haseeb Avarakkan]

e4excel,

Define 4 names,

Name: ColA
refers to: =MAX(IFERROR(MATCH({"zzzzzzzzzzzzzzzzzzzz",9E300},$A:$A),0))

ColB, ColC, ColD on refers to change $A:$A to , $B:$B, $C:$C, $D:$D respectively.

Then you can use,

=MAX(ColA,ColB,ColC,ColD)

[e4excel]

Thanks both of You Sweep and Haseeb A...

But is there a way of avoiding the C+S+E? and Defining Multiple Names?, if the No of Columns are More like using the Fisrt and Last COlumn in the code and that too without an array..

If not then I am still good but I am still hopeful for a single compact code without C+S+E..

Thanks in advance..

Warm Regards
e4excel

[e4excel]

Dear Haseeb,

Can a Single Formula be made to look at a Column Range if I were using the
Index : Index Functionality to get the Columns First and then using your approach get the Max No of Rows in these generated Columns lets say..

I get the Columns Name using the Match Functionality the first column using the Search for "Sr No" and the last column searches for the column heading as "Totals" then this wiuld be the the columns size and then i need the no of rows in any of these columns.

[Haseeb Avarakkan]

This is little lengthy,

=INDEX($A:$A,MAX(IFERROR(MATCH("Sr NO",$A:$A,0),1),IFERROR(MATCH("Sr NO",$B:$B,0),1),IFERROR(MATCH("Sr NO",$C:$C,0),1),IFERROR(MATCH("Sr NO",$D:$D,0),1))):INDEX($D:$D,MAX(IFERROR(MATCH("Totals",$A:$A,0),1),IFERROR(MATCH("Totals",$B:$B,0),1),IFERROR(MATCH("Totals",$C:$C,0),1),IFERROR(MATCH("Totals",$D:$D,0),1)))

[e4excel]

Haseeb,

No I meant the reason why I was asking for a single formula was to get it in your way without Arrays, I can get the Matrix Size or even if I dont get that I want to actually provide the Entire Range in the code in order to get the Max no of Rows..
My Focus is to get the Range such as A2:D and the Max no of Rows which can be present in any of the 4 columns between A and D..
Example:-

The same formula yiou were providing without Arrays and also one single formula if possible for an ENtire Range..

MAX(IFERROR(MATCH({"zzzzzzzzzzzzzzzzzzzz",9E300},$A:$D),0)) 
 
=MAX(ROW(A2:D20000)*(A2:D20000<>"")) 
Is the above possible without arrays?  

[Haseeb Avarakkan]

As far as I know, can't use MATCH like =MATCH({"zzzzzzzzzzzzzzzzzzzz",9E300},$A:$D) lookup_array with more than 1 column/row. You need separate formula for each column as above mentioned.

=MAX(ROW($A$2:$D$20000)*($A$2:$D$20000<>"")) Basically this require CSE key, but If you define a name with this formula will work without CSE.

If you want to use it in a formula include them with INDEX,

=MAX(INDEX(ROW(A2:D20000)*(A2:D20000<>""),0)) But I think this may cause sheet performance.

I still don't understand. Why do you need a single formula??? Post #4 is not workable???

[e4excel]

No that is working no doubts about that the reason of asking for a singke formula was due to the fact that the No of Columns wont be fixed and therefore if I had a formula where the Columns Range is included it becomes easier..

Lets say my Data is such that I have 3 Columns from A till C and then there are some 4 cells filled in col A with some blanks and then there are like 8 cells in the Col B which are filled and then in Col D there are 4 cells which are filled so I want to get the Matrix as
A2:C8 based on the Max length from any of these columns now in this case it is 8 because the column C contains the Max no of Filled Row no..

Now that works..! but let me come back on that still...

Thanks once again Haseeb..