We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
3 days, 7 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Denormalize Excel Data
Posted on 2012-12-30
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
4-
4
3
12 Comments
Active today
Hello,
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
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
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.
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.
Active today
Hello,
please see attached a suggestion with helper columns that can be hidden.
Column E has the formula
=IF(ISERROR(MATCH($D2&E$1,
Column F has
=IF(COUNTA($E2:E2)>0,E2&",
Hide columns E to H. In column I use
=SUBSTITUTE(SUBSTITUTE(H2,
This can be expanded to as many positions as there are in the data.
You can use a pivot table to set up the initial grid of unique names in the rows and unique list of positions in the column headers. Then copy the unique names and use paste special > values to paste to a new table. Copy the unique position names and paste special > values across the top.
cheers, teylyn
NotPivot.xlsx
please see attached a suggestion with helper columns that can be hidden.
Column E has the formula
=IF(ISERROR(MATCH($D2&E$1,
Column F has
=IF(COUNTA($E2:E2)>0,E2&",
Hide columns E to H. In column I use
=SUBSTITUTE(SUBSTITUTE(H2,
This can be expanded to as many positions as there are in the data.
You can use a pivot table to set up the initial grid of unique names in the rows and unique list of positions in the column headers. Then copy the unique names and use paste special > values to paste to a new table. Copy the unique position names and paste special > values across the top.
cheers, teylyn
NotPivot.xlsx
Thanks, propertytax.
Please see the attached. A few points...
(1) The formulas are in the yellow (and one red) cells. Copy them down for as many as rows as you have data in column A. You can overshoot (as I have in rows 28 to 31) without breaking anything.
(2) If you want to be fancy the formulas in columns F anf H:I only need to go down as many rows as the number of unique names (E2).
(3) The red cell is highlighted because, apologies, its formula is different from the other cells in that column.
(4) If you may have more than 5,000 entries in column A then the formulas in column F need to be changed.
Regards,
Brian.
Denormalize-No-VBA.xlsx
Please see the attached. A few points...
(1) The formulas are in the yellow (and one red) cells. Copy them down for as many as rows as you have data in column A. You can overshoot (as I have in rows 28 to 31) without breaking anything.
(2) If you want to be fancy the formulas in columns F anf H:I only need to go down as many rows as the number of unique names (E2).
(3) The red cell is highlighted because, apologies, its formula is different from the other cells in that column.
(4) If you may have more than 5,000 entries in column A then the formulas in column F need to be changed.
Regards,
Brian.
Denormalize-No-VBA.xlsx
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.
Active today
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.
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Suggested Courses
Course of the Month3 days, 7 hours left to enroll
691 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.