asked on
Denormalize Excel Data
I don't know that "denormalize" is technically the correct term, so to you database guys, I apologize up front, but it's the best I could come up with to describe my need.
I am being given the output of a relational database in Excel format, where there is a one-to-many relationship between "person name" and "position code". For each person, one row is output for each position held. The "key" is the name, which is the "one" side of the relationship.
I'm not bad with Excel, but this is beyond my pay grade: I want to have, for each name, a cell that has the concatenated set of positions to which that person is assigned. An example:
Input
I am being given the output of a relational database in Excel format, where there is a one-to-many relationship between "person name" and "position code". For each person, one row is output for each position held. The "key" is the name, which is the "one" side of the relationship.
I'm not bad with Excel, but this is beyond my pay grade: I want to have, for each name, a cell that has the concatenated set of positions to which that person is assigned. An example:
Input
Name | Position
John | AA
John | BB
Tom | AA
Harry | BB
Harry | CC
Harry | DD
Name | Positions
John | AA, BB
Tom | AA
Harry | BB, CC, DD
Microsoft Excel
Last Comment
redmondb
ASKER
Yeah, I ran down the Pivot Table rabbit hole myself; I've seen some very fancy things done with VLOOKUP and am hoping someone clever can put something together, but if VBA is the only solution I'm going to have to go another route :-(
Thanks for the suggestion, though.
Thanks for the suggestion, though.
Hi, propertytax.
A few questions, please...
(1) In your source example, all of the entries for an individual are grouped together. Is this true for the actual data?
(2) May we add a helper column (or two!) beside the source data?
(3) May we assume a maximum no. of entries?
(4) How does the source data get from the database to the spreadsheet which will contain the results?
Thanks,
Brian.
A few questions, please...
(1) In your source example, all of the entries for an individual are grouped together. Is this true for the actual data?
(2) May we add a helper column (or two!) beside the source data?
(3) May we assume a maximum no. of entries?
(4) How does the source data get from the database to the spreadsheet which will contain the results?
Thanks,
Brian.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
1) The entries for each name are grouped by name; further, they are sorted by name ascending, which is the primary (and only) sort key
2) Yes, we're free to add any helper columns we would care to, the data is "branched" from the database and we can manipulate at will
3) We may assume a maximum of 4 positions per name
4) The data is delivered to me as an Excel spreadsheet; I have no direct access to the database itself.
I've been playing with ARRAY formulas but I am just so terrible at those, I haven't a clue where to start.
2) Yes, we're free to add any helper columns we would care to, the data is "branched" from the database and we can manipulate at will
3) We may assume a maximum of 4 positions per name
4) The data is delivered to me as an Excel spreadsheet; I have no direct access to the database itself.
I've been playing with ARRAY formulas but I am just so terrible at those, I haven't a clue where to start.
SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a questionASKER CERTIFIED SOLUTION
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Sorry, both - came down with something or other, keeping me on my back for now. I'll get back to this shortly, though. I appreciate the responses.
Thanks for the update, propertytax. All the best for a speedy recovery,
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Hello, thanks for closing the question, but can you please explain the B grade? If you don't assign an A grade, you should explain what aspect of the answers you are not happy with, so experts can improve their suggestions.
If you are not happy with the way that Excel works, then that does not mean the question gets a B, though. Experts are not responsible for the way Excel works.
If you are not happy with the way that Excel works, then that does not mean the question gets a B, though. Experts are not responsible for the way Excel works.
ASKER
I've requested that the moderators change the grade on this question.
Thanks, all.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Excel does not provide that functionality out of the box. You would need a custom formula written with VBA.
The closest you can get with out of the box functionality is to build a pivot table as the attached.
cheers, teylyn
Pivot.xlsx