Microsoft Office 2010 professional

I hope I used the right terminology for this, I don't think I've ever done anything like this before! I have a workbook that gives keeps track of all of our cases: case number, case name, when it came into our queue, who picked them up, and when it's been resolved. There's also a cell that tells how old the case is based on the entry data and the resolved date. If the aged date is more than two, I have it moved to another workbook.

The second wb is what I need help with. The first column is our employee name, the second is how many cases each employee has that's over 2 days. ( I'm not sure how I'll do that yet, maybe a countif, maybe a vlookup, but I'm okay with this and am sure I can figure this out.)

The third column is the one I need help with. The manager wants all of the account numbers with aged dates for research and there seems like too many variables to search for, I can't wrap my head around it. First, the employee name in workbook2 has to match the employee name on workbook1 but only if the aged date is >2. If yes, then carry the Customer Number, (column E in workbook1) into column C in workbook2. Because we want all of them, it would have to be a range of columns and not named cells so I thought Ecel had a function where you could separate results with a comma.

For instance if A2 is for employee Ann Brown, cell B2 shows 3 as her total, then C2 would show 123456, 345678, 234567.

Is there a function? And what would my formula look like? Would a macro be a better idea?

✔ Best Answer

First, the questions: 1) It doesn't really matter how the number of Age >2 Cases is retrieved, I was just curious. As explained below, I used COUNTIFS

2)"maximum number of 'Aged >2 Cases'.....how many numbers could we pull in successfully".Technically, I think we could pull in as many numbers as there are columns in an Excel spreadsheet, at least up until we hit the maximum number of characters allowed in a cell. In others words, the limit is not based on the method I am going to suggest, it's based how how much data Excel can put in a cell. A dozen won't faze Excel in the least. In my example, I am only going to set it up for 5.

Please note that I don't know your workbook or sheet names, so the tests I did were all done in one worksheet. I see no reason why it won't work across workbooks/worksheets, but you'll have to let me know if it doesn't.

All of my formulas will refer to cells on the same sheet...you'll have to modify them to refer to other workbooks/worksheets. I am going to use

Helper Columns. Helper Columns are columns where we put formulas that will return "intermediate results" which we can then use for our final solution. I am going to place the Helper Columns right next to the data just because it is easier to do/explain. In reality, they can go anywhere you want, even on a different sheet if you don't want to see them. You just have to make sure you have all of your cell references correct so that the correct data is pulled from the correct column. That's why keeping them close to the original data is less confusing.OK, here we go. Hang onto your hat, this could get bumpy. I strongly suggest that you use my exact example in a blank spreadsheet until you understand what I am suggesting.

We're going to start with this:

Representing Workbook 1:

A J Q 1 CIN Name Aged Days 2 123456 Ann Brown 2 3 564563 Bill Smith 4 4 898765 Ann Brown 3 5 453567 Sue Martin 4 6 321234 Ann Brown 3 7 456765 Ann Brown 4 8 987654 Sue Martin 3 9 222333 Sue Martin 1Representing Workbook 2:

A B 11 Name Aged >2 Cases 12 Ann Brown 3 13 Bill Smith 1 14 Sue Martin 2I used COUNTIFS to pulled the Aged >2 Cases:

In B12 enter this formula and drag it down to B14 to get the table I show above

=COUNTIFS($J$2:$J$9,A12,$Q$2:$Q$9,">2")

First Helper Column:In R2 of Worksheet 1, enter this formula and drag it down to R9:

R2: =IF(Q2>2,J2&COUNTIFS($J$2:J2,J2,$Q$2:Q2,">2"),"")

This formula will create a list of names with a number appended to it. The number will increment each time the corresponding name from Column J has an Age Days >2.

For the example shown above, you should see this:

R 1 Unique Values 2 3 Bill Smith1 4 Ann Brown1 5 Sue Martin1 6 Ann Brown2 7 Ann Brown3 8 Sue Martin2 9OK, now we have unique items to "look up", one unique item for each CIN that is aged >2 days

The Rest Of The Helper Columns:In D12, enter this formula:

=INDEX($A$2:$Q$9,MATCH($A12&COLUMN()-3,$R$2:$R$9,0),1)

You should see 898765.

Drag this formula over and down to H14. Ignore the #N/A errors.

Each row, 12 - 14 should contain the Aged >2 CIN's for the person listed in A12:A14.

(I am only using 5 columns as my maximum allowed CIN's Aged >2. You will want to drag this over as many columns (12? 20? 30?) as you think you will ever need.

If you don't understand how the INDEX(MATCH...) formula works, just ask. I'm not going to explain it unless you need me to.

OK, final step...In C12 enter this formula and drag it down to C14:

=IF(B12>0,D12&IF(ISNUMBER(E12),", "&E12,"")&IF(ISNUMBER(F12),", "&F12,"")&IF(ISNUMBER(G12),", "&G12,"")&IF(ISNUMBER(H12),", "&H12,""),"")

What this returns is what you originally asked for:

It concatenates the values in Columns D:H which are the CIN's Aged >2 retrieved by the INDEX(MATCH...)) formula. However, it only concatenates the cells that contain numbers and ignores the #N/A errors.

Note: Once again, I am only checking 5 columns, D:H. If you are going to have to check 12 or more columns , e.g. D:R, then you are going to have to extend that formula by including a

&IF(ISNUMBER(x12),", "&x12,"")term for every column, D:R. Cut and paste is your friend.If you think it will help, I can send you a copy of the worksheet I created. Send me an email address via PM and I'll send it to you.

Good Luck!

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

message edited by DerbyDad03

I'm a little confused about the layout of the workbook that keeps track of all the cases. When you say that the results for Ann Brown should show three Customer Numbers, does that mean that Ann Brown is listed at least 3 times in the first workbook. Are you asking to search through multiple instances of Ann Brown on only pull info from some of them into the second workbook?

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

Don't worry about the first workbook too much, it's very large and has tons of fields that I won't be using at all. Each row is set up to hold a case or an instance that a customer is being reviewed. Multiple fields, data that would be used by other areas, but not much for this purpose. The only important fields are the customer/case number (col A), the employee name (col J), and the aged date (col Q - which is how many days the case has been active in our area.) The aged date will always be a number. The employee name is chosen from a drop down. On the second workwork, when I say Ann Brown is listed three times, it means she worked on three

casesthat wentovertwo days. If it was under 2 days, I don't want to pull it so there will be two criteria for my vlookup.Workbook 2 layout: Employee name is col A, number of over 2 day aged cases is col B, col C is the customer number separated with a comma.

Now, if the employee is Ann Brown (a2) and there are three cases with aged date is greater than two (b2), I want to place the customer's number in c2 with a comma between the numbers and there will be three of them per the three in b2 so 123456, 345678, 234567.

Does this make more sense?

re: " Don't worry about the first workbook too much"Unless I am mistaken, we

haveto worry about the first workbook because that is where the data being searched resides and that is where the data must be pulled from.It would help if you posted some examples of the data in each workbook so we have something to relate your text to. Keep in mind that we can't see your workbooks from where we're sitting. Please take this comment in the spirit in which it is intended. You may feel that you are describing exactly what you have in a clear and concise manner, but it may still be confusing for those of us out here on the interweb because we have no visual to work with. We could end up wasting time working on a solution that you can't use simply because we misinterpreted your description. I'm not placing any blame on anyone, it's just that I've seen it happen so many, many times.

That said, I am assuming you have something like this; please correct me if I am wrong:

Workbook 1:

A J Q 1 Case Number Name Aged Days 2 123456 Ann Brown 1 3 564563 Bill Smith 3 4 898765 Ann Brown 2 5 453567 Sue Martin 2 6 321234 Ann Brown 3 7 456765 Ann Brown 4 8 987654 Sue Martin 3 9 222333 Sue Martin 1Workbook 2:

A B 1 Name Aged >2 Cases 2 Ann Brown 3 3 Bill Smith 1 4 Sue Martin 2

What you want is:

A B C 1 Name Aged >2 Cases Aged Case Numbers 2 Ann Brown 3 898765, 321234, 456765 3 Bill Smith 1 564563 4 Sue Martin 2 453567, 987654EDIT:

See my next post.

~~Note: If that is correct, my initial thought is that you are going to need a macro to accomplish your goal. I may be wrong, but if don't want a macro, I'd like to know that upfront so I don't waste time writing one and I put my efforts into trying to see if a formula based solution is possible.~~

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

message edited by DerbyDad03

BTW...two questions: 1 - How are you getting the "Aged >2 Cases" number in Column B of workbook? Are you using a COUNTIFS function?

2 - Will there ever be a maximum number of "Aged >2 Cases" any one employee might have at any given time? It doesn't really matter, but if my Response # 3 description of your workbooks is correct, I may have a formula based solution. The issue is writing the formula so that it knows when to stop pulling in "aged case numbers" when it has reached the correct number. We can write the formula to be as long as it needs to be, it would just be nice to know the maximum number of aged cases so we don't use more terms than will ever be needed.

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

message edited by DerbyDad03

(Sorry! I forgot about this until I started to work on it again today!) Okay, to all three of your examples (Workbook 1 and 2 and 'What You Want'), that is exactly how the data is formatted but column C would be called Customer Number or CIN.

And to answer your questions....

1) I haven't worked on this much and am still doing this part. I thought a vLookUp since I need to do the employee name along with the >2day count. Or a countif. I'll put it together and get back to you on Monday. Your example above of Workbook 1 is dead on.

2) As to if they'll be a maximum number of 'Aged >2 Cases'....well no, I guess there won't be. I never considered that before but since each employee will do a couple hundred cases a month, a dozen cases might be feasible. Cross fingers there's no more than that, but how many numbers could we pull in successfully?

First, the questions: 1) It doesn't really matter how the number of Age >2 Cases is retrieved, I was just curious. As explained below, I used COUNTIFS

2)"maximum number of 'Aged >2 Cases'.....how many numbers could we pull in successfully".Technically, I think we could pull in as many numbers as there are columns in an Excel spreadsheet, at least up until we hit the maximum number of characters allowed in a cell. In others words, the limit is not based on the method I am going to suggest, it's based how how much data Excel can put in a cell. A dozen won't faze Excel in the least. In my example, I am only going to set it up for 5.

Please note that I don't know your workbook or sheet names, so the tests I did were all done in one worksheet. I see no reason why it won't work across workbooks/worksheets, but you'll have to let me know if it doesn't.

All of my formulas will refer to cells on the same sheet...you'll have to modify them to refer to other workbooks/worksheets. I am going to use

Helper Columns. Helper Columns are columns where we put formulas that will return "intermediate results" which we can then use for our final solution. I am going to place the Helper Columns right next to the data just because it is easier to do/explain. In reality, they can go anywhere you want, even on a different sheet if you don't want to see them. You just have to make sure you have all of your cell references correct so that the correct data is pulled from the correct column. That's why keeping them close to the original data is less confusing.OK, here we go. Hang onto your hat, this could get bumpy. I strongly suggest that you use my exact example in a blank spreadsheet until you understand what I am suggesting.

We're going to start with this:

Representing Workbook 1:

A J Q 1 CIN Name Aged Days 2 123456 Ann Brown 2 3 564563 Bill Smith 4 4 898765 Ann Brown 3 5 453567 Sue Martin 4 6 321234 Ann Brown 3 7 456765 Ann Brown 4 8 987654 Sue Martin 3 9 222333 Sue Martin 1Representing Workbook 2:

A B 11 Name Aged >2 Cases 12 Ann Brown 3 13 Bill Smith 1 14 Sue Martin 2I used COUNTIFS to pulled the Aged >2 Cases:

In B12 enter this formula and drag it down to B14 to get the table I show above

=COUNTIFS($J$2:$J$9,A12,$Q$2:$Q$9,">2")

First Helper Column:In R2 of Worksheet 1, enter this formula and drag it down to R9:

R2: =IF(Q2>2,J2&COUNTIFS($J$2:J2,J2,$Q$2:Q2,">2"),"")

This formula will create a list of names with a number appended to it. The number will increment each time the corresponding name from Column J has an Age Days >2.

For the example shown above, you should see this:

R 1 Unique Values 2 3 Bill Smith1 4 Ann Brown1 5 Sue Martin1 6 Ann Brown2 7 Ann Brown3 8 Sue Martin2 9OK, now we have unique items to "look up", one unique item for each CIN that is aged >2 days

The Rest Of The Helper Columns:In D12, enter this formula:

=INDEX($A$2:$Q$9,MATCH($A12&COLUMN()-3,$R$2:$R$9,0),1)

You should see 898765.

Drag this formula over and down to H14. Ignore the #N/A errors.

Each row, 12 - 14 should contain the Aged >2 CIN's for the person listed in A12:A14.

(I am only using 5 columns as my maximum allowed CIN's Aged >2. You will want to drag this over as many columns (12? 20? 30?) as you think you will ever need.

If you don't understand how the INDEX(MATCH...) formula works, just ask. I'm not going to explain it unless you need me to.

OK, final step...In C12 enter this formula and drag it down to C14:

=IF(B12>0,D12&IF(ISNUMBER(E12),", "&E12,"")&IF(ISNUMBER(F12),", "&F12,"")&IF(ISNUMBER(G12),", "&G12,"")&IF(ISNUMBER(H12),", "&H12,""),"")

What this returns is what you originally asked for:

It concatenates the values in Columns D:H which are the CIN's Aged >2 retrieved by the INDEX(MATCH...)) formula. However, it only concatenates the cells that contain numbers and ignores the #N/A errors.

Note: Once again, I am only checking 5 columns, D:H. If you are going to have to check 12 or more columns , e.g. D:R, then you are going to have to extend that formula by including a

&IF(ISNUMBER(x12),", "&x12,"")term for every column, D:R. Cut and paste is your friend.If you think it will help, I can send you a copy of the worksheet I created. Send me an email address via PM and I'll send it to you.

Good Luck!

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

message edited by DerbyDad03

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History