Return text from a mixed text/value 3d array result

[rmiller]

Hi All,
I've been searching the forums and web trying to figure out how to extract a text value from a 3D array result, to no avail or well beyond my understanding.
I've attached a file which shows an abbreviated version of the search (from the original 49 col x 400 row, which shows the name to date array which can return a sum.
What I'm looking to do is identify within this array result if there has been a text entry, and return the text as opposed to a sum of the remaining values. This is essentially a double check to make certain that there aren't hours scheduled when other events such as vac/loa/etc are also booked.

Thanks in advance.
Ray

[DonkeyOte]

Ray, I confess I'm not entirely sure I can fathom from your file what exactly you want to return nor where you want to return it but in essence...

Find Text if it Exists for Given Name & Date:

Please Login or Register  to view this content.

On an aside, to SUM the hours you don't need Arrays or SUMPRODUCTs you can in fact use a basic and very efficient SUMIF by using INDEX to establish the summation range:

Please Login or Register  to view this content.

The first formula will return an error should no text exist... you could thus compare the two formulae to validate - ie if no error in first formula (ie text returned) hours should be 0 etc...

[rmiller]

DonK, Thanks for the reply, and sorry for the confusing attachment ... I should have deleted my mess of formulas.
My goal was to have a formula which would be able to query an array (name/date) and: (1) return a sum of hours or (2) if there is text in the array result to return the text. I've cleaned up the attachment, so hopefully this makes more sense.
Thanks for the suggested SUMIF/INDEX/MATCH, it worked well as you can see in the attached, however the lookup returns n/a (which I believe is what was expected).

R

[DonkeyOte]

Sorry there was a typo in my original (since corrected prior post).

You could theoretically use Conditional Formatting to flag the time where Text is present however it seems you intend to run this final summary on a separate sheet and if so you will have to make use of named ranges so as to be able to reference the sheet1 table - this will make the formula even more convoluted.

How big is the matrix you're looking to generate ?

FWIW I've attached your file with the CF approach in place - text mismatch is in red and hour excess in different colour...

In reality however I suspect you might want to consider a VBA based approach.. it's also not clear to me what you want to return in your final matrix if there are both hours & text present ... in the attached for simplicity only hours are returned.

[rmiller]

> Yes, the summary is on a separate sheet.

> The sheet1 matrix will be approx 320 rows x 50 columns, whereas the Sheet2 summary matrix will be 110 rows x 50 columns.

>Ideally, the single cell result would be either the text (vac/test etc.) or the hours (if no text present). however the CF solution does provide a workable solution.

Thx

[DonkeyOte]

I will look at this tomorrow if not resolved by others in the meantime but in short I remain a tad confused and primarily because of:

What I'm looking to do is identify within this array result if there has been a text entry, and return the text as opposed to a sum of the remaining values.

which makes sense perfect sense, however, the above is then followed by:

This is essentially a double check to make certain that there aren't hours scheduled when other events such as vac/loa/etc are also booked.

And this was what I was getting regards my question as to what should be returned where both numbers and text appear for a given combination ?
To me it would seem as though these combinations are the "show stoppers" requiring attention.

Merely returning the text that appears (as and when) does not to my mind highlight error per se - after all if no hours are assigned then it matters not a jot, correct ?

And on that basis I would deem those instances where both hours and text have been assigned simultaneously that are of primary concern, no ?
And if so how do you wish to differentiate those instances from the remainder (be they just text or just number) ?

(I would say that 5000+ conditional format formulae is not going to be something you'll want to pursue given they are "super-volatile" [to quote Charles Williams])

[rmiller]

You are correct that the goal is to flag those instances where there is both text and numbers in the same range, so thanks for pointing out the flaw in my logic.

The CF solution you provided does the job perfectly, but by your comments am I interpreting correctly that based on the matrix size the recalculations will be a huge drain on the system. Curious how a VBA solution reduces system load.

Thanks again for bearing with my newbie level

[DonkeyOte]

The CF solution you provided does the job perfectly, but by your comments am I interpreting correctly that based on the matrix size the recalculations will be a huge drain on the system.

Yes exactly... Conditional Formatting can be a drain esp. when the formulae used are expensive as they would be here.
The additional requirement of Named Ranges adds another layer of fiddling which in short I would say will make things all a bit too cumbersome to be a truly practical approach IMO.

Curious how a VBA solution reduces system load.

Well it could be that you use VBA to generate the matrix in it's entirety but "on-demand" rather than continuously (reducing calc overheads)

Would I be correct in assuming the compression from x rows to y rows on summary is down purely to name aggregation and nothing more ?
If so it's possible you could invoke the VBA to update the summary report partially (ie relevant name(s)) as and when master sheet is altered rather than relying on manually invoked updates.

If you could post a decent sized sample file I will gladly put something together for you tomorrow.
The other advantage of VBA is that it will be easier to manipulate in terms of how you wish to populate the cells - ie with combination of text:number in the same cell etc... or just text / number as appropriate.

[rmiller]

Thanks Don, your expertise is much appreciated.

Attached is the full size matrix, with the separate sheets intact.

The end user gave me the "Data" sheet format, which is where the daily hours will be input. Despite the redundancy of employee name in column A it gives the scheduler a visual feel for the day/week. The challenge for them is that it is relatively easy to make duplicate entries so we added the "Employee daily summary" with the intent to provide a cross check for individual daily hours as well as for the check on text entries (which you've solved for us, Thx). Not very elegant but ....

[DonkeyOte]

I've had a look through this and though the attached is still not great it should be a little less hungry in terms of performance.

In essence I've done the following:

WORKBOOK

Added new Named Range: Schools

DATA Sheet

I've removed all conditional formatting from the sheet with the exception of your Stat formatting as this would be a bit cumbersome to handle - and replaced with format via VBA change event.

The code itself can be viewed by right clicking on the DATA sheet tab and selecting View Code - in essence whenever a cell within the data_range matrix is altered the code will fire and will format both itself and the appropriate cell on the EE sheet as appropriate.

I corrected one of the names on the Data sheet as this did not tie out to the School Summary sheet (have highlighted in yellow)

I added Data Validation to each cell in the Data_Range matrix such that only loa/vac/number/blank could be entered

EMPLOYEE DAILY SUMMARY Sheet

Again I've removed all conditional formatting - replaced via the same Change event as above.

I added additional formulae in row 4 so as to remove need for repetitive calculations in each cell in the results matrix
(one of the "golden rules" so to speak)

SCHOOL SUMMARY Sheet

Though this was not complete I figured I'd chuck in my two pennies...

Formulae for Actual Hours simplified to a SUMIF (was SUMPRODUCT Array)

Added calcs in rows 1 & 2 and cols P & Q to again avoid need for repetitive calcs when populating the results matrix

Added calcs to generate results matrix - by utilising the above you can use a simple and efficient SUM function in conjunction with 2 INDEX calls to establish the range.


I hope that helps to some extent.

[rmiller]

Thanks Don, this works great, and I'm looking forward to dissecting your work to try and learn more about VBA and the other functions that I hadn't known much about until now.
Appreciate you sharing your expertise so readily.
Regards
R