|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 1
Representing Workbook 2:
11 Name Aged >2 Cases
12 Ann Brown 3
13 Bill Smith 1
14 Sue Martin 2
I 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
First Helper Column:
In R2 of Worksheet 1, enter this formula and drag it down to R9:
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:
1 Unique Values
3 Bill Smith1
4 Ann Brown1
5 Sue Martin1
6 Ann Brown2
7 Ann Brown3
8 Sue Martin2
OK, 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:
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.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
message edited by DerbyDad03