Mixing
Pivot table data retrieval
I know this is easy, just need a quick answer. I have a Pivot table that has three columns and about 1k rows. I need to get 1 number out of the list per agent. for example:
Example pic of the pivot table
^^the highlighted number is the number i need to return
What I've come up with is this:
=if(and('Calls Handled Pivot'!A1='Agent Breakdown'!A$3,'Calls Handled Pivot'!B1="wrap-up"),'Calls Handled Pivot'!C1, "")
So, If ColumnA = the agent's name AND columnB = "wrap-up" THEN give me the number in columnC, otherwise leave the cell blank.
Then i run that formula all the way down another sheet until it picks up the number. in the adjacent column i run the formula down all the rows AGAIN for the next agent until it picks up the number i want. after all of that, i just do the sum of all columns on that sheet to get the numbers all in row 1. the problem is with the amount of data running through this, it's basically crashing the page when it tries to run all those formulas.
I'm open to any ideas but i did have this thought-process: is there a way to run that formula so that instead of leaving the cell blank, it will just search down the next row until the number is found?
That or.. some kind of vlookup that verifies two values, like columnA = agents name AND columnB = "wrap-up"
excel google-sheets offset
asked Jan 2 at 0:36
driggersdriggers
6
add a comment |
I know this is easy, just need a quick answer. I have a Pivot table that has three columns and about 1k rows. I need to get 1 number out of the list per agent. for example:
Example pic of the pivot table
^^the highlighted number is the number i need to return
What I've come up with is this:
=if(and('Calls Handled Pivot'!A1='Agent Breakdown'!A$3,'Calls Handled Pivot'!B1="wrap-up"),'Calls Handled Pivot'!C1, "")
So, If ColumnA = the agent's name AND columnB = "wrap-up" THEN give me the number in columnC, otherwise leave the cell blank.
Then i run that formula all the way down another sheet until it picks up the number. in the adjacent column i run the formula down all the rows AGAIN for the next agent until it picks up the number i want. after all of that, i just do the sum of all columns on that sheet to get the numbers all in row 1. the problem is with the amount of data running through this, it's basically crashing the page when it tries to run all those formulas.
I'm open to any ideas but i did have this thought-process: is there a way to run that formula so that instead of leaving the cell blank, it will just search down the next row until the number is found?
That or.. some kind of vlookup that verifies two values, like columnA = agents name AND columnB = "wrap-up"
excel google-sheets offset
asked Jan 2 at 0:36
driggersdriggers
6
You should look into query. Try something like=QUERY(A:C, "Select * where B = 'Wrap-up' "). If you're looking for a specific agent, try=QUERY(A:C, "Select * where A = 'name goes here' and B = 'Wrap-up' "). developers.google.com/chart/interactive/docs/querylanguage
– s1c0j1
Jan 2 at 7:40
Excel and Google Sheets have not the same features for pivot tables. Which one are you using?
– Rubén
Jan 2 at 20:23
add a comment |
I know this is easy, just need a quick answer. I have a Pivot table that has three columns and about 1k rows. I need to get 1 number out of the list per agent. for example:
Example pic of the pivot table
^^the highlighted number is the number i need to return
What I've come up with is this:
=if(and('Calls Handled Pivot'!A1='Agent Breakdown'!A$3,'Calls Handled Pivot'!B1="wrap-up"),'Calls Handled Pivot'!C1, "")
So, If ColumnA = the agent's name AND columnB = "wrap-up" THEN give me the number in columnC, otherwise leave the cell blank.
Then i run that formula all the way down another sheet until it picks up the number. in the adjacent column i run the formula down all the rows AGAIN for the next agent until it picks up the number i want. after all of that, i just do the sum of all columns on that sheet to get the numbers all in row 1. the problem is with the amount of data running through this, it's basically crashing the page when it tries to run all those formulas.
I'm open to any ideas but i did have this thought-process: is there a way to run that formula so that instead of leaving the cell blank, it will just search down the next row until the number is found?
That or.. some kind of vlookup that verifies two values, like columnA = agents name AND columnB = "wrap-up"
excel google-sheets offset
asked Jan 2 at 0:36
driggersdriggers
6
I know this is easy, just need a quick answer. I have a Pivot table that has three columns and about 1k rows. I need to get 1 number out of the list per agent. for example:
Example pic of the pivot table
^^the highlighted number is the number i need to return
What I've come up with is this:
=if(and('Calls Handled Pivot'!A1='Agent Breakdown'!A$3,'Calls Handled Pivot'!B1="wrap-up"),'Calls Handled Pivot'!C1, "")
So, If ColumnA = the agent's name AND columnB = "wrap-up" THEN give me the number in columnC, otherwise leave the cell blank.
Then i run that formula all the way down another sheet until it picks up the number. in the adjacent column i run the formula down all the rows AGAIN for the next agent until it picks up the number i want. after all of that, i just do the sum of all columns on that sheet to get the numbers all in row 1. the problem is with the amount of data running through this, it's basically crashing the page when it tries to run all those formulas.
I'm open to any ideas but i did have this thought-process: is there a way to run that formula so that instead of leaving the cell blank, it will just search down the next row until the number is found?
That or.. some kind of vlookup that verifies two values, like columnA = agents name AND columnB = "wrap-up"
excel google-sheets offset
excel google-sheets offset
asked Jan 2 at 0:36
driggersdriggers
6
asked Jan 2 at 0:36
driggersdriggers
6
asked Jan 2 at 0:36
driggersdriggers
6
asked Jan 2 at 0:36
driggersdriggers
6
asked Jan 2 at 0:36
driggersdriggers
6
6
You should look into query. Try something like=QUERY(A:C, "Select * where B = 'Wrap-up' "). If you're looking for a specific agent, try=QUERY(A:C, "Select * where A = 'name goes here' and B = 'Wrap-up' "). developers.google.com/chart/interactive/docs/querylanguage
– s1c0j1
Jan 2 at 7:40
Excel and Google Sheets have not the same features for pivot tables. Which one are you using?
– Rubén
Jan 2 at 20:23
add a comment |
You should look into query. Try something like=QUERY(A:C, "Select * where B = 'Wrap-up' "). If you're looking for a specific agent, try=QUERY(A:C, "Select * where A = 'name goes here' and B = 'Wrap-up' "). developers.google.com/chart/interactive/docs/querylanguage
– s1c0j1
Jan 2 at 7:40
Excel and Google Sheets have not the same features for pivot tables. Which one are you using?
– Rubén
Jan 2 at 20:23
You should look into query. Try something like
=QUERY(A:C, "Select * where B = 'Wrap-up' "). If you're looking for a specific agent, try =QUERY(A:C, "Select * where A = 'name goes here' and B = 'Wrap-up' "). developers.google.com/chart/interactive/docs/querylanguage– s1c0j1
Jan 2 at 7:40
You should look into query. Try something like
=QUERY(A:C, "Select * where B = 'Wrap-up' "). If you're looking for a specific agent, try =QUERY(A:C, "Select * where A = 'name goes here' and B = 'Wrap-up' "). developers.google.com/chart/interactive/docs/querylanguage– s1c0j1
Jan 2 at 7:40
Excel and Google Sheets have not the same features for pivot tables. Which one are you using?
– Rubén
Jan 2 at 20:23
Excel and Google Sheets have not the same features for pivot tables. Which one are you using?
– Rubén
Jan 2 at 20:23
add a comment |
1 Answer
1
active
oldest
votes
For the quick non automated method here is what I would do (Assuming D&E are blank columns)
Formula in column D :
"=IF(B2="Wrap-up",A2,"")
Column E
=IF(B2="Wrap-up",C2,"")
Select column D&E and paste them into a new tab. Remove duplicates (to get rid of most blanks). Then delete the singular blank line left.
Automated version
On one tab have a list of unique Agent Names.
On the next column fill in:
=GETPIVOTDATA("Total",PivotSheetNameHere!$A$1,"Agent Name", A1)
Reference:
https://www.contextures.com/xlPivot06.html
answered Jan 2 at 15:35
Emily AldenEmily Alden
407213
add a comment |
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%2f54000056%2fpivot-table-data-retrieval%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
For the quick non automated method here is what I would do (Assuming D&E are blank columns)
Formula in column D :
"=IF(B2="Wrap-up",A2,"")
Column E
=IF(B2="Wrap-up",C2,"")
Select column D&E and paste them into a new tab. Remove duplicates (to get rid of most blanks). Then delete the singular blank line left.
Automated version
On one tab have a list of unique Agent Names.
On the next column fill in:
=GETPIVOTDATA("Total",PivotSheetNameHere!$A$1,"Agent Name", A1)
Reference:
https://www.contextures.com/xlPivot06.html
answered Jan 2 at 15:35
Emily AldenEmily Alden
407213
add a comment |
For the quick non automated method here is what I would do (Assuming D&E are blank columns)
Formula in column D :
"=IF(B2="Wrap-up",A2,"")
Column E
=IF(B2="Wrap-up",C2,"")
Select column D&E and paste them into a new tab. Remove duplicates (to get rid of most blanks). Then delete the singular blank line left.
Automated version
On one tab have a list of unique Agent Names.
On the next column fill in:
=GETPIVOTDATA("Total",PivotSheetNameHere!$A$1,"Agent Name", A1)
Reference:
https://www.contextures.com/xlPivot06.html
answered Jan 2 at 15:35
Emily AldenEmily Alden
407213
add a comment |
For the quick non automated method here is what I would do (Assuming D&E are blank columns)
Formula in column D :
"=IF(B2="Wrap-up",A2,"")
Column E
=IF(B2="Wrap-up",C2,"")
Select column D&E and paste them into a new tab. Remove duplicates (to get rid of most blanks). Then delete the singular blank line left.
Automated version
On one tab have a list of unique Agent Names.
On the next column fill in:
=GETPIVOTDATA("Total",PivotSheetNameHere!$A$1,"Agent Name", A1)
Reference:
https://www.contextures.com/xlPivot06.html
answered Jan 2 at 15:35
Emily AldenEmily Alden
407213
For the quick non automated method here is what I would do (Assuming D&E are blank columns)
Formula in column D :
"=IF(B2="Wrap-up",A2,"")
Column E
=IF(B2="Wrap-up",C2,"")
Select column D&E and paste them into a new tab. Remove duplicates (to get rid of most blanks). Then delete the singular blank line left.
Automated version
On one tab have a list of unique Agent Names.
On the next column fill in:
=GETPIVOTDATA("Total",PivotSheetNameHere!$A$1,"Agent Name", A1)
Reference:
https://www.contextures.com/xlPivot06.html
answered Jan 2 at 15:35
Emily AldenEmily Alden
407213
edited Jan 2 at 15:43
answered Jan 2 at 15:35
Emily AldenEmily Alden
407213
answered Jan 2 at 15:35
Emily AldenEmily Alden
407213
answered Jan 2 at 15:35
Emily AldenEmily Alden
407213
407213
add a comment |
add a comment |
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%2f54000056%2fpivot-table-data-retrieval%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
You should look into query. Try something like
=QUERY(A:C, "Select * where B = 'Wrap-up' "). If you're looking for a specific agent, try=QUERY(A:C, "Select * where A = 'name goes here' and B = 'Wrap-up' "). developers.google.com/chart/interactive/docs/querylanguage– s1c0j1
Jan 2 at 7:40
Excel and Google Sheets have not the same features for pivot tables. Which one are you using?
– Rubén
Jan 2 at 20:23