Macro help to pull company each time it's added to list?

Microsoft Office 2010 professional
February 4, 2016 at 10:18:41
Specs: Windows 64
I hope someone can help with this, it's been about a year since I've worked with Excel and I cannot wrap my head around this!

We're trying to track how long any company is in an employees queue but it's not continuous, the company can be sent to other employees for tasks so it might be gone a week before it comes back to the originating person.

    A                   B                           C                    D
10/5/16             ABC Corp                    000123456             Task A
10/5/16             XYZ Inc                     888555999             Task A
10/5/16             123 Comp                    554654545             Task A
10/6/16             XYZ Inc                     888555999             Task B
10/7/16             ABC Corp                    000123456             Task B
10/7/16             XYZ Inc                     888555999             Task B
10/8/16             123 Comp                    554654545             Task B
10/9/16             XYZ Inc                     888555999             Task D
10/10/16            ABC Corp                    000123456             Task B
10/10/16            123 Comp                    554654545             Task C
10/12/16            123 Comp                    554654545             Task D

This is what the list looks like when I get it, it's created by date so the companies can be anywhere.

I need to pull ABC Corp and see how long we had it. Task A, we received it so that's our start day. Task B, we send it out, so the end date. For ABC, that's a count of 2 days. Then there's another Task B, which means we got it back and sent it out the same day, so another 1 day so a count of 3 total.

XYZ has 1 day for Task A and B, then comes back for 1 day and goes straight to D (approval) so the count remains at 2 total.

123 has 3 days between Task A and B, then comes back to us for C where we have it for 2 days until it goes to Approval (D). Total count of 5 days.

Please tell me there is a shorter way to do this than counting! I was handed a spreadsheet with 538 rows of data (one employee out of 18!) and told this was a monthly task.


See More: Macro help to pull company each time its added to list?

Report •

February 4, 2016 at 10:21:03
Sorry, I forgot how to do the mock data.

A is the date, B is the company name, C is a 9 digit number purely associated with the company, and D is the task.

Report •

February 4, 2016 at 20:00:44
You use the "pre" tags to line up the columns when posting example data.

As far as your question, the first I need was to sort the data to group all the companies together. Once I did that, I found that I was not seeing any kind of pattern that a formula or macro could detect in order to "count" the days.

In some cases a "B" is associated with a prior "A", so a calculation needs to be done, in other cases a "B" stand alone and is counted as a single value.

If there was always an A-B or B-C etc. calculation, that would be different. It's those pesky standalone days that seem troublesome. At this point, I'm not sure how the code would be able to recognize those.

Any thoughts?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

February 5, 2016 at 08:12:26
Maybe the first step would be to change the tasks, add a couple more?

A-the day we get it
B-the day we send it back for more info (if this is the first B, it's associated with the A. If there's a previous B, it will be a count of one. Is there a way to differentiate that? Like a count for the company name and how many Bs there are? And label them B1, B2, B3, etc?)
C-finalizing after the info is all gathered. C will almost always go to D, it will rarely go to B.
D-approval, the end. It's very, very rare that anything goes back to B or C from there.

Would that work?

Report •

Related Solutions

February 5, 2016 at 09:13:58
I'll have to try and wrap my head around that and see what I can figure out.

However, this concerns me:

It's very, very rare that anything goes back to B or C from there

Generally, when it comes to VBA, which is really nothing more than a list of step by step instructions, you have to account for every possible scenario. The code can't "decide" what to do when it runs into an anomaly, it has to be told what to do. It could told anything from ignore it to present a message to the user to perform a calculation to whatever. If we don't tell it what to do, it might cause an error and crash the code. If we tell it to ignore it, the user might never know that the anomaly occurred and that the output data isn't valid.

For example, in your case if it did go back from D to B even once, wouldn't the count for that company be wrong? Would you need to at least know that so that you could manually fix it? Would it be such a nit in the larger picture that you wouldn't care?

But comes the gloom and doom: ;-)

What happens if something in your process changes over time and that "very, very rare" moves to "very rare" then to "rare" then to "every now and then" to "25% of the time". Would you be able to detect that through some other means or would the code
just blissfully ignore each and every D to B move and calculate counts that are wrong fairly often?

You see where I'm going with that?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

February 5, 2016 at 11:45:52
Hm. Well, I guess I'd need a message that would display if it ever went from D to anything else. But in that case, wouldn't that be easy to check by looking at date? Like IF D2="Task D" then nothing in column A should be > than A2?

Report •

Ask Question