Use Excel to format denormalized table as report with summary headers and detail rows
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
It is common where I work to obtain a CSV or Excel sheet that contains denormalized report data. Here is a simple example:
Category ID | Category Name | Detail one | Detail two
------------|---------------|------------|-----------
1 | Cat1 | 65 | Jane
1 | Cat1 | 13 | Jill
1 | Cat1 | 25 | Jack
2 | Cat2 | 60 | John
2 | Cat2 | 25 | Jeremy
2 | Cat2 | 84 | Jason
I want to know how to create a report from this kind of data with groupings and detail bands, similar to how one would build a report in SQL Server Reporting Services. I don't want to remove any information, I just want to format the data so that "categories" are only listed once, and all of the detail rows are listed under each category. Here is the example output:
Category ID | Category Name | Detail one | Detail two
------------|---------------|------------|-----------
1 | Cat1 | 103 |
| | 65 | Jane
| | 13 | Jill
| | 25 | Jack
2 | Cat2 | 169 |
| | 60 | John
| | 25 | Jeremy
| | 84 | Jason
I have added a sum field in the grouping row of column C, but this is not strictly required.
I have experimented with pivot tables and power query, but I cannot seem to figure out how to do grouping with these tools that do NOT aggregate all of the detail rows. As you can see, I am not trying to do a pivot of any kind -- I'm just trying to create category headers automatically based on the data in the table.
Is there any way to achieve the desired outcome by applying something to the entire source table at once?
microsoft-excel power-query
edited Jan 28 at 22:18
fixer1234
19.5k145082
asked Jul 10 '17 at 17:21
NReilinghNReilingh
5,06922046
add a comment |
It is common where I work to obtain a CSV or Excel sheet that contains denormalized report data. Here is a simple example:
Category ID | Category Name | Detail one | Detail two
------------|---------------|------------|-----------
1 | Cat1 | 65 | Jane
1 | Cat1 | 13 | Jill
1 | Cat1 | 25 | Jack
2 | Cat2 | 60 | John
2 | Cat2 | 25 | Jeremy
2 | Cat2 | 84 | Jason
I want to know how to create a report from this kind of data with groupings and detail bands, similar to how one would build a report in SQL Server Reporting Services. I don't want to remove any information, I just want to format the data so that "categories" are only listed once, and all of the detail rows are listed under each category. Here is the example output:
Category ID | Category Name | Detail one | Detail two
------------|---------------|------------|-----------
1 | Cat1 | 103 |
| | 65 | Jane
| | 13 | Jill
| | 25 | Jack
2 | Cat2 | 169 |
| | 60 | John
| | 25 | Jeremy
| | 84 | Jason
I have added a sum field in the grouping row of column C, but this is not strictly required.
I have experimented with pivot tables and power query, but I cannot seem to figure out how to do grouping with these tools that do NOT aggregate all of the detail rows. As you can see, I am not trying to do a pivot of any kind -- I'm just trying to create category headers automatically based on the data in the table.
Is there any way to achieve the desired outcome by applying something to the entire source table at once?
microsoft-excel power-query
edited Jan 28 at 22:18
fixer1234
19.5k145082
asked Jul 10 '17 at 17:21
NReilinghNReilingh
5,06922046
One way is to use conditional formatting to blank (font color same as interior color) the cells in the first two columns if they are the same as the cell above. And you could use theData --> Subtotalwizard to get the subtotals of each group. OH, and hide the Grand Total row if you don't need it.
– Ron Rosenfeld
Jul 13 '17 at 1:01
add a comment |
It is common where I work to obtain a CSV or Excel sheet that contains denormalized report data. Here is a simple example:
Category ID | Category Name | Detail one | Detail two
------------|---------------|------------|-----------
1 | Cat1 | 65 | Jane
1 | Cat1 | 13 | Jill
1 | Cat1 | 25 | Jack
2 | Cat2 | 60 | John
2 | Cat2 | 25 | Jeremy
2 | Cat2 | 84 | Jason
I want to know how to create a report from this kind of data with groupings and detail bands, similar to how one would build a report in SQL Server Reporting Services. I don't want to remove any information, I just want to format the data so that "categories" are only listed once, and all of the detail rows are listed under each category. Here is the example output:
Category ID | Category Name | Detail one | Detail two
------------|---------------|------------|-----------
1 | Cat1 | 103 |
| | 65 | Jane
| | 13 | Jill
| | 25 | Jack
2 | Cat2 | 169 |
| | 60 | John
| | 25 | Jeremy
| | 84 | Jason
I have added a sum field in the grouping row of column C, but this is not strictly required.
I have experimented with pivot tables and power query, but I cannot seem to figure out how to do grouping with these tools that do NOT aggregate all of the detail rows. As you can see, I am not trying to do a pivot of any kind -- I'm just trying to create category headers automatically based on the data in the table.
Is there any way to achieve the desired outcome by applying something to the entire source table at once?
microsoft-excel power-query
edited Jan 28 at 22:18
fixer1234
19.5k145082
asked Jul 10 '17 at 17:21
NReilinghNReilingh
5,06922046
It is common where I work to obtain a CSV or Excel sheet that contains denormalized report data. Here is a simple example:
Category ID | Category Name | Detail one | Detail two
------------|---------------|------------|-----------
1 | Cat1 | 65 | Jane
1 | Cat1 | 13 | Jill
1 | Cat1 | 25 | Jack
2 | Cat2 | 60 | John
2 | Cat2 | 25 | Jeremy
2 | Cat2 | 84 | Jason
I want to know how to create a report from this kind of data with groupings and detail bands, similar to how one would build a report in SQL Server Reporting Services. I don't want to remove any information, I just want to format the data so that "categories" are only listed once, and all of the detail rows are listed under each category. Here is the example output:
Category ID | Category Name | Detail one | Detail two
------------|---------------|------------|-----------
1 | Cat1 | 103 |
| | 65 | Jane
| | 13 | Jill
| | 25 | Jack
2 | Cat2 | 169 |
| | 60 | John
| | 25 | Jeremy
| | 84 | Jason
I have added a sum field in the grouping row of column C, but this is not strictly required.
I have experimented with pivot tables and power query, but I cannot seem to figure out how to do grouping with these tools that do NOT aggregate all of the detail rows. As you can see, I am not trying to do a pivot of any kind -- I'm just trying to create category headers automatically based on the data in the table.
Is there any way to achieve the desired outcome by applying something to the entire source table at once?
microsoft-excel power-query
microsoft-excel power-query
edited Jan 28 at 22:18
fixer1234
19.5k145082
asked Jul 10 '17 at 17:21
NReilinghNReilingh
5,06922046
edited Jan 28 at 22:18
fixer1234
19.5k145082
asked Jul 10 '17 at 17:21
NReilinghNReilingh
5,06922046
edited Jan 28 at 22:18
fixer1234
19.5k145082
edited Jan 28 at 22:18
fixer1234
19.5k145082
edited Jan 28 at 22:18
fixer1234
19.5k145082
19.5k145082
asked Jul 10 '17 at 17:21
NReilinghNReilingh
5,06922046
asked Jul 10 '17 at 17:21
NReilinghNReilingh
5,06922046
asked Jul 10 '17 at 17:21
NReilinghNReilingh
5,06922046
5,06922046
One way is to use conditional formatting to blank (font color same as interior color) the cells in the first two columns if they are the same as the cell above. And you could use theData --> Subtotalwizard to get the subtotals of each group. OH, and hide the Grand Total row if you don't need it.
– Ron Rosenfeld
Jul 13 '17 at 1:01
add a comment |
One way is to use conditional formatting to blank (font color same as interior color) the cells in the first two columns if they are the same as the cell above. And you could use theData --> Subtotalwizard to get the subtotals of each group. OH, and hide the Grand Total row if you don't need it.
– Ron Rosenfeld
Jul 13 '17 at 1:01
One way is to use conditional formatting to blank (font color same as interior color) the cells in the first two columns if they are the same as the cell above. And you could use the
Data --> Subtotal wizard to get the subtotals of each group. OH, and hide the Grand Total row if you don't need it.– Ron Rosenfeld
Jul 13 '17 at 1:01
One way is to use conditional formatting to blank (font color same as interior color) the cells in the first two columns if they are the same as the cell above. And you could use the
Data --> Subtotal wizard to get the subtotals of each group. OH, and hide the Grand Total row if you don't need it.– Ron Rosenfeld
Jul 13 '17 at 1:01
add a comment |
1 Answer
1
active
oldest
votes
You can do what you want with a Pivot Table. Put all four fields in the Rows section, choose 'Show In Tabular Form' in Report Layout, and remove subtotals. Format to suit.
answered Aug 21 '17 at 8:58
OllyOlly
20918
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1228537%2fuse-excel-to-format-denormalized-table-as-report-with-summary-headers-and-detail%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can do what you want with a Pivot Table. Put all four fields in the Rows section, choose 'Show In Tabular Form' in Report Layout, and remove subtotals. Format to suit.
answered Aug 21 '17 at 8:58
OllyOlly
20918
add a comment |
You can do what you want with a Pivot Table. Put all four fields in the Rows section, choose 'Show In Tabular Form' in Report Layout, and remove subtotals. Format to suit.
answered Aug 21 '17 at 8:58
OllyOlly
20918
add a comment |
You can do what you want with a Pivot Table. Put all four fields in the Rows section, choose 'Show In Tabular Form' in Report Layout, and remove subtotals. Format to suit.
answered Aug 21 '17 at 8:58
OllyOlly
20918
You can do what you want with a Pivot Table. Put all four fields in the Rows section, choose 'Show In Tabular Form' in Report Layout, and remove subtotals. Format to suit.
answered Aug 21 '17 at 8:58
OllyOlly
20918
answered Aug 21 '17 at 8:58
OllyOlly
20918
answered Aug 21 '17 at 8:58
OllyOlly
20918
answered Aug 21 '17 at 8:58
OllyOlly
20918
20918
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1228537%2fuse-excel-to-format-denormalized-table-as-report-with-summary-headers-and-detail%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
One way is to use conditional formatting to blank (font color same as interior color) the cells in the first two columns if they are the same as the cell above. And you could use the
Data --> Subtotalwizard to get the subtotals of each group. OH, and hide the Grand Total row if you don't need it.– Ron Rosenfeld
Jul 13 '17 at 1:01