Transpose Stacked Data in One Column to Rows based on Variable Criteria and Unique Identifiers
Using Excel 2013 and I'm struggling to write a formula that allows me to transpose the amounts stacked in column E based on the deduction code in column F using various criteria on the first row/unique identifiers of each stacked data set. I'm thinking there are at least two potential options to solving this but I haven't cracked either yet.
Write a formula (or VBA script? I've only dabbled in formulas so far) based on the unique identifiers in columns A-D to alphabetize or sort by color the deduction code by stacked line item to consistently organize the Deduction Code column to then pull the amount from the Amount column and transpose the amounts into the correct deduction code G-K columns on the same row as the unique identifiers.
(I played with adding an A to the deduction code column for the main line item row but couldn't get that to work either.)Formula (or VBA) to automatically sort the data into groups of 4, 5, or 6 rows, based on how many rows make up each stacked line item/set of unique identifiers, and alphabetize the deductions codes. Then apply Index or Offset formulas or use the transpose function to put the data into the correct deduction code G-K columns on the same row as the unique identifiers.
I enjoy learning new ways Excel can make my life easier but am still working on my execution of complex data processing. These are the only two options I've thought of and tried to figure out but ultimately could not solve my issue.
I don't care if it takes several steps to solve my problem of converting vertically stacked data based on deduction codes into the correct columns and the same row as the unique identifiers as shown on rows 2, 8, 12, and 17.
Any help or solutions are welcome so I don't have to manually group and alphabetize the 4, 5 or 6 rows together then transpose the data into the correct columns.
Thank you!!
Link to the file:
https://drive.google.com/file/d/1jNpuRWsFqPRTo9J3-g4X1Qss5nFPRdET/view?usp=sharing
excel vba
asked Nov 14 '18 at 7:25
smitty53482smitty53482
12
add a comment |
Using Excel 2013 and I'm struggling to write a formula that allows me to transpose the amounts stacked in column E based on the deduction code in column F using various criteria on the first row/unique identifiers of each stacked data set. I'm thinking there are at least two potential options to solving this but I haven't cracked either yet.
Write a formula (or VBA script? I've only dabbled in formulas so far) based on the unique identifiers in columns A-D to alphabetize or sort by color the deduction code by stacked line item to consistently organize the Deduction Code column to then pull the amount from the Amount column and transpose the amounts into the correct deduction code G-K columns on the same row as the unique identifiers.
(I played with adding an A to the deduction code column for the main line item row but couldn't get that to work either.)Formula (or VBA) to automatically sort the data into groups of 4, 5, or 6 rows, based on how many rows make up each stacked line item/set of unique identifiers, and alphabetize the deductions codes. Then apply Index or Offset formulas or use the transpose function to put the data into the correct deduction code G-K columns on the same row as the unique identifiers.
I enjoy learning new ways Excel can make my life easier but am still working on my execution of complex data processing. These are the only two options I've thought of and tried to figure out but ultimately could not solve my issue.
I don't care if it takes several steps to solve my problem of converting vertically stacked data based on deduction codes into the correct columns and the same row as the unique identifiers as shown on rows 2, 8, 12, and 17.
Any help or solutions are welcome so I don't have to manually group and alphabetize the 4, 5 or 6 rows together then transpose the data into the correct columns.
Thank you!!
Link to the file:
https://drive.google.com/file/d/1jNpuRWsFqPRTo9J3-g4X1Qss5nFPRdET/view?usp=sharing
excel vba
asked Nov 14 '18 at 7:25
smitty53482smitty53482
12
add a comment |
Using Excel 2013 and I'm struggling to write a formula that allows me to transpose the amounts stacked in column E based on the deduction code in column F using various criteria on the first row/unique identifiers of each stacked data set. I'm thinking there are at least two potential options to solving this but I haven't cracked either yet.
Write a formula (or VBA script? I've only dabbled in formulas so far) based on the unique identifiers in columns A-D to alphabetize or sort by color the deduction code by stacked line item to consistently organize the Deduction Code column to then pull the amount from the Amount column and transpose the amounts into the correct deduction code G-K columns on the same row as the unique identifiers.
(I played with adding an A to the deduction code column for the main line item row but couldn't get that to work either.)Formula (or VBA) to automatically sort the data into groups of 4, 5, or 6 rows, based on how many rows make up each stacked line item/set of unique identifiers, and alphabetize the deductions codes. Then apply Index or Offset formulas or use the transpose function to put the data into the correct deduction code G-K columns on the same row as the unique identifiers.
I enjoy learning new ways Excel can make my life easier but am still working on my execution of complex data processing. These are the only two options I've thought of and tried to figure out but ultimately could not solve my issue.
I don't care if it takes several steps to solve my problem of converting vertically stacked data based on deduction codes into the correct columns and the same row as the unique identifiers as shown on rows 2, 8, 12, and 17.
Any help or solutions are welcome so I don't have to manually group and alphabetize the 4, 5 or 6 rows together then transpose the data into the correct columns.
Thank you!!
Link to the file:
https://drive.google.com/file/d/1jNpuRWsFqPRTo9J3-g4X1Qss5nFPRdET/view?usp=sharing
excel vba
asked Nov 14 '18 at 7:25
smitty53482smitty53482
12
Using Excel 2013 and I'm struggling to write a formula that allows me to transpose the amounts stacked in column E based on the deduction code in column F using various criteria on the first row/unique identifiers of each stacked data set. I'm thinking there are at least two potential options to solving this but I haven't cracked either yet.
Write a formula (or VBA script? I've only dabbled in formulas so far) based on the unique identifiers in columns A-D to alphabetize or sort by color the deduction code by stacked line item to consistently organize the Deduction Code column to then pull the amount from the Amount column and transpose the amounts into the correct deduction code G-K columns on the same row as the unique identifiers.
(I played with adding an A to the deduction code column for the main line item row but couldn't get that to work either.)Formula (or VBA) to automatically sort the data into groups of 4, 5, or 6 rows, based on how many rows make up each stacked line item/set of unique identifiers, and alphabetize the deductions codes. Then apply Index or Offset formulas or use the transpose function to put the data into the correct deduction code G-K columns on the same row as the unique identifiers.
I enjoy learning new ways Excel can make my life easier but am still working on my execution of complex data processing. These are the only two options I've thought of and tried to figure out but ultimately could not solve my issue.
I don't care if it takes several steps to solve my problem of converting vertically stacked data based on deduction codes into the correct columns and the same row as the unique identifiers as shown on rows 2, 8, 12, and 17.
Any help or solutions are welcome so I don't have to manually group and alphabetize the 4, 5 or 6 rows together then transpose the data into the correct columns.
Thank you!!
Link to the file:
https://drive.google.com/file/d/1jNpuRWsFqPRTo9J3-g4X1Qss5nFPRdET/view?usp=sharing
excel vba
excel vba
asked Nov 14 '18 at 7:25
smitty53482smitty53482
12
asked Nov 14 '18 at 7:25
smitty53482smitty53482
12
asked Nov 14 '18 at 7:25
smitty53482smitty53482
12
asked Nov 14 '18 at 7:25
smitty53482smitty53482
12
asked Nov 14 '18 at 7:25
smitty53482smitty53482
12
12
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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%2fstackoverflow.com%2fquestions%2f53295028%2ftranspose-stacked-data-in-one-column-to-rows-based-on-variable-criteria-and-uniq%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53295028%2ftranspose-stacked-data-in-one-column-to-rows-based-on-variable-criteria-and-uniq%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
