Forum Discussion
Text aggregating in a single cell
Hello,
I realize this is a big question and I'm trying to do several things at once so any help/ ideas/ recommendations are welcome. Anyone who wants to swing big and solve the whole thing for me is welcome too though.
Background: I'm running excel 2013. I'm a recruiter (only relevant to the data). I'm trying to return a series of text strings in a single cell that are dependent on multiple corresponding criteria.
In column B I have applicant names.
In column E I have requisition ID's.
In column O I have the date the applicant comes into consideration as a potential hire.
In column P I have dates a candidate was rejected for a requisition
In column Q I have the outcome of verbal offers to applicants.
What I need (preferably on a separate workbook):
One column showing the unique requisitions id's.
One column listing the names of all applicants that are associated with a requisition, that have a consideration date in the past, that have nothing in the P column for rejection, and that have not previously rejected an offer. This would potentially include no names, one name, or multiple names.
One column containing just the name of applicants who have accepted the verbal offer for the corresponding requisition id.
What I have so far:
I think the first column will need to be an aggregate function but I'm at least one or two steps away from really understanding aggregate.
The second column is the main reason I'm here. Most of my ideas for it are half-baked but involve working data through multiple sheets using indexing and matching arrays with the middle step being a big question mark. I think I could make it work, but I think there must be a better way.
For the third column, an index match array seems to be the best answer. Any simpler or faster suggestions are welcome though.
6 Replies
Brent_OKeefe Another option for you with Power Query, an add-in that you can install in Excel 2013.
See attached.
- Thank you so much for the recommendation. I've never tried the Power Query add-in but I have a coworker who is trying to learn it. I will chat with them and see if we can help eachother.
Brent_OKeefe Maybe there is a reason you must have it this way but I just thought I would suggest using a pivot table instead. In a few simple steps I created a pivot table, then created 2 conditional formatting formulas so highlight "Rejected" and "Accepted" names and hid the middle columns:
They are not concatenated into single cells but grouped and give as much or more information.
- Thank you so much for the recommendation. I haven't had much use for pivot tables in the past (crazy, I know) but your example makes me want to consider them. The reason I was looking for all the names to be consolidated into a single cell though was to be able to send out a request for updates from multiple managers using an email campaign through word and outlook.
It would be simpler if you had Microsoft 365 or Office 2021.
Here is a solution that'll work in all versions using a custom VBA function; the workbook is now a macro-enabled workbook (.xlsm).
I couldn't agree more. I would love to have access to 365 or 2021, but I'm the only excel nerd on my team at work and nobody else cares about an upgrade.
I haven't had any experience with Macros or VBA-enabled workbooks in the past but after 15 minutes of playing with what you sent back, I now know it's the next thing I need to learn. It seems to be working like a charm for exactly what I need it to do and will allow me to disseminate information much more easily amongst the people I work with.
Thank you so very much for your help. HansVogelaar
