Solved Concatanate results into a single cell

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?

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.

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.

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?

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 cases that went over two 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.

Unless I am mistaken, we have to 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:

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.

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.

(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?

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.

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.

The information on Computing.Net is the opinions of its users. Such
opinions may not be accurate and they are to be used at your own risk.
Computing.Net cannot verify the validity of the statements made on this
site. Computing.Net and Compnet Ventures, LLC hereby disclaim all responsibility
and liability for the content of Computing.Net and its accuracy.