How to create a text based Crosstab (pivot) in Excel?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
Suppose I have a list of issues that have the following:
- Priority: 1-5 (how urgent something is)
- Impact: 1-5 (how many people will be affected by the problem)
- Desc: 15 chars (a very short description)
What I would like to see is this:
impact 1 2 3 ...
priority
1 network fail password blocked
2 badge issue
3 toolbar hidden
...
The only things I have seen on the web are very limited examples that do not appear to be able to cope with the display of different text
Is it possible to do this easily or would it require more complex VBA?
In the event of multiple values appearing in one CELL it should just append ","
EDIT:
Sample input data
Description Impact Priority
network fail 1 1
toolbar hidden 2 3
password blocked 3 1
...
microsoft-excel microsoft-excel-2013 pivot-table crosstab
edited Jan 28 at 22:10
fixer1234
19.5k145082
asked Jun 26 '18 at 7:28
adolf garlicadolf garlic
87582542
add a comment |
0
Suppose I have a list of issues that have the following:
- Priority: 1-5 (how urgent something is)
- Impact: 1-5 (how many people will be affected by the problem)
- Desc: 15 chars (a very short description)
What I would like to see is this:
impact 1 2 3 ...
priority
1 network fail password blocked
2 badge issue
3 toolbar hidden
...
The only things I have seen on the web are very limited examples that do not appear to be able to cope with the display of different text
Is it possible to do this easily or would it require more complex VBA?
In the event of multiple values appearing in one CELL it should just append ","
EDIT:
Sample input data
Description Impact Priority
network fail 1 1
toolbar hidden 2 3
password blocked 3 1
...
microsoft-excel microsoft-excel-2013 pivot-table crosstab
edited Jan 28 at 22:10
fixer1234
19.5k145082
asked Jun 26 '18 at 7:28
adolf garlicadolf garlic
87582542
Show us a sample of your data
– PeterH
Jun 26 '18 at 7:38
well I've shown the schema (the 3 fields) and you can see the desired output so...
– adolf garlic
Jun 26 '18 at 9:07
soooo.. what is the data looking like originally ? 3 columns individual columns ? do they have headers ?? I cant even attempt to create the output without seeing the input
– PeterH
Jun 26 '18 at 9:45
priority can be a value from 1 to 5, impact can be a value from one to five and description can be 15 characters long
– adolf garlic
Jun 26 '18 at 11:28
add a comment |
0
0
0
Suppose I have a list of issues that have the following:
- Priority: 1-5 (how urgent something is)
- Impact: 1-5 (how many people will be affected by the problem)
- Desc: 15 chars (a very short description)
What I would like to see is this:
impact 1 2 3 ...
priority
1 network fail password blocked
2 badge issue
3 toolbar hidden
...
The only things I have seen on the web are very limited examples that do not appear to be able to cope with the display of different text
Is it possible to do this easily or would it require more complex VBA?
In the event of multiple values appearing in one CELL it should just append ","
EDIT:
Sample input data
Description Impact Priority
network fail 1 1
toolbar hidden 2 3
password blocked 3 1
...
microsoft-excel microsoft-excel-2013 pivot-table crosstab
edited Jan 28 at 22:10
fixer1234
19.5k145082
asked Jun 26 '18 at 7:28
adolf garlicadolf garlic
87582542
Suppose I have a list of issues that have the following:
- Priority: 1-5 (how urgent something is)
- Impact: 1-5 (how many people will be affected by the problem)
- Desc: 15 chars (a very short description)
What I would like to see is this:
impact 1 2 3 ...
priority
1 network fail password blocked
2 badge issue
3 toolbar hidden
...
The only things I have seen on the web are very limited examples that do not appear to be able to cope with the display of different text
Is it possible to do this easily or would it require more complex VBA?
In the event of multiple values appearing in one CELL it should just append ","
EDIT:
Sample input data
Description Impact Priority
network fail 1 1
toolbar hidden 2 3
password blocked 3 1
...
microsoft-excel microsoft-excel-2013 pivot-table crosstab
microsoft-excel microsoft-excel-2013 pivot-table crosstab
edited Jan 28 at 22:10
fixer1234
19.5k145082
asked Jun 26 '18 at 7:28
adolf garlicadolf garlic
87582542
edited Jan 28 at 22:10
fixer1234
19.5k145082
asked Jun 26 '18 at 7:28
adolf garlicadolf garlic
87582542
edited Jan 28 at 22:10
fixer1234
19.5k145082
edited Jan 28 at 22:10
fixer1234
19.5k145082
edited Jan 28 at 22:10
fixer1234
19.5k145082
19.5k145082
asked Jun 26 '18 at 7:28
adolf garlicadolf garlic
87582542
asked Jun 26 '18 at 7:28
adolf garlicadolf garlic
87582542
asked Jun 26 '18 at 7:28
adolf garlicadolf garlic
87582542
87582542
Show us a sample of your data
– PeterH
Jun 26 '18 at 7:38
well I've shown the schema (the 3 fields) and you can see the desired output so...
– adolf garlic
Jun 26 '18 at 9:07
soooo.. what is the data looking like originally ? 3 columns individual columns ? do they have headers ?? I cant even attempt to create the output without seeing the input
– PeterH
Jun 26 '18 at 9:45
priority can be a value from 1 to 5, impact can be a value from one to five and description can be 15 characters long
– adolf garlic
Jun 26 '18 at 11:28
add a comment |
Show us a sample of your data
– PeterH
Jun 26 '18 at 7:38
well I've shown the schema (the 3 fields) and you can see the desired output so...
– adolf garlic
Jun 26 '18 at 9:07
soooo.. what is the data looking like originally ? 3 columns individual columns ? do they have headers ?? I cant even attempt to create the output without seeing the input
– PeterH
Jun 26 '18 at 9:45
priority can be a value from 1 to 5, impact can be a value from one to five and description can be 15 characters long
– adolf garlic
Jun 26 '18 at 11:28
Show us a sample of your data
– PeterH
Jun 26 '18 at 7:38
Show us a sample of your data
– PeterH
Jun 26 '18 at 7:38
well I've shown the schema (the 3 fields) and you can see the desired output so...
– adolf garlic
Jun 26 '18 at 9:07
well I've shown the schema (the 3 fields) and you can see the desired output so...
– adolf garlic
Jun 26 '18 at 9:07
soooo.. what is the data looking like originally ? 3 columns individual columns ? do they have headers ?? I cant even attempt to create the output without seeing the input
– PeterH
Jun 26 '18 at 9:45
soooo.. what is the data looking like originally ? 3 columns individual columns ? do they have headers ?? I cant even attempt to create the output without seeing the input
– PeterH
Jun 26 '18 at 9:45
priority can be a value from 1 to 5, impact can be a value from one to five and description can be 15 characters long
– adolf garlic
Jun 26 '18 at 11:28
priority can be a value from 1 to 5, impact can be a value from one to five and description can be 15 characters long
– adolf garlic
Jun 26 '18 at 11:28
add a comment |
1 Answer
1
active
oldest
votes
0
see my answer on Stackoverflow concerning the same kind of problem.
Since you use Excel 2013, you will have to install the free MS Add-in Power Query*
Follow these steps:
- Import your data in Power Query (define your Inputs as table)
Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010) - Select the column Impact
- on the ribbon Transform, choose Pivot column
- Values Column = Description
- Aggregate Value Function = no Aggregation
- on the ribbon Transform, choose Pivot column
- Click Close & Load from the Home tab
The M-Code is this one
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}, {"Impact", Int64.Type}, {"Priority", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Impact", type text}}, "de-CH"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Impact", type text}}, "de-CH")[Impact]), "Impact", "Description")
in
#"Pivoted Column"
.* from MS Office 2016, it's fully integrated in Excel as Get & Transform function.
answered Jun 27 '18 at 9:42
visu-lvisu-l
44628
seems like it can only support one value per result cell? that's not exactly what I was after - there could be multiple values per cell, it should show all of them
– adolf garlic
Jul 4 '18 at 8:40
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
});
}
});
draft saved
draft discarded
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%2f1334252%2fhow-to-create-a-text-based-crosstab-pivot-in-excel%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
0
see my answer on Stackoverflow concerning the same kind of problem.
Since you use Excel 2013, you will have to install the free MS Add-in Power Query*
Follow these steps:
- Import your data in Power Query (define your Inputs as table)
Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010) - Select the column Impact
- on the ribbon Transform, choose Pivot column
- Values Column = Description
- Aggregate Value Function = no Aggregation
- on the ribbon Transform, choose Pivot column
- Click Close & Load from the Home tab
The M-Code is this one
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}, {"Impact", Int64.Type}, {"Priority", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Impact", type text}}, "de-CH"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Impact", type text}}, "de-CH")[Impact]), "Impact", "Description")
in
#"Pivoted Column"
.* from MS Office 2016, it's fully integrated in Excel as Get & Transform function.
answered Jun 27 '18 at 9:42
visu-lvisu-l
44628
seems like it can only support one value per result cell? that's not exactly what I was after - there could be multiple values per cell, it should show all of them
– adolf garlic
Jul 4 '18 at 8:40
add a comment |
0
see my answer on Stackoverflow concerning the same kind of problem.
Since you use Excel 2013, you will have to install the free MS Add-in Power Query*
Follow these steps:
- Import your data in Power Query (define your Inputs as table)
Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010) - Select the column Impact
- on the ribbon Transform, choose Pivot column
- Values Column = Description
- Aggregate Value Function = no Aggregation
- on the ribbon Transform, choose Pivot column
- Click Close & Load from the Home tab
The M-Code is this one
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}, {"Impact", Int64.Type}, {"Priority", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Impact", type text}}, "de-CH"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Impact", type text}}, "de-CH")[Impact]), "Impact", "Description")
in
#"Pivoted Column"
.* from MS Office 2016, it's fully integrated in Excel as Get & Transform function.
answered Jun 27 '18 at 9:42
visu-lvisu-l
44628
seems like it can only support one value per result cell? that's not exactly what I was after - there could be multiple values per cell, it should show all of them
– adolf garlic
Jul 4 '18 at 8:40
add a comment |
0
0
0
see my answer on Stackoverflow concerning the same kind of problem.
Since you use Excel 2013, you will have to install the free MS Add-in Power Query*
Follow these steps:
- Import your data in Power Query (define your Inputs as table)
Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010) - Select the column Impact
- on the ribbon Transform, choose Pivot column
- Values Column = Description
- Aggregate Value Function = no Aggregation
- on the ribbon Transform, choose Pivot column
- Click Close & Load from the Home tab
The M-Code is this one
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}, {"Impact", Int64.Type}, {"Priority", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Impact", type text}}, "de-CH"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Impact", type text}}, "de-CH")[Impact]), "Impact", "Description")
in
#"Pivoted Column"
.* from MS Office 2016, it's fully integrated in Excel as Get & Transform function.
answered Jun 27 '18 at 9:42
visu-lvisu-l
44628
see my answer on Stackoverflow concerning the same kind of problem.
Since you use Excel 2013, you will have to install the free MS Add-in Power Query*
Follow these steps:
- Import your data in Power Query (define your Inputs as table)
Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010) - Select the column Impact
- on the ribbon Transform, choose Pivot column
- Values Column = Description
- Aggregate Value Function = no Aggregation
- on the ribbon Transform, choose Pivot column
- Click Close & Load from the Home tab
The M-Code is this one
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}, {"Impact", Int64.Type}, {"Priority", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Impact", type text}}, "de-CH"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Impact", type text}}, "de-CH")[Impact]), "Impact", "Description")
in
#"Pivoted Column"
.* from MS Office 2016, it's fully integrated in Excel as Get & Transform function.
answered Jun 27 '18 at 9:42
visu-lvisu-l
44628
edited Jun 27 '18 at 10:09
answered Jun 27 '18 at 9:42
visu-lvisu-l
44628
answered Jun 27 '18 at 9:42
visu-lvisu-l
44628
answered Jun 27 '18 at 9:42
visu-lvisu-l
44628
44628
seems like it can only support one value per result cell? that's not exactly what I was after - there could be multiple values per cell, it should show all of them
– adolf garlic
Jul 4 '18 at 8:40
add a comment |
seems like it can only support one value per result cell? that's not exactly what I was after - there could be multiple values per cell, it should show all of them
– adolf garlic
Jul 4 '18 at 8:40
seems like it can only support one value per result cell? that's not exactly what I was after - there could be multiple values per cell, it should show all of them
– adolf garlic
Jul 4 '18 at 8:40
seems like it can only support one value per result cell? that's not exactly what I was after - there could be multiple values per cell, it should show all of them
– adolf garlic
Jul 4 '18 at 8:40
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f1334252%2fhow-to-create-a-text-based-crosstab-pivot-in-excel%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
Show us a sample of your data
– PeterH
Jun 26 '18 at 7:38
well I've shown the schema (the 3 fields) and you can see the desired output so...
– adolf garlic
Jun 26 '18 at 9:07
soooo.. what is the data looking like originally ? 3 columns individual columns ? do they have headers ?? I cant even attempt to create the output without seeing the input
– PeterH
Jun 26 '18 at 9:45
priority can be a value from 1 to 5, impact can be a value from one to five and description can be 15 characters long
– adolf garlic
Jun 26 '18 at 11:28