1
I have a database of 1800 employees (1 employee per row) and a binary confirmation of whether they have 1 or more of the 105 professional certifications we track (1 certification per column).
I need to consolidate this information down to |Employee A| |Certification 1 (i.e. PMP)| |Certification 2| |Certification 3| |Certification 4| |Certification 5|.
Is this possible without the use of a macro? If so, how?
user293832
Posted 2014-01-28T13:56:05.723
Reputation: 11
0
Okay. I would suggest using the text to data. EG. If you have the text Jim Bob|A+|CCNA|MOS Access|CAP|CISSP you can use text to columns with a delimiter of | to separate it out.
The next challenge becomes joining all of their certs together in one text field. To do this I am actually going to use a quick throwaway excel sheet just to make the formula for the joining. So, assuming your data is on Sheet1.
I make a set of cells that contain
'=CONCATENATE(,=IF(Sheet1!C2=1,Sheet1!C$1 & ", ",""),=IF(Sheet1!D2=1,Sheet1!D$1 & ", ",""),=IF(Sheet1!E2=1,Sheet1!E$1 & ", ",""),')
Be sure to notice the ' before the incomplete concatenate command marking it as text. Because of careful marking as static where appropriate (using $ to prevent that portion of the cell address from changing) You can copy one of those IF cells all the way accross to cover what would be enough for all of your employees.
This gives you something like.
I know what you're saying. "What is all this random junk! This isn't helping." The next trick comes in your favorite text editor with find and replace.
So, head up to the formulas tab in Excel and click show formulas. Wow! Lots of stuff. Select all of your filled cells (Click in top left, Ctrl-Shift-Right works well) and copy. Open your favorite text editor, mine is Notepad++.
Paste. Tons of tab separated formulaic goodness. I'm going to do a few replacements using Find/Replace. Ctrl-H in my environment
CONCATENATE(<Tab> and replace with ( to remove the first set of Tabs. ,<Tab>) and replace with ) to remove the trailing comma and last tab.<tab> and replace with | this will make all tabs commas. =IF and replace with IF. This should leave you with something like `=CONCATENATE(IF1,IF2,IF3,IF4....)Copy the text from the = on to the end of the line. Head back to excel (I like a different sheet, but do what you like) click into a cell, then I recommend clicking into the Address bar 
and THEN pasting your formula. This way you dont accidentally paste as text. That should give you a lovely list of all the Certs completed in one cell. You can copy that cell down through the rest of the employees.
You can then separate that out like I suggested above. Waay Easier right? It's a lot of setup, but once you're used to this type of maneuver, it can really help you make some complicated formulas. Like a formula that concatenates 105 certification names If they are there into one cell. If you have any more specific questions just comment back
PsychoData
Posted 2014-01-28T13:56:05.723
Reputation: 1 331
I know it's not the easiest solution, but anytime you need to update the Practitioner list you dont need to update the formula(Unless you add a certification), just split out a new copy of the data created by the formula. – PsychoData – 2014-01-28T16:25:22.983
Glad to hear it! If you like the solution please be sure to mark the answer as accepted and upvote! – PsychoData – 2014-01-28T16:59:51.623
So, just to understand you have something like this but with more certifications and more employees?
– PsychoData – 2014-01-28T14:21:00.207Exactly the same but with blanks instead of zeros when employees do not have certifications – user293832 – 2014-01-28T14:38:32.203
which can obviously be changed quite easily... – user293832 – 2014-01-28T15:49:42.213
No worries. Blank is fine :) I have responded with a way to create the complicated concatenation formula that joins all certs in the emps row together in one cell and then a link that shows how to split it out into multiple cells if you want to do that. – PsychoData – 2014-01-28T15:57:27.307