Index/Match, Multiple Critiera with MIN (or SMALL)

[lookingforhelp1]

Hi there

I'm trying to streamline some of my workload. I often use route planning software and can generate an export with a breakdown of the entire plan (but can not break it down any further unfortunately).

As I need to show a break down per customer group (It's never more than 3 different customer groups - but each customer could have 5 call points for example), I've created a custom summary sheet, where I paste the exported CSV into the background so I can perform all reavent calculations.

In my summary sheet, I have overall totals, customer A totals, Customer B totals etc.

All is working well, until I get to the time that the customer is started.

As it stands, for customer A, I've used the following array formula:

Please Login or Register  to view this content.

This works great...until I get to a couple of routes where the sequential order at this customers first call point is any other sequence than 1 (i.e. it could be 5).

I imagine that in the last criteria of the index/match, I need to somehow incorporate a MIN or SMALL function to get the correct sequence number that this starts at but I'm really struggling.

All the criteria's I need to match up are in the data (Route Number, Customer Group the call belongs to and Sequence)

Is any one able to offer any help, please? I'm really scratching my head over this one!

[Glenn Kennedy]

Can you post a sample of your sheet, so that we can see what you mean. Incidentally, using whole column ranges in the MATCH part of that formula will slow things dow n a lot. Can't you use a couple of Named Ranges or sensible end points to speed things up?

[Tnashnhsp1]

It's difficult to help without seeing your work but in case this helps, you can use MIN and IF as an array, e.g. below to see if value in A1 matches any value in array (A2:A5) and where it does find the MIN in the adjacent cells

Please Login or Register  to view this content.

as an array

so if A1 is found in A3, it will find the smallest number between B3 and F3

You can of course use AND within that IF to add multiple conditions

[lookingforhelp1]

Can you post a sample of your sheet, so that we can see what you mean. Incidentally, using whole column ranges in the MATCH part of that formula will slow things dow n a lot. Can't you use a couple of Named Ranges or sensible end points to speed things up?

Sure thing, I've attached it to my original post for you.

It's just an example of one route (with all confidential data ommitted, and I've also removed some data that was irrelavent to my question).

Just to explain with the sample, if I wanted to show the start time where we started Customer Group B, this would start at sequence 5, so in the summary I've made, I'd expect to see Customer Group 2 starts at 09:41am.

(Ignore the "end time" and the "hours" time for now, these are just there for my next task of calculating the end times)

[newdoverman]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[lookingforhelp1]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

It's already attached but your reply must have crossed over with my attachment.

[FlameRetired]

Try this minor adjustment to the formula in K3 of Summary.

Please Login or Register  to view this content.

Then apply that change i.e. [(1='Core Data'!$B$2:$B$30) -- > ('Core Data'!$B$2:$B$30>=1)] to G3. It returns 09:41 in your example and also continues to return 6:00 in G3. How does it do with the rest of your live data?

Edit Afterthoughts -- upon further review this formula does not take other issues into consideration. Sorry. Please see my next post.

[FlameRetired]

Answered my own question. That formula above will continue to return 9:41 when copied down. If I understand the rest of the mission, this will return the sequential times from Core Data and limit the number of returns to Total Call points. Array-entered in K3

Please Login or Register  to view this content.

[newdoverman]

Perhaps a Pivot Table summary might suffice