return a total when meeting only one criteria.

[Thargor]

Hello forum folks. Long time reader first time poster.

I am trying to collate some data for my company and i am really struggling, so have come here in the hopes that you can help. I'm sure it can be done but our IT department are really struggling staff-wise and have deemed this not urgent for the business, but it is extremely urgent in my job role.

Here's my Dilemma
For each of our orders we have various departments that each have a set time for processing.
Dept Process days
14 1
16 4
19 2
20 2
50 1
60 1
70 1
93 1
98 2

Different orders can go through any combination of departments. So when displayed in excel would show as
Job# Dept
1 14
1 16
1 93
2 20
2 50
2 70
2 98
3 14
3 93
4 60
4 70
4 98

What i need to display are jobs that only have dept 93 in their process and also display a total time for each order. So that the returned data looks like this.
Job# Total Time
1 6
3 2

Like i say i am really struggling to do it. We use Excel 2003 and i'm guessing perhaps a macro of some sort would do the trick, but i have no idea how to do it. Can anyone help?

[Thargor]

I know i need to change the post title, and i will when i figure out how.

[jaslake]

Hi Thargor



First thing:

• Job #1 runs through Dept 93
• Dept 93 has 1 processing day
• Job #1 requires 6 processing days. And I know this because?

Second thing:

• I like to test my code; in order to do so, I need to have a workbook with your data.
• You've provided data (partial): please put the data in a workbook and I'll be glad to look at this with you.

John

[foxguy]

Hi Thargor;

The way you described it, you don't need macros. You just need VLOOKUP (maybe 2 nested VLOOKUPs), but I agree with John, how did you arrive at 6 days for job #1

[Thargor]

Hi Thargor



First thing:

• Job #1 runs through Dept 93
• Dept 93 has 1 processing day
• Job #1 requires 6 processing days. And I know this because?

Second thing:

• I like to test my code; in order to do so, I need to have a workbook with your data.
• You've provided data (partial): please put the data in a workbook and I'll be glad to look at this with you.

John

Sorry
What i meant was job #1 runs through each department listed against a #1. With each department having its own process time (which i listed but perhaps didn't explain very well as i'm newish to this) so in this instance its total time as it goes through all the departments listed is 6days.

I have attached a worksheet for you guys to play with. Sorry i'm not explaining things as thoroughly as you need, your patience is greatly appreciated

[jaslake]

Hi Thargor
This code is included in the attached workbook

Please Login or Register  to view this content.

See if it does as you require. Let me know of issues.

John

[foxguy]

Hi Thargor;

Here's a file that does what you want without macros. You can hide Column D. Column E shows the combined processing time for each Job # in Column A.

[Thargor]

John this is awesome so i'm gonna rate you highly right now lol. However i need it to only return the orders that go through dept 93.
I know in sql you can do a subquery. Is it possible to do that in VBA? something like select o4ord#, m2seq; where o4ord# IN (select o4ord#;m2seq where m2seq = 93). Obviously its probably a lot more complex but if there were a macro version of this that ran before the macro you have created would that solve it?

[Thargor]

HI Foxguy

Thats quite clever, our order numbers always display in ascending order so VLOOKUP would always work. However as i have also replied to John's answer i need to only display jobs that have dept 93 in their process. So if it can incorporate that somehow we're onto a winner.

Thanks for spending the time to help me out. it is greatly appreciated.

Hi Thargor;

Here's a file that does what you want without macros. You can hide Column D. Column E shows the combined processing time for each Job # in Column A.

[foxguy]

Hi Thargor;

I took the challenge.
Here's a file that does it with formulas. I imagine that you would hide Columns D:H in the final product.

[Thargor]

That my friend is brilliant.

I'm gonna mark as solved and of course give you the respect you deserve but i'd still be interested to see if John can come up with a Macro version.

Thanks a million for this foxguy.

Hi Thargor;

I took the challenge.
Here's a file that does it with formulas. I imagine that you would hide Columns D:H in the final product.

[jaslake]

Hi Thargor

If you're still looking for a macro solution, I'll look at this tomorrow. Let me know.

John

[Thargor]

Thanks John.

I am still interested in a macro solution as it would speed things up a tad as the volume of orders i'm dealing with varies a lot and so i think it would be easier, but if its not possible or will take too long don't worry about it as foxguy's works well.

Hi Thargor

If you're still looking for a macro solution, I'll look at this tomorrow. Let me know.

John

[foxguy]

Hi Thargor;

I am still interested in a macro solution as it would speed things up a tad

Macros are not as fast as Excel's native formulas. If you need a macro to put my macros into your workbook, then I would say let the macro also do your calculations. But if it's just a matter of copying my macros down the columns and you do that manually, then the formulas will be faster.

[jaslake]

Hi Thargor

One of the things the Moderators really frown on is quoting whole posts in your response. Just a "heads up"

John

[shg]

Jaslake's truthin', Thargor.

[Thargor]

My opinion on how fast it is is not from the calculations but from the data returned. I explained in the original post how i needed the data to be returned. i.e Cutting out all other information, only showing only a job number and total, not all the rows inbetween. I realise i could, (and have been) filter my final row for nonblanks, then hide columns, then copy and paste the resulting data into a different sheet, but thats not as quick as the macro john originally made does all that.

But if it's just a matter of copying my macros down the columns and you do that manually, then the formulas will be faster.

And John thanks for the heads up on the etiquette of quoting. I read the rules and i don't remember it being mentioned. Harsh to think i could get a smacked bum for something like that.

[shg]

Here's another way:

Please Login or Register  to view this content.

The reason that the On Error Resume Next is in there is because there's no process time for DESPATCH (99) in the table.

[jaslake]

Hi Thargor

The code in the attached workbook has been modified to do this

i need to only display jobs that have dept 93 in their process

Let me know of issues.

John

PS: quoting entire posts has been described as "clutter"

[Thargor]

Thanks guys for all your hard work. Both of these work well. Guess this is Officially SOLVED