I have an excel sheet, with a main ab that is formatted as a cross-table.
So there are rows, labeled by project,
and columns labeled by business unit
(gladly this is only 5 high-level BU's)
But the projects are plenty.
The "cells" in that crosstab contain ...
lists of names of people:
which BU sends which people to decide on changes in which project.
We use that table, to attribute access to the projects' tools,
which is done in eDirectory (user groups within the right OU, then mapped to the actual access systems)
When we want to "verify" the attributed access,
each LDAP group has an alphabetically sorted list of user "logins" (normally 6 letters),
but of course the excel tab has "real names".
In excel, I found a plugin (tableau) to un-pivot that table to a denormalized list, with rows containing
Project - BU - Listofnames
As the listofnames still cannot be sorted, I wanted to turn this into:
Project - BU - SingleNames
With SingleNames (and a separate excel sheet translating real name to user login, and vlookup, of course)
I then would be able to "sort" on the logins within project, within BU, or both.
That way checking whether the user group in eDirectory has the same content as the official user list has, would be much easier.
First time around, doing transformations by hand
(I'm a terrible excel programmer)
I really lost much time reworking the sheet.
And when the list is updated, the business people sometimes "forget" to tell "who or what has changed",
but there is no auto-compare tool to highlight the changes "within a cell"
Is there a way, in excel, to turn the
Project - BU - Listofnames
into a
Project - BU - SingleNames
thus creating an extra row for each name (separated by comma's) in the first representation ?
I've been searching, in excel help (excel 2007), in microsoft online help, on internet ... my "google fu" has failed me on that subject.
It would be OK to solve the problem in LibreOffice, but there I didn't find a "string splitter on comma" either. |
