Excel table conversion help needed
Here is the current table:
I would like it to look like this:
As you can see in the original table, some people don't have data (paid up) for the categories of "rent" "late", "utilities", etc.
I have tried vlookup and it works okay, but I can't get the data per person without changing the array table per person.
microsoft-excel worksheet-function vlookup
edited Feb 6 at 3:03
angelofdev
929120
asked Nov 6 '18 at 17:18
user3105519user3105519
1
add a comment |
Here is the current table:
I would like it to look like this:
As you can see in the original table, some people don't have data (paid up) for the categories of "rent" "late", "utilities", etc.
I have tried vlookup and it works okay, but I can't get the data per person without changing the array table per person.
microsoft-excel worksheet-function vlookup
edited Feb 6 at 3:03
angelofdev
929120
asked Nov 6 '18 at 17:18
user3105519user3105519
1
Create a new sheet. The data in your first image would could be sheet "Data" and the new sheet could be "Totals". In Totals you would set A2=Data!A2 which would be Bob. B2=Data!C2+Data!C3+Data!C4[etc...] which would output 1,500. For utilities, you just grab the data for utilities, same for late fees, and so on to break those down like you have in your second image. Basically a sheet for inputting all of your data and a sheet which grabs all of that data and makes it readable.
– Andrew
Nov 6 '18 at 18:30
I don't see how your two tables are related. Names and data seem quite different.
– Ron Rosenfeld
Nov 6 '18 at 20:44
Ignore the names @RonRosenfeld its the format that is important
– user3105519
Nov 7 '18 at 21:28
I also don’t see how your two tables are related. None of the numbers in the second table are traceable to the first table in any obvious way. (The fact that both tables contain “0.00” doesn’t count, because the zeros in the first table are associated with Sally, who isn’t in the second table.) It’s bizarre that the first table has a person (Sally) who isn’t in the second table, and the second table has four people who aren’t in the first one. … (Cont’d)
– Scott
Feb 6 at 3:04
(Cont’d) … And, after pointing out that some people (again, e.g., Sally) are prepaid and don’t have itemized (categorized) charges, you don’t say or show how such people should appear in the second table. (And, BTW, from a real world point of view, I don’t see why a creditor wouldn’t compute and keep itemized records of the charges of people who are prepaid.)
– Scott
Feb 6 at 3:04
add a comment |
Here is the current table:
I would like it to look like this:
As you can see in the original table, some people don't have data (paid up) for the categories of "rent" "late", "utilities", etc.
I have tried vlookup and it works okay, but I can't get the data per person without changing the array table per person.
microsoft-excel worksheet-function vlookup
edited Feb 6 at 3:03
angelofdev
929120
asked Nov 6 '18 at 17:18
user3105519user3105519
1
Here is the current table:
I would like it to look like this:
As you can see in the original table, some people don't have data (paid up) for the categories of "rent" "late", "utilities", etc.
I have tried vlookup and it works okay, but I can't get the data per person without changing the array table per person.
microsoft-excel worksheet-function vlookup
microsoft-excel worksheet-function vlookup
edited Feb 6 at 3:03
angelofdev
929120
asked Nov 6 '18 at 17:18
user3105519user3105519
1
edited Feb 6 at 3:03
angelofdev
929120
asked Nov 6 '18 at 17:18
user3105519user3105519
1
edited Feb 6 at 3:03
angelofdev
929120
edited Feb 6 at 3:03
angelofdev
929120
edited Feb 6 at 3:03
angelofdev
929120
929120
asked Nov 6 '18 at 17:18
user3105519user3105519
1
asked Nov 6 '18 at 17:18
user3105519user3105519
1
asked Nov 6 '18 at 17:18
user3105519user3105519
1
1
Create a new sheet. The data in your first image would could be sheet "Data" and the new sheet could be "Totals". In Totals you would set A2=Data!A2 which would be Bob. B2=Data!C2+Data!C3+Data!C4[etc...] which would output 1,500. For utilities, you just grab the data for utilities, same for late fees, and so on to break those down like you have in your second image. Basically a sheet for inputting all of your data and a sheet which grabs all of that data and makes it readable.
– Andrew
Nov 6 '18 at 18:30
I don't see how your two tables are related. Names and data seem quite different.
– Ron Rosenfeld
Nov 6 '18 at 20:44
Ignore the names @RonRosenfeld its the format that is important
– user3105519
Nov 7 '18 at 21:28
I also don’t see how your two tables are related. None of the numbers in the second table are traceable to the first table in any obvious way. (The fact that both tables contain “0.00” doesn’t count, because the zeros in the first table are associated with Sally, who isn’t in the second table.) It’s bizarre that the first table has a person (Sally) who isn’t in the second table, and the second table has four people who aren’t in the first one. … (Cont’d)
– Scott
Feb 6 at 3:04
(Cont’d) … And, after pointing out that some people (again, e.g., Sally) are prepaid and don’t have itemized (categorized) charges, you don’t say or show how such people should appear in the second table. (And, BTW, from a real world point of view, I don’t see why a creditor wouldn’t compute and keep itemized records of the charges of people who are prepaid.)
– Scott
Feb 6 at 3:04
add a comment |
Create a new sheet. The data in your first image would could be sheet "Data" and the new sheet could be "Totals". In Totals you would set A2=Data!A2 which would be Bob. B2=Data!C2+Data!C3+Data!C4[etc...] which would output 1,500. For utilities, you just grab the data for utilities, same for late fees, and so on to break those down like you have in your second image. Basically a sheet for inputting all of your data and a sheet which grabs all of that data and makes it readable.
– Andrew
Nov 6 '18 at 18:30
I don't see how your two tables are related. Names and data seem quite different.
– Ron Rosenfeld
Nov 6 '18 at 20:44
Ignore the names @RonRosenfeld its the format that is important
– user3105519
Nov 7 '18 at 21:28
I also don’t see how your two tables are related. None of the numbers in the second table are traceable to the first table in any obvious way. (The fact that both tables contain “0.00” doesn’t count, because the zeros in the first table are associated with Sally, who isn’t in the second table.) It’s bizarre that the first table has a person (Sally) who isn’t in the second table, and the second table has four people who aren’t in the first one. … (Cont’d)
– Scott
Feb 6 at 3:04
(Cont’d) … And, after pointing out that some people (again, e.g., Sally) are prepaid and don’t have itemized (categorized) charges, you don’t say or show how such people should appear in the second table. (And, BTW, from a real world point of view, I don’t see why a creditor wouldn’t compute and keep itemized records of the charges of people who are prepaid.)
– Scott
Feb 6 at 3:04
Create a new sheet. The data in your first image would could be sheet "Data" and the new sheet could be "Totals". In Totals you would set A2=Data!A2 which would be Bob. B2=Data!C2+Data!C3+Data!C4[etc...] which would output 1,500. For utilities, you just grab the data for utilities, same for late fees, and so on to break those down like you have in your second image. Basically a sheet for inputting all of your data and a sheet which grabs all of that data and makes it readable.
– Andrew
Nov 6 '18 at 18:30
Create a new sheet. The data in your first image would could be sheet "Data" and the new sheet could be "Totals". In Totals you would set A2=Data!A2 which would be Bob. B2=Data!C2+Data!C3+Data!C4[etc...] which would output 1,500. For utilities, you just grab the data for utilities, same for late fees, and so on to break those down like you have in your second image. Basically a sheet for inputting all of your data and a sheet which grabs all of that data and makes it readable.
– Andrew
Nov 6 '18 at 18:30
I don't see how your two tables are related. Names and data seem quite different.
– Ron Rosenfeld
Nov 6 '18 at 20:44
I don't see how your two tables are related. Names and data seem quite different.
– Ron Rosenfeld
Nov 6 '18 at 20:44
Ignore the names @RonRosenfeld its the format that is important
– user3105519
Nov 7 '18 at 21:28
Ignore the names @RonRosenfeld its the format that is important
– user3105519
Nov 7 '18 at 21:28
I also don’t see how your two tables are related. None of the numbers in the second table are traceable to the first table in any obvious way. (The fact that both tables contain “0.00” doesn’t count, because the zeros in the first table are associated with Sally, who isn’t in the second table.) It’s bizarre that the first table has a person (Sally) who isn’t in the second table, and the second table has four people who aren’t in the first one. … (Cont’d)
– Scott
Feb 6 at 3:04
I also don’t see how your two tables are related. None of the numbers in the second table are traceable to the first table in any obvious way. (The fact that both tables contain “0.00” doesn’t count, because the zeros in the first table are associated with Sally, who isn’t in the second table.) It’s bizarre that the first table has a person (Sally) who isn’t in the second table, and the second table has four people who aren’t in the first one. … (Cont’d)
– Scott
Feb 6 at 3:04
(Cont’d) … And, after pointing out that some people (again, e.g., Sally) are prepaid and don’t have itemized (categorized) charges, you don’t say or show how such people should appear in the second table. (And, BTW, from a real world point of view, I don’t see why a creditor wouldn’t compute and keep itemized records of the charges of people who are prepaid.)
– Scott
Feb 6 at 3:04
(Cont’d) … And, after pointing out that some people (again, e.g., Sally) are prepaid and don’t have itemized (categorized) charges, you don’t say or show how such people should appear in the second table. (And, BTW, from a real world point of view, I don’t see why a creditor wouldn’t compute and keep itemized records of the charges of people who are prepaid.)
– Scott
Feb 6 at 3:04
add a comment |
1 Answer
1
active
oldest
votes
Use SUMIFS instead of lookups, very powerful and flexible. The range would be the column with values, and you add two conditions, one for the person, the other for the type of charge.
The only change you need to do is to populate each line of your dataset with the appropriate name of the person. This you can easily automate with a formula as well of course.
Once you have a clean dataset, you also can use pivot tables, but I personally prefer to make my own summary with SUMIFS.
Interesting article with a comparison of SUMIF , LOOKUP , and INDEX/MATCH.
answered Nov 6 '18 at 18:45
Peter K.Peter K.
232110
Wouldn't SUMIFS give you the sums of count data with multiple criteria? I need the actual value for each criteria per person.
– user3105519
Nov 6 '18 at 19:56
@user3105519 TheSUMIFSgives you sum of the data, and it works also if there is only one occurrence (i.e. one value to be summed). It is theCOUNTIFSfunction that counts the occurrences that match the conditions.
– Peter K.
Nov 6 '18 at 21:59
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%2f1373203%2fexcel-table-conversion-help-needed%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
Use SUMIFS instead of lookups, very powerful and flexible. The range would be the column with values, and you add two conditions, one for the person, the other for the type of charge.
The only change you need to do is to populate each line of your dataset with the appropriate name of the person. This you can easily automate with a formula as well of course.
Once you have a clean dataset, you also can use pivot tables, but I personally prefer to make my own summary with SUMIFS.
Interesting article with a comparison of SUMIF , LOOKUP , and INDEX/MATCH.
answered Nov 6 '18 at 18:45
Peter K.Peter K.
232110
Wouldn't SUMIFS give you the sums of count data with multiple criteria? I need the actual value for each criteria per person.
– user3105519
Nov 6 '18 at 19:56
@user3105519 TheSUMIFSgives you sum of the data, and it works also if there is only one occurrence (i.e. one value to be summed). It is theCOUNTIFSfunction that counts the occurrences that match the conditions.
– Peter K.
Nov 6 '18 at 21:59
add a comment |
Use SUMIFS instead of lookups, very powerful and flexible. The range would be the column with values, and you add two conditions, one for the person, the other for the type of charge.
The only change you need to do is to populate each line of your dataset with the appropriate name of the person. This you can easily automate with a formula as well of course.
Once you have a clean dataset, you also can use pivot tables, but I personally prefer to make my own summary with SUMIFS.
Interesting article with a comparison of SUMIF , LOOKUP , and INDEX/MATCH.
answered Nov 6 '18 at 18:45
Peter K.Peter K.
232110
Wouldn't SUMIFS give you the sums of count data with multiple criteria? I need the actual value for each criteria per person.
– user3105519
Nov 6 '18 at 19:56
@user3105519 TheSUMIFSgives you sum of the data, and it works also if there is only one occurrence (i.e. one value to be summed). It is theCOUNTIFSfunction that counts the occurrences that match the conditions.
– Peter K.
Nov 6 '18 at 21:59
add a comment |
Use SUMIFS instead of lookups, very powerful and flexible. The range would be the column with values, and you add two conditions, one for the person, the other for the type of charge.
The only change you need to do is to populate each line of your dataset with the appropriate name of the person. This you can easily automate with a formula as well of course.
Once you have a clean dataset, you also can use pivot tables, but I personally prefer to make my own summary with SUMIFS.
Interesting article with a comparison of SUMIF , LOOKUP , and INDEX/MATCH.
answered Nov 6 '18 at 18:45
Peter K.Peter K.
232110
Use SUMIFS instead of lookups, very powerful and flexible. The range would be the column with values, and you add two conditions, one for the person, the other for the type of charge.
The only change you need to do is to populate each line of your dataset with the appropriate name of the person. This you can easily automate with a formula as well of course.
Once you have a clean dataset, you also can use pivot tables, but I personally prefer to make my own summary with SUMIFS.
Interesting article with a comparison of SUMIF , LOOKUP , and INDEX/MATCH.
answered Nov 6 '18 at 18:45
Peter K.Peter K.
232110
answered Nov 6 '18 at 18:45
Peter K.Peter K.
232110
answered Nov 6 '18 at 18:45
Peter K.Peter K.
232110
answered Nov 6 '18 at 18:45
Peter K.Peter K.
232110
232110
Wouldn't SUMIFS give you the sums of count data with multiple criteria? I need the actual value for each criteria per person.
– user3105519
Nov 6 '18 at 19:56
@user3105519 TheSUMIFSgives you sum of the data, and it works also if there is only one occurrence (i.e. one value to be summed). It is theCOUNTIFSfunction that counts the occurrences that match the conditions.
– Peter K.
Nov 6 '18 at 21:59
add a comment |
Wouldn't SUMIFS give you the sums of count data with multiple criteria? I need the actual value for each criteria per person.
– user3105519
Nov 6 '18 at 19:56
@user3105519 TheSUMIFSgives you sum of the data, and it works also if there is only one occurrence (i.e. one value to be summed). It is theCOUNTIFSfunction that counts the occurrences that match the conditions.
– Peter K.
Nov 6 '18 at 21:59
Wouldn't SUMIFS give you the sums of count data with multiple criteria? I need the actual value for each criteria per person.
– user3105519
Nov 6 '18 at 19:56
Wouldn't SUMIFS give you the sums of count data with multiple criteria? I need the actual value for each criteria per person.
– user3105519
Nov 6 '18 at 19:56
@user3105519 The
SUMIFS gives you sum of the data, and it works also if there is only one occurrence (i.e. one value to be summed). It is the COUNTIFS function that counts the occurrences that match the conditions.– Peter K.
Nov 6 '18 at 21:59
@user3105519 The
SUMIFS gives you sum of the data, and it works also if there is only one occurrence (i.e. one value to be summed). It is the COUNTIFS function that counts the occurrences that match the conditions.– Peter K.
Nov 6 '18 at 21:59
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%2f1373203%2fexcel-table-conversion-help-needed%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
Create a new sheet. The data in your first image would could be sheet "Data" and the new sheet could be "Totals". In Totals you would set A2=Data!A2 which would be Bob. B2=Data!C2+Data!C3+Data!C4[etc...] which would output 1,500. For utilities, you just grab the data for utilities, same for late fees, and so on to break those down like you have in your second image. Basically a sheet for inputting all of your data and a sheet which grabs all of that data and makes it readable.
– Andrew
Nov 6 '18 at 18:30
I don't see how your two tables are related. Names and data seem quite different.
– Ron Rosenfeld
Nov 6 '18 at 20:44
Ignore the names @RonRosenfeld its the format that is important
– user3105519
Nov 7 '18 at 21:28
I also don’t see how your two tables are related. None of the numbers in the second table are traceable to the first table in any obvious way. (The fact that both tables contain “0.00” doesn’t count, because the zeros in the first table are associated with Sally, who isn’t in the second table.) It’s bizarre that the first table has a person (Sally) who isn’t in the second table, and the second table has four people who aren’t in the first one. … (Cont’d)
– Scott
Feb 6 at 3:04
(Cont’d) … And, after pointing out that some people (again, e.g., Sally) are prepaid and don’t have itemized (categorized) charges, you don’t say or show how such people should appear in the second table. (And, BTW, from a real world point of view, I don’t see why a creditor wouldn’t compute and keep itemized records of the charges of people who are prepaid.)
– Scott
Feb 6 at 3:04