Retrieving the address of the last cell before the FIRST blank in a range.

**Tattooed soul** · 09-28-2007, 01:20 PM

Retrieving the address of the last cell before the FIRST blank in a range.

OK, I have searched the web through six different search engines and explored too many dead end solutions to this problem that I am nearly ready to just scrap the whole sheet.

The problem is very simple.

I need to return the address of the last cell that contains data before the FIRST blank cell.

Heck, I don’t even need to have the address, I can just index the position.

Problem is this question has been posted on nearly every excel help forum from here to pokipsy.
Unfortunately EVERY solution I have seen fails the “FIRST blank” requirement.

I have a column of data that never has blanks until the end of the data. I need to know what that cell address is in order to identify a range.

This data has a table above it and below it so none of the “dynamic range” or “Dynamic range name” solutions will work.

How do you get that address without the function continuing to the last blank cell?

**NBVC** · 09-28-2007, 01:29 PM

Does this give you the solution?

=ADDRESS(INDEX(A:A,MATCH(9.999999999E+307,A:A)),1)

you can replace A:A with an actual defined range....

This assumes the last cell will contain a number and assumes as per your quote:

I have a column of data that never has blanks until the end of the data

**Tattooed soul** · 09-28-2007, 01:39 PM

Further clarification…

What I am doing is checking a table for records that fit in the following criteria,

=SUM(((Range<>"accepted test")*(Range<>"release w/o test"))*((Range=1)+(Range=11)))

Problem is the table is sandwiched between two other tables and the data changes each day.

I manually enter the Range every day for five similar CSE formulas and I am trying to automate this task.

Keep in mind that the idea is to automate the task. So having to actually highlight the range used is no different than manually entering the information like I currently am doing.

**Tattooed soul** · 09-28-2007, 01:41 PM

Originally Posted by NBVC

Does this give you the solution?

=ADDRESS(INDEX(A:A,MATCH(9.999999999E+307,A:A)),1)

you can replace A:A with an actual defined range....

This assumes the last cell will contain a number and assumes as per your quote:

Thanks for trying but no. this is one of the solutions I have already tried.
It fails the "First blank" requirement.

**NBVC** · 09-28-2007, 01:52 PM

How about:

=INDEX(A1:A100,MATCH(TRUE,A1:A100="",0)-1)

entered as array formula using CSE keys..

this will give you last value before first blank.....

within another function, it will give the cell reference.

edit: of course, since it is an array formula, you can't have full column references, like A:A, so make the range as large as possible to ensure encapsulation of the data

**Unreal** · 09-28-2007, 03:15 PM

Originally Posted by NBVC

How about:

=INDEX(A1:A100,MATCH(TRUE,A1:A100="",0)-1)

entered as array formula using CSE keys..

this will give you last value before first blank.....

within another function, it will give the cell reference.

edit: of course, since it is an array formula, you can't have full column references, like A:A, so make the range as large as possible to ensure encapsulation of the data

Wow, thanks NVBC that one works flawlessly. I tried something similar to this approach earlier but I don’t have enough experience with range function conglomerations and couldn’t pull it off. I was actually trying to use various formations of isblank, cell, index, match, etc…

OK now I am going to show off my novice flag.

Given the formula,

=SUM(((Range<>"accepted test")*(Range<>"release w/o test"))*((Range=1)+(Range=11)))

and I have a range of alphanumeric data E40:E228, J40:J228, Q40:Q228 and T40:T228 to query, in that order, per argument in the function.

How would one make these ranges update automatically as the range changes day to day?

I thought if I could just find a way to determine the end cell I could implement that, but as usual, I now can’t figure out how to actually use the new method to accomplish my goal!
Sound familiar?

Thanks for all the help!

BTW,
I had to reregister as another user. The board allowed me to register with a name that had a space and this has caused at least 12 types of hell for me to log on under the "Tattooed soul" screen name.....
I have already sent messages to the forum admin...

**NBVC** · 09-28-2007, 03:21 PM

So you want the E228, J228, Q228 and T228 to be dynamic based on the value before the last blank?

Btw... Are you counting records based on criteria? If so, maybe you are looking for a Sumproduct() formula rather than a Sum() formula?

**Unreal** · 09-28-2007, 03:37 PM

Originally Posted by NBVC

So you want the E228, J228, Q228 and T228 to be dynamic based on the value before the last blank?

Btw... Are you counting records based on criteria? If so, maybe you are looking for a Sumproduct() formula rather than a Sum() formula?

Well, I’m not certain actually.

What I am dealing with here is database records of a manufactured product that has been tested for assembly performance.

The CSE formula,

=SUM(((Range<>"accepted test")*(Range<>"release w/o test"))*((Range=1)+(Range=11)))

is simply a formula that counts the number of records that designate a product that has actually completed a test and been accepted “designated in column E”,
has not been released with out test “designated in column J”
and has a value of “1” in column Q, OR, a value of “11” in column T.

This formula is entered as a range function.

This formula’s function is to count the products that have accepted tests in my range.
I also have similar functions that count “product released w/o test” “repaired product” and other issues.

So I don’t think the SUMPRODUCT function will apply.

If I can somehow make these formulae dynamic to the point where all I have to do is drag the group of formulae to the first line of the adjacent table I would save a lot of time on daily reporting.

**NBVC** · 09-28-2007, 03:40 PM

I think we are the point where you need to display a sample of your spreadsheet... you can attach a zipped excel (2003 or earlier) version here... and I can take a look....

I'm off in 20 minutes...though... so the quicker the better....

**NBVC** · 09-28-2007, 04:05 PM

...okay....i'm off.....until next time I have time to sign in...

just as a test (that may or may not work)...try the following formula confirmed with CTRL+SHIFT+ENTER...

Please Login or Register  to view this content.

this assumes you basically have a whole blank row so that that columns E, J, Q and T have the same "row previous to first blank".... if not, just replace the Index() functions in each condition to the one that meets the "row before first blank" criteria...

**daddylonglegs** · 09-28-2007, 05:13 PM

Originally Posted by Unreal

=SUM(((Range<>"accepted test")*(Range<>"release w/o test"))*((Range=1)+(Range=11)))

Is it possible for the last two conditions to apply on the same row? If so the above formula would count that row twice. You could avoid that...and avoid CSE as NBVC suggests by switching to SUMPRODUCT, i.e.

=SUMPRODUCT((Range1<>"accepted test")*(Range2<>"release w/o test")*((Range3=1)+(Range4=11)>0))

**Unreal** · 10-01-2007, 09:36 AM

Originally Posted by NBVC

I think we are the point where you need to display a sample of your spreadsheet... you can attach a zipped excel (2003 or earlier) version here... and I can take a look....

I'm off in 20 minutes...though... so the quicker the better....

Sorry I couldn't get back to you last Friday!

I am happy to display the worksheet. The problem is however that my employer would not look so favorably upon my employment status would that I divulge any pertinent information publicly online.
Soooo… I will have to “sanitize” the worksheet prior to posting it.
I should have an example by the end of the day…

The formula you provided in post #10 meets the requirement of stopping at the first empty cell but it does not calculate the correct number of records for some reason.

I think the “fix” is to be found in some form of this formula for sure.

Thanks for all of your help.

I will post an example as soon as I am able.

**Unreal** · 10-01-2007, 09:51 AM

Originally Posted by daddylonglegs

Is it possible for the last two conditions to apply on the same row? If so the above formula would count that row twice. You could avoid that...and avoid CSE as NBVC suggests by switching to SUMPRODUCT, i.e.

=SUMPRODUCT((Range1<>"accepted test")*(Range2<>"release w/o test")*((Range3=1)+(Range4=11)>0))

Ahhhh… I see now. The SUMPRODUCT function behaves exactly as the CSE SUM function. I was not aware of this.

OK, well this is an improvement in functionality by avoiding volatility but it does not help me automate the calculation.

I will employ the SUMPRODUCT function and submit an abbreviated and sanitized version of the spreadsheet for your perusal this afternoon.

Thanks for all of your help….

OBTW, what limitations are to be expected with the SUMPRODUCT function?
I mean, can I employ it everywhere there is a CSE SUM function?

**NBVC** · 10-01-2007, 10:00 AM

Using Sumproduct() over the CSE form of Sum(IF(...)) will depend on how we define your "first blank" criteria....

...better to wait for your sample......

**Unreal** · 10-01-2007, 03:35 PM

Originally Posted by NBVC

Using Sumproduct() over the CSE form of Sum(IF(...)) will depend on how we define your "first blank" criteria....

...better to wait for your sample......

Sorry mates but I got called away on a problem this afternoon.
I will have something for you tomorrow. End of the day now and I am roasted!

Thanks for all of your help.

**Unreal** · 10-02-2007, 12:05 PM

All right then,
Here is the “corrections Sheet” I must calculate every day.

The data base I work from is fairly straightforward but unfortunately it often has errors in it. I must detect these errors and correct them every day. The database is a table of every product tested and each record is a unique instance of every accepted test and test point failure. This is somewhat confusing because each product tested can generate multiple records for each test depending on how many failures there are, an accepted test without failure generates only one record. It is possible that any one product will test multiple times generating multiple failures each time. The problem arrises from the fact that any one failure may generate symptomatic failure caused by one particular failure mode.
It is my job to identify these instances and identify the “root cause” and report the actual repair that corrected the issue.

Unfortunately this is mostly a “manual” process, that is, I simply have to sift through these record manually and think through the process and identify the proper repair for each product that is listed in this daily table.

Once I correct the data I filter the corrected data back into the daily table and generate reports from this data in the form of 7 pivot tables.

To obtain this corrected data I filter records out of the “unique records” table that match the following criteria,
“stand errors”, “misc. issues”, “release w/o test” and “missing repair data”
These records invariably contain all of the incorrect repairs and hence the faulty data.

This yields the table found in E2:U41

I then use the “Serial number” column found in G2:G41 to filter out all of the records from the main database table, this yields the table found in E44:U316.

This is the table that I sift through manually and make my corrections in. the corrections can be seen in blue text in column O44:O316.

Here is what I am trying to correct and automate….

The three formulas to the left of the table in A44:A51, highlighted in yellow and red, contain formulas that calculate the number of records that are actual “repairs”, “Accepts” and “Releases”.
These numbers help me check my work when I re-filter the data back into unique records at the bottom of this sheet.

The checksum cells help me keep track of totals and accuracy while I work as well. All checksums should equal zero, or I have a problem.

I am looking to write a formula that dynamically resizes to the end of my corrections table each time I generate the table. What I want to be able to do is simply drag the three formulas to the top of the table’s first line and have it automatically find the end of the data and make the calculation. Right now I have to manually re-enter the end cell every day…
The problem is that I can’t use dynamic ranges or names in the traditional sense because I generate more tables at the bottom of this corrections table and this blows the dynamic range every time… hence the requirement of stopping after the “FIRST BLANK”.

This is the only manual page in my report process, most everything else is fairly automated.

Unfortunately the only way to obtain this data is to manually sift through the data.
Automating this simple process will save me a good amount of time.

I hope this is clear enough.

Thanks for all of your help.

**NBVC** · 10-02-2007, 12:49 PM

You mean as per attached?

**Unreal** · 10-03-2007, 11:56 AM

Originally Posted by NBVC

You mean as per attached?

Thanks very much NVBC.

That cures my ills perfectly!

I have applied the same technique to the first checksum as well.

Where,

Please Login or Register  to view this content.

becomes,

Please Login or Register  to view this content.

It seems to work very well indeed.

I am trying to parse out the logic so I can understand the technique and remember its form. Could you maybe give me a simple explanation of what is going on with this technique?

Thanks again for all of your help.

**NBVC** · 10-03-2007, 12:17 PM

Hi,

Glad it worked, finally.

I assume you want help understanding this part since it is the only difference to your original formula: INDEX(T28:$T$961,MATCH(TRUE,T28:$T$961="",0)-1

Well, as you know, it finds the first blank within the indexed range and goes back 1 row.

The Match() part is where the magic happens... The condition T28:T961="" returns an array of trues/falses. The Match looks for the first True and returns the position number within the index range. and then I subtract 1 to get the previous number (which is the last text field before the first blank).

Note: when you use the Index() function within formulas like this, it returns the actual address instead of the data at that address.

See the Index and Match functions in Excel help for more detail about the way they work.

Hope this helps.

**Unreal** · 10-03-2007, 02:16 PM

Originally Posted by NBVC

...I assume you want help understanding this part since it is the only difference to your original formula: INDEX(T28:$T$961,MATCH(TRUE,T28:$T$961="",0)-1...

Exactly!

Great explanation BTW.

This technique has already automated much more of my spreadsheet.

It has also turned into another dilemma, as is frequently the case with these situations.

If you look at my sheet again you will notice that there are three filtered tables at the end of the sheet that assemble the unique corrected data from the corrections table above.

I have added the Index / Match technique to the checksums under each filter criteria, the problem is that the corrections table “floats”. That is its position changes with each days data.
If you notice the checksum it subtracts each corresponding total “Actual Repairs”, “Actual Accepts”, and “Actual Releases” from the counted total in each corresponding filtered unique table.

Each day I will have to change that ending cell reference to match the new position of the corrections table.

Is there a way to make the formula find that position automatically?

I have changed the checksum formula to the following form,

Please Login or Register  to view this content.

Employing the “Index / Match” technique. The problematic bit is the "-(A28)" at the end.

Thanks for all of your help.

**NBVC** · 10-03-2007, 03:20 PM

see attached...

hope this does what you were asking for.

I just changed that last cell reference to find the row of the corresponding "title" within the column and get data from next row.

e.g. =COUNTA(E320:E327)-INDEX(A:A,MATCH("Actual Repairs",A:A,0)+1)

**Unreal** · 10-03-2007, 03:55 PM

Originally Posted by NBVC

see attached...

hope this does what you were asking for.

I just changed that last cell reference to find the row of the corresponding "title" within the column and get data from next row.

e.g. =COUNTA(E320:E327)-INDEX(A:A,MATCH("Actual Repairs",A:A,0)+1)

That is a great Idea... unfortunately it produces a circular reference when I employ it!

Working on that now!

**Unreal** · 10-03-2007, 04:06 PM

Ahhhhh!

I fixed it, naturally it was my error! Go figure!

Those are very savvy solutions, thank you so much for all of your help.

Thanks to you, I now have several new and powerful techniques to add to my list!

Thanks again.

Peace!

**NBVC** · 10-03-2007, 04:28 PM

Glad you figured it out... I just noticed that my sample didn't get attached...

here it is again...in case it is of any use to yourself or future solution seekers....