| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Pivot Table Cycling Through Page Fields Automatically
Hi. I am trying to cycle through a complete set of data in one of the
parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the underlying cashflow change constantly. How could I cycle through the investments and copy the results from the IRR to a separate table? (I can create the copy routine, but have not been successful with the cycling through the pivot table page field). Many thanks! |
|
#2
|
|||
|
|||
|
There's sample code here for printing each item in the page field:
http://www.contextures.com/xlPivot09.html You could adapt that to your workbook. nrehman wrote: Hi. I am trying to cycle through a complete set of data in one of the parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the underlying cashflow change constantly. How could I cycle through the investments and copy the results from the IRR to a separate table? (I can create the copy routine, but have not been successful with the cycling through the pivot table page field). Many thanks! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#3
|
|||
|
|||
|
Debra Dalgleish has some sample code at:
http://contextures.com/xlPivot09.html I bet you could modify it to do what you want. nrehman wrote: Hi. I am trying to cycle through a complete set of data in one of the parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the underlying cashflow change constantly. How could I cycle through the investments and copy the results from the IRR to a separate table? (I can create the copy routine, but have not been successful with the cycling through the pivot table page field). Many thanks! -- Dave Peterson |
|
#4
|
|||
|
|||
|
Thank you! I should be able to adapt this code.
"Debra Dalgleish" wrote: There's sample code here for printing each item in the page field: http://www.contextures.com/xlPivot09.html You could adapt that to your workbook. nrehman wrote: Hi. I am trying to cycle through a complete set of data in one of the parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the underlying cashflow change constantly. How could I cycle through the investments and copy the results from the IRR to a separate table? (I can create the copy routine, but have not been successful with the cycling through the pivot table page field). Many thanks! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#5
|
|||
|
|||
|
This worked great! Thank you.
However, as my sample has now expanded beyond a couple of hundred pivot items. There are now over two thousand unique items and I am faced with the error that effectively says there are too many items for the pivotitems field. (I believe this is a function of the number of unique items by the columns adding up to some number which cannot be exceeded). Is there a work around? I would like to be able to generate the results for all the items at one shot as it speeds the work and precludes human error. Many thanks. "Debra Dalgleish" wrote: There's sample code here for printing each item in the page field: http://www.contextures.com/xlPivot09.html You could adapt that to your workbook. nrehman wrote: Hi. I am trying to cycle through a complete set of data in one of the parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the underlying cashflow change constantly. How could I cycle through the investments and copy the results from the IRR to a separate table? (I can create the copy routine, but have not been successful with the cycling through the pivot table page field). Many thanks! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#6
|
|||
|
|||
|
The page field should allow more than 2000 items. What version of Excel
are you using? nrehman wrote: This worked great! Thank you. However, as my sample has now expanded beyond a couple of hundred pivot items. There are now over two thousand unique items and I am faced with the error that effectively says there are too many items for the pivotitems field. (I believe this is a function of the number of unique items by the columns adding up to some number which cannot be exceeded). Is there a work around? I would like to be able to generate the results for all the items at one shot as it speeds the work and precludes human error. Many thanks. "Debra Dalgleish" wrote: There's sample code here for printing each item in the page field: http://www.contextures.com/xlPivot09.html You could adapt that to your workbook. nrehman wrote: Hi. I am trying to cycle through a complete set of data in one of the parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the underlying cashflow change constantly. How could I cycle through the investments and copy the results from the IRR to a separate table? (I can create the copy routine, but have not been successful with the cycling through the pivot table page field). Many thanks! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#7
|
|||
|
|||
|
Thank you for your reply.
You are absolutely correct. Creating the identical parameters to cause the same "bug", I found that I had created another variable which was crashing the code. Once I removed that, the program ran fine (which is really great)! I will be coming down to another wall soon, which the the maximum number of rows (I have almost 65000 rows by 6 columns of data). Would you have any ideas for how to work around this issue? Is there some way in the vb to code/join two worksheets or range two sets of columns for a pivot table? (If you prefer, I can submit a new item for the forum). Thanks again for your invaluable help. Nadir "Debra Dalgleish" wrote: The page field should allow more than 2000 items. What version of Excel are you using? nrehman wrote: This worked great! Thank you. However, as my sample has now expanded beyond a couple of hundred pivot items. There are now over two thousand unique items and I am faced with the error that effectively says there are too many items for the pivotitems field. (I believe this is a function of the number of unique items by the columns adding up to some number which cannot be exceeded). Is there a work around? I would like to be able to generate the results for all the items at one shot as it speeds the work and precludes human error. Many thanks. "Debra Dalgleish" wrote: There's sample code here for printing each item in the page field: http://www.contextures.com/xlPivot09.html You could adapt that to your workbook. nrehman wrote: Hi. I am trying to cycle through a complete set of data in one of the parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the underlying cashflow change constantly. How could I cycle through the investments and copy the results from the IRR to a separate table? (I can create the copy routine, but have not been successful with the cycling through the pivot table page field). Many thanks! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#8
|
|||
|
|||
|
You could use multiple consolidation ranges, but the results are too
good. There's an example, and a couple of workarounds, he http://www.contextures.com/xlPivot08.html nrehman wrote: Thank you for your reply. You are absolutely correct. Creating the identical parameters to cause the same "bug", I found that I had created another variable which was crashing the code. Once I removed that, the program ran fine (which is really great)! I will be coming down to another wall soon, which the the maximum number of rows (I have almost 65000 rows by 6 columns of data). Would you have any ideas for how to work around this issue? Is there some way in the vb to code/join two worksheets or range two sets of columns for a pivot table? (If you prefer, I can submit a new item for the forum). Thanks again for your invaluable help. Nadir "Debra Dalgleish" wrote: The page field should allow more than 2000 items. What version of Excel are you using? nrehman wrote: This worked great! Thank you. However, as my sample has now expanded beyond a couple of hundred pivot items. There are now over two thousand unique items and I am faced with the error that effectively says there are too many items for the pivotitems field. (I believe this is a function of the number of unique items by the columns adding up to some number which cannot be exceeded). Is there a work around? I would like to be able to generate the results for all the items at one shot as it speeds the work and precludes human error. Many thanks. "Debra Dalgleish" wrote: There's sample code here for printing each item in the page field: http://www.contextures.com/xlPivot09.html You could adapt that to your workbook. nrehman wrote: Hi. I am trying to cycle through a complete set of data in one of the parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the underlying cashflow change constantly. How could I cycle through the investments and copy the results from the IRR to a separate table? (I can create the copy routine, but have not been successful with the cycling through the pivot table page field). Many thanks! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#9
|
|||
|
|||
|
Thanks again.
I created a new database query (the "Get Data" was trying to link to data on a directory on your PC when I followed the instructions). Then created a new SQL based on you SQL. This worked, creating a new pivot table which matched your results. I tried to do the same with some of my data, creating named regions for two identical worksheets (say using Ontario and Alberta as the worksheet names). However, I have the curious result of seeing ONLY ONE set of data (from one named region) when the first column is identical in both of the worksheets with data. When I change the first column in the Ontario to be different from the first column in Alberta, I then see the two sets of data. Do the first columns have to have different data (?). And therefore have to have data that is unique to one worksheet as compared to the other? Is there a step that I have missed in consolidating the data which causes identical data to not be unioned? Thanks! "Debra Dalgleish" wrote: You could use multiple consolidation ranges, but the results are too good. There's an example, and a couple of workarounds, he http://www.contextures.com/xlPivot08.html nrehman wrote: Thank you for your reply. You are absolutely correct. Creating the identical parameters to cause the same "bug", I found that I had created another variable which was crashing the code. Once I removed that, the program ran fine (which is really great)! I will be coming down to another wall soon, which the the maximum number of rows (I have almost 65000 rows by 6 columns of data). Would you have any ideas for how to work around this issue? Is there some way in the vb to code/join two worksheets or range two sets of columns for a pivot table? (If you prefer, I can submit a new item for the forum). Thanks again for your invaluable help. Nadir "Debra Dalgleish" wrote: The page field should allow more than 2000 items. What version of Excel are you using? nrehman wrote: This worked great! Thank you. However, as my sample has now expanded beyond a couple of hundred pivot items. There are now over two thousand unique items and I am faced with the error that effectively says there are too many items for the pivotitems field. (I believe this is a function of the number of unique items by the columns adding up to some number which cannot be exceeded). Is there a work around? I would like to be able to generate the results for all the items at one shot as it speeds the work and precludes human error. Many thanks. "Debra Dalgleish" wrote: There's sample code here for printing each item in the page field: http://www.contextures.com/xlPivot09.html You could adapt that to your workbook. nrehman wrote: Hi. I am trying to cycle through a complete set of data in one of the parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the underlying cashflow change constantly. How could I cycle through the investments and copy the results from the IRR to a separate table? (I can create the copy routine, but have not been successful with the cycling through the pivot table page field). Many thanks! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#10
|
|||
|
|||
|
It should work with identical data, if you use UNION ALL in the SQL
statement. nrehman wrote: Thanks again. I created a new database query (the "Get Data" was trying to link to data on a directory on your PC when I followed the instructions). Then created a new SQL based on you SQL. This worked, creating a new pivot table which matched your results. I tried to do the same with some of my data, creating named regions for two identical worksheets (say using Ontario and Alberta as the worksheet names). However, I have the curious result of seeing ONLY ONE set of data (from one named region) when the first column is identical in both of the worksheets with data. When I change the first column in the Ontario to be different from the first column in Alberta, I then see the two sets of data. Do the first columns have to have different data (?). And therefore have to have data that is unique to one worksheet as compared to the other? Is there a step that I have missed in consolidating the data which causes identical data to not be unioned? Thanks! "Debra Dalgleish" wrote: You could use multiple consolidation ranges, but the results are too good. There's an example, and a couple of workarounds, he http://www.contextures.com/xlPivot08.html nrehman wrote: Thank you for your reply. You are absolutely correct. Creating the identical parameters to cause the same "bug", I found that I had created another variable which was crashing the code. Once I removed that, the program ran fine (which is really great)! I will be coming down to another wall soon, which the the maximum number of rows (I have almost 65000 rows by 6 columns of data). Would you have any ideas for how to work around this issue? Is there some way in the vb to code/join two worksheets or range two sets of columns for a pivot table? (If you prefer, I can submit a new item for the forum). Thanks again for your invaluable help. Nadir "Debra Dalgleish" wrote: The page field should allow more than 2000 items. What version of Excel are you using? nrehman wrote: This worked great! Thank you. However, as my sample has now expanded beyond a couple of hundred pivot items. There are now over two thousand unique items and I am faced with the error that effectively says there are too many items for the pivotitems field. (I believe this is a function of the number of unique items by the columns adding up to some number which cannot be exceeded). Is there a work around? I would like to be able to generate the results for all the items at one shot as it speeds the work and precludes human error. Many thanks. "Debra Dalgleish" wrote: There's sample code here for printing each item in the page field: http://www.contextures.com/xlPivot09.html You could adapt that to your workbook. nrehman wrote: Hi. I am trying to cycle through a complete set of data in one of the parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the underlying cashflow change constantly. How could I cycle through the investments and copy the results from the IRR to a separate table? (I can create the copy routine, but have not been successful with the cycling through the pivot table page field). Many thanks! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#11
|
|||
|
|||
|
Pure genius. It's a pleasure to know someone who has mastery of their art.
A capital thank you! "Debra Dalgleish" wrote: It should work with identical data, if you use UNION ALL in the SQL statement. nrehman wrote: Thanks again. I created a new database query (the "Get Data" was trying to link to data on a directory on your PC when I followed the instructions). Then created a new SQL based on you SQL. This worked, creating a new pivot table which matched your results. I tried to do the same with some of my data, creating named regions for two identical worksheets (say using Ontario and Alberta as the worksheet names). However, I have the curious result of seeing ONLY ONE set of data (from one named region) when the first column is identical in both of the worksheets with data. When I change the first column in the Ontario to be different from the first column in Alberta, I then see the two sets of data. Do the first columns have to have different data (?). And therefore have to have data that is unique to one worksheet as compared to the other? Is there a step that I have missed in consolidating the data which causes identical data to not be unioned? Thanks! "Debra Dalgleish" wrote: You could use multiple consolidation ranges, but the results are too good. There's an example, and a couple of workarounds, he http://www.contextures.com/xlPivot08.html nrehman wrote: Thank you for your reply. You are absolutely correct. Creating the identical parameters to cause the same "bug", I found that I had created another variable which was crashing the code. Once I removed that, the program ran fine (which is really great)! I will be coming down to another wall soon, which the the maximum number of rows (I have almost 65000 rows by 6 columns of data). Would you have any ideas for how to work around this issue? Is there some way in the vb to code/join two worksheets or range two sets of columns for a pivot table? (If you prefer, I can submit a new item for the forum). Thanks again for your invaluable help. Nadir "Debra Dalgleish" wrote: The page field should allow more than 2000 items. What version of Excel are you using? nrehman wrote: This worked great! Thank you. However, as my sample has now expanded beyond a couple of hundred pivot items. There are now over two thousand unique items and I am faced with the error that effectively says there are too many items for the pivotitems field. (I believe this is a function of the number of unique items by the columns adding up to some number which cannot be exceeded). Is there a work around? I would like to be able to generate the results for all the items at one shot as it speeds the work and precludes human error. Many thanks. "Debra Dalgleish" wrote: There's sample code here for printing each item in the page field: http://www.contextures.com/xlPivot09.html You could adapt that to your workbook. nrehman wrote: Hi. I am trying to cycle through a complete set of data in one of the parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the underlying cashflow change constantly. How could I cycle through the investments and copy the results from the IRR to a separate table? (I can create the copy routine, but have not been successful with the cycling through the pivot table page field). Many thanks! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#12
|
|||
|
|||
|
You're welcome! Thanks for letting me know that it worked.
nrehman wrote: Pure genius. It's a pleasure to know someone who has mastery of their art. A capital thank you! "Debra Dalgleish" wrote: It should work with identical data, if you use UNION ALL in the SQL statement. nrehman wrote: Thanks again. I created a new database query (the "Get Data" was trying to link to data on a directory on your PC when I followed the instructions). Then created a new SQL based on you SQL. This worked, creating a new pivot table which matched your results. I tried to do the same with some of my data, creating named regions for two identical worksheets (say using Ontario and Alberta as the worksheet names). However, I have the curious result of seeing ONLY ONE set of data (from one named region) when the first column is identical in both of the worksheets with data. When I change the first column in the Ontario to be different from the first column in Alberta, I then see the two sets of data. Do the first columns have to have different data (?). And therefore have to have data that is unique to one worksheet as compared to the other? Is there a step that I have missed in consolidating the data which causes identical data to not be unioned? Thanks! "Debra Dalgleish" wrote: You could use multiple consolidation ranges, but the results are too good. There's an example, and a couple of workarounds, he http://www.contextures.com/xlPivot08.html nrehman wrote: Thank you for your reply. You are absolutely correct. Creating the identical parameters to cause the same "bug", I found that I had created another variable which was crashing the code. Once I removed that, the program ran fine (which is really great)! I will be coming down to another wall soon, which the the maximum number of rows (I have almost 65000 rows by 6 columns of data). Would you have any ideas for how to work around this issue? Is there some way in the vb to code/join two worksheets or range two sets of columns for a pivot table? (If you prefer, I can submit a new item for the forum). Thanks again for your invaluable help. Nadir "Debra Dalgleish" wrote: The page field should allow more than 2000 items. What version of Excel are you using? nrehman wrote: This worked great! Thank you. However, as my sample has now expanded beyond a couple of hundred pivot items. There are now over two thousand unique items and I am faced with the error that effectively says there are too many items for the pivotitems field. (I believe this is a function of the number of unique items by the columns adding up to some number which cannot be exceeded). Is there a work around? I would like to be able to generate the results for all the items at one shot as it speeds the work and precludes human error. Many thanks. "Debra Dalgleish" wrote: There's sample code here for printing each item in the page field: http://www.contextures.com/xlPivot09.html You could adapt that to your workbook. nrehman wrote: Hi. I am trying to cycle through a complete set of data in one of the parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the underlying cashflow change constantly. How could I cycle through the investments and copy the results from the IRR to a separate table? (I can create the copy routine, but have not been successful with the cycling through the pivot table page field). Many thanks! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#13
|
|||
|
|||
|
Progress is sometimes painful. After trying to decode the routine, I am
trying to add two capabilities to the cycling routine, but have had little success (probably due to lack of both knowledge and correct implementation): 1) Include the "All" results Here I would like to include the "All" results for each of the pagefields. I have tried to modify the pivotitem For Next loops to start from "0", but the code did not like this and crashes. and 2) Exclude pagefields which create parameters for the data to cycle within. Here I have have tried to modify the pagefield counts in multiple places, but have not been successful. The idea was that if I could get the pagefield count to be less than the number of pagefields (and the parameter pagefields were placed below the cycling pagefields), then the parameter pagefields would be ignored. ANY suggestions? Thanks you. "Debra Dalgleish" wrote: You're welcome! Thanks for letting me know that it worked. nrehman wrote: Pure genius. It's a pleasure to know someone who has mastery of their art. A capital thank you! "Debra Dalgleish" wrote: It should work with identical data, if you use UNION ALL in the SQL statement. nrehman wrote: Thanks again. I created a new database query (the "Get Data" was trying to link to data on a directory on your PC when I followed the instructions). Then created a new SQL based on you SQL. This worked, creating a new pivot table which matched your results. I tried to do the same with some of my data, creating named regions for two identical worksheets (say using Ontario and Alberta as the worksheet names). However, I have the curious result of seeing ONLY ONE set of data (from one named region) when the first column is identical in both of the worksheets with data. When I change the first column in the Ontario to be different from the first column in Alberta, I then see the two sets of data. Do the first columns have to have different data (?). And therefore have to have data that is unique to one worksheet as compared to the other? Is there a step that I have missed in consolidating the data which causes identical data to not be unioned? Thanks! "Debra Dalgleish" wrote: You could use multiple consolidation ranges, but the results are too good. There's an example, and a couple of workarounds, he http://www.contextures.com/xlPivot08.html nrehman wrote: Thank you for your reply. You are absolutely correct. Creating the identical parameters to cause the same "bug", I found that I had created another variable which was crashing the code. Once I removed that, the program ran fine (which is really great)! I will be coming down to another wall soon, which the the maximum number of rows (I have almost 65000 rows by 6 columns of data). Would you have any ideas for how to work around this issue? Is there some way in the vb to code/join two worksheets or range two sets of columns for a pivot table? (If you prefer, I can submit a new item for the forum). Thanks again for your invaluable help. Nadir "Debra Dalgleish" wrote: The page field should allow more than 2000 items. What version of Excel are you using? nrehman wrote: This worked great! Thank you. However, as my sample has now expanded beyond a couple of hundred pivot items. There are now over two thousand unique items and I am faced with the error that effectively says there are too many items for the pivotitems field. (I believe this is a function of the number of unique items by the columns adding up to some number which cannot be exceeded). Is there a work around? I would like to be able to generate the results for all the items at one shot as it speeds the work and precludes human error. Many thanks. "Debra Dalgleish" wrote: There's sample code here for printing each item in the page field: http://www.contextures.com/xlPivot09.html You could adapt that to your workbook. nrehman wrote: Hi. I am trying to cycle through a complete set of data in one of the parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the underlying cashflow change constantly. How could I cycle through the investments and copy the results from the IRR to a separate table? (I can create the copy routine, but have not been successful with the cycling through the pivot table page field). Many thanks! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#14
|
|||
|
|||
|
The code in one of my sample files may give you some ideas:
http://www.contextures.com/excelfiles.html Under PivotTables, look for 'Pivot Page Spinner' nrehman wrote: Progress is sometimes painful. After trying to decode the routine, I am trying to add two capabilities to the cycling routine, but have had little success (probably due to lack of both knowledge and correct implementation): 1) Include the "All" results Here I would like to include the "All" results for each of the pagefields. I have tried to modify the pivotitem For Next loops to start from "0", but the code did not like this and crashes. and 2) Exclude pagefields which create parameters for the data to cycle within. Here I have have tried to modify the pagefield counts in multiple places, but have not been successful. The idea was that if I could get the pagefield count to be less than the number of pagefields (and the parameter pagefields were placed below the cycling pagefields), then the parameter pagefields would be ignored. ANY suggestions? Thanks you. "Debra Dalgleish" wrote: You're welcome! Thanks for letting me know that it worked. nrehman wrote: Pure genius. It's a pleasure to know someone who has mastery of their art. A capital thank you! "Debra Dalgleish" wrote: It should work with identical data, if you use UNION ALL in the SQL statement. nrehman wrote: Thanks again. I created a new database query (the "Get Data" was trying to link to data on a directory on your PC when I followed the instructions). Then created a new SQL based on you SQL. This worked, creating a new pivot table which matched your results. I tried to do the same with some of my data, creating named regions for two identical worksheets (say using Ontario and Alberta as the worksheet names). However, I have the curious result of seeing ONLY ONE set of data (from one named region) when the first column is identical in both of the worksheets with data. When I change the first column in the Ontario to be different from the first column in Alberta, I then see the two sets of data. Do the first columns have to have different data (?). And therefore have to have data that is unique to one worksheet as compared to the other? Is there a step that I have missed in consolidating the data which causes identical data to not be unioned? Thanks! "Debra Dalgleish" wrote: You could use multiple consolidation ranges, but the results are too good. There's an example, and a couple of workarounds, he http://www.contextures.com/xlPivot08.html nrehman wrote: Thank you for your reply. You are absolutely correct. Creating the identical parameters to cause the same "bug", I found that I had created another variable which was crashing the code. Once I removed that, the program ran fine (which is really great)! I will be coming down to another wall soon, which the the maximum number of rows (I have almost 65000 rows by 6 columns of data). Would you have any ideas for how to work around this issue? Is there some way in the vb to code/join two worksheets or range two sets of columns for a pivot table? (If you prefer, I can submit a new item for the forum). Thanks again for your invaluable help. Nadir "Debra Dalgleish" wrote: The page field should allow more than 2000 items. What version of Excel are you using? nrehman wrote: This worked great! Thank you. However, as my sample has now expanded beyond a couple of hundred pivot items. There are now over two thousand unique items and I am faced with the error that effectively says there are too many items for the pivotitems field. (I believe this is a function of the number of unique items by the columns adding up to some number which cannot be exceeded). Is there a work around? I would like to be able to generate the results for all the items at one shot as it speeds the work and precludes human error. Many thanks. "Debra Dalgleish" wrote: There's sample code here for printing each item in the page field: http://www.contextures.com/xlPivot09.html You could adapt that to your workbook. nrehman wrote: Hi. I am trying to cycle through a complete set of data in one of the parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the underlying cashflow change constantly. How could I cycle through the investments and copy the results from the IRR to a separate table? (I can create the copy routine, but have not been successful with the cycling through the pivot table page field). Many thanks! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#15
|
|||
|
|||
|
Thanks! I will dig into it.
Regards, "Debra Dalgleish" wrote: The code in one of my sample files may give you some ideas: http://www.contextures.com/excelfiles.html Under PivotTables, look for 'Pivot Page Spinner' nrehman wrote: Progress is sometimes painful. After trying to decode the routine, I am trying to add two capabilities to the cycling routine, but have had little success (probably due to lack of both knowledge and correct implementation): 1) Include the "All" results Here I would like to include the "All" results for each of the pagefields. I have tried to modify the pivotitem For Next loops to start from "0", but the code did not like this and crashes. and 2) Exclude pagefields which create parameters for the data to cycle within. Here I have have tried to modify the pagefield counts in multiple places, but have not been successful. The idea was that if I could get the pagefield count to be less than the number of pagefields (and the parameter pagefields were placed below the cycling pagefields), then the parameter pagefields would be ignored. ANY suggestions? Thanks you. "Debra Dalgleish" wrote: You're welcome! Thanks for letting me know that it worked. nrehman wrote: Pure genius. It's a pleasure to know someone who has mastery of their art. A capital thank you! "Debra Dalgleish" wrote: It should work with identical data, if you use UNION ALL in the SQL statement. nrehman wrote: Thanks again. I created a new database query (the "Get Data" was trying to link to data on a directory on your PC when I followed the instructions). Then created a new SQL based on you SQL. This worked, creating a new pivot table which matched your results. I tried to do the same with some of my data, creating named regions for two identical worksheets (say using Ontario and Alberta as the worksheet names). However, I have the curious result of seeing ONLY ONE set of data (from one named region) when the first column is identical in both of the worksheets with data. When I change the first column in the Ontario to be different from the first column in Alberta, I then see the two sets of data. Do the first columns have to have different data (?). And therefore have to have data that is unique to one worksheet as compared to the other? Is there a step that I have missed in consolidating the data which causes identical data to not be unioned? Thanks! "Debra Dalgleish" wrote: You could use multiple consolidation ranges, but the results are too good. There's an example, and a couple of workarounds, he http://www.contextures.com/xlPivot08.html nrehman wrote: Thank you for your reply. You are absolutely correct. Creating the identical parameters to cause the same "bug", I found that I had created another variable which was crashing the code. Once I removed that, the program ran fine (which is really great)! I will be coming down to another wall soon, which the the maximum number of rows (I have almost 65000 rows by 6 columns of data). Would you have any ideas for how to work around this issue? Is there some way in the vb to code/join two worksheets or range two sets of columns for a pivot table? (If you prefer, I can submit a new item for the forum). Thanks again for your invaluable help. Nadir "Debra Dalgleish" wrote: The page field should allow more than 2000 items. What version of Excel are you using? nrehman wrote: This worked great! Thank you. However, as my sample has now expanded beyond a couple of hundred pivot items. There are now over two thousand unique items and I am faced with the error that effectively says there are too many items for the pivotitems field. (I believe this is a function of the number of unique items by the columns adding up to some number which cannot be exceeded). Is there a work around? I would like to be able to generate the results for all the items at one shot as it speeds the work and precludes human error. Many thanks. "Debra Dalgleish" wrote: There's sample code here for printing each item in the page field: http://www.contextures.com/xlPivot09.html You could adapt that to your workbook. nrehman wrote: Hi. I am trying to cycle through a complete set of data in one of the parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the underlying cashflow change constantly. How could I cycle through the investments and copy the results from the IRR to a separate table? (I can create the copy routine, but have not been successful with the cycling through the pivot table page field). Many thanks! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pivot table page field switch to (all) if my criteria is not avail | Excel Discussion (Misc queries) | |||
| Pivot Table page fields | Excel Discussion (Misc queries) | |||
| How to remove Drop Page Fields Here from Pivot Table | Excel Discussion (Misc queries) | |||
| How do I set up filter for page fields in pivot table? | Excel Discussion (Misc queries) | |||
| Pivot Table Page Fields | Excel Discussion (Misc queries) | |||
Linear Mode
