| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
how do I transpose columns and rows
I have a spreadsheet as follows:
DonorId Date Amount 1 5/4/04 100 1 7/5 04 50 2 1/5/04 200 I'd like to change it to: DonorId Date Amount Date Amount 1 5/4/04 100 7/5/04 50 2 1/5/04 200 3 There are about 3,700 rows and the number of dtes per donor vary. Any ideas oon how I can do this? Thanks, jnix |
|
#2
|
|||
|
|||
|
You could add a column to your table, to count the donation occurrence,
then use a pivot table to summarize the data. For example, in cell D1 enter the heading DonationNum In cell D2, enter the formula: =COUNTIF($A$1:A2,A2) Copy the formula down to the last row of data. Select a cell in the table, and choose DataPivotTable and PivotChart Report Click Next, check the data range, click Next Click the Layout button Drag DonorID to the Row area Drag DonationNum to the column Area Drag Date and Amount to the Data area Click OK, click Finish Drag the Data button to the right, to arrange the data horizontally If necessary, drag the Donation button to the left of the Data button You may have to format the date columns. jnix wrote: I have a spreadsheet as follows: DonorId Date Amount 1 5/4/04 100 1 7/5 04 50 2 1/5/04 200 I'd like to change it to: DonorId Date Amount Date Amount 1 5/4/04 100 7/5/04 50 2 1/5/04 200 3 There are about 3,700 rows and the number of dtes per donor vary. Any ideas oon how I can do this? Thanks, jnix -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#3
|
|||
|
|||
|
Debra:
Thanks. It works pretty good until I get to the pivot table. The table just gives me columns headed by different counts and then has "1" in each column where there was something in the table. What didi I do wrong? Thanks, jnix "Debra Dalgleish" wrote: You could add a column to your table, to count the donation occurrence, then use a pivot table to summarize the data. For example, in cell D1 enter the heading DonationNum In cell D2, enter the formula: =COUNTIF($A$1:A2,A2) Copy the formula down to the last row of data. Select a cell in the table, and choose DataPivotTable and PivotChart Report Click Next, check the data range, click Next Click the Layout button Drag DonorID to the Row area Drag DonationNum to the column Area Drag Date and Amount to the Data area Click OK, click Finish Drag the Data button to the right, to arrange the data horizontally If necessary, drag the Donation button to the left of the Data button You may have to format the date columns. jnix wrote: I have a spreadsheet as follows: DonorId Date Amount 1 5/4/04 100 1 7/5 04 50 2 1/5/04 200 I'd like to change it to: DonorId Date Amount Date Amount 1 5/4/04 100 7/5/04 50 2 1/5/04 200 3 There are about 3,700 rows and the number of dtes per donor vary. Any ideas oon how I can do this? Thanks, jnix -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#4
|
|||
|
|||
|
Debra:
More information. In the Data Area, it says Count of Date and Count of Amount and Thanks, jniix "Debra Dalgleish" wrote: You could add a column to your table, to count the donation occurrence, then use a pivot table to summarize the data. For example, in cell D1 enter the heading DonationNum In cell D2, enter the formula: =COUNTIF($A$1:A2,A2) Copy the formula down to the last row of data. Select a cell in the table, and choose DataPivotTable and PivotChart Report Click Next, check the data range, click Next Click the Layout button Drag DonorID to the Row area Drag DonationNum to the column Area Drag Date and Amount to the Data area Click OK, click Finish Drag the Data button to the right, to arrange the data horizontally If necessary, drag the Donation button to the left of the Data button You may have to format the date columns. jnix wrote: I have a spreadsheet as follows: DonorId Date Amount 1 5/4/04 100 1 7/5 04 50 2 1/5/04 200 I'd like to change it to: DonorId Date Amount Date Amount 1 5/4/04 100 7/5/04 50 2 1/5/04 200 3 There are about 3,700 rows and the number of dtes per donor vary. Any ideas oon how I can do this? Thanks, jnix -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#5
|
|||
|
|||
|
Right-click on one of the Date column headings
Choose Field Settings Choose to Summarize by: Sum Click OK Do the same for the Amt field. jnix wrote: Debra: Thanks. It works pretty good until I get to the pivot table. The table just gives me columns headed by different counts and then has "1" in each column where there was something in the table. What didi I do wrong? Thanks, jnix "Debra Dalgleish" wrote: You could add a column to your table, to count the donation occurrence, then use a pivot table to summarize the data. For example, in cell D1 enter the heading DonationNum In cell D2, enter the formula: =COUNTIF($A$1:A2,A2) Copy the formula down to the last row of data. Select a cell in the table, and choose DataPivotTable and PivotChart Report Click Next, check the data range, click Next Click the Layout button Drag DonorID to the Row area Drag DonationNum to the column Area Drag Date and Amount to the Data area Click OK, click Finish Drag the Data button to the right, to arrange the data horizontally If necessary, drag the Donation button to the left of the Data button You may have to format the date columns. jnix wrote: I have a spreadsheet as follows: DonorId Date Amount 1 5/4/04 100 1 7/5 04 50 2 1/5/04 200 I'd like to change it to: DonorId Date Amount Date Amount 1 5/4/04 100 7/5/04 50 2 1/5/04 200 3 There are about 3,700 rows and the number of dtes per donor vary. Any ideas oon how I can do this? Thanks, jnix -- 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 |
|
#6
|
|||
|
|||
|
Debra:
That works great for the Amount row. How do I get the Date row to show date instead of count? Thanks, jnix "Debra Dalgleish" wrote: Right-click on one of the Date column headings Choose Field Settings Choose to Summarize by: Sum Click OK Do the same for the Amt field. jnix wrote: Debra: Thanks. It works pretty good until I get to the pivot table. The table just gives me columns headed by different counts and then has "1" in each column where there was something in the table. What didi I do wrong? Thanks, jnix "Debra Dalgleish" wrote: You could add a column to your table, to count the donation occurrence, then use a pivot table to summarize the data. For example, in cell D1 enter the heading DonationNum In cell D2, enter the formula: =COUNTIF($A$1:A2,A2) Copy the formula down to the last row of data. Select a cell in the table, and choose DataPivotTable and PivotChart Report Click Next, check the data range, click Next Click the Layout button Drag DonorID to the Row area Drag DonationNum to the column Area Drag Date and Amount to the Data area Click OK, click Finish Drag the Data button to the right, to arrange the data horizontally If necessary, drag the Donation button to the left of the Data button You may have to format the date columns. jnix wrote: I have a spreadsheet as follows: DonorId Date Amount 1 5/4/04 100 1 7/5 04 50 2 1/5/04 200 I'd like to change it to: DonorId Date Amount Date Amount 1 5/4/04 100 7/5/04 50 2 1/5/04 200 3 There are about 3,700 rows and the number of dtes per donor vary. Any ideas oon how I can do this? Thanks, jnix -- 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
|
|||
|
|||
|
You'll have to format the Date column --
Move the pointer to the top of one of the Date columns When you see a thick black arrow, click, to select all the date columns Choose FormatCells On the Number tab, select one of the Date formats jnix wrote: Debra: That works great for the Amount row. How do I get the Date row to show date instead of count? Thanks, jnix "Debra Dalgleish" wrote: Right-click on one of the Date column headings Choose Field Settings Choose to Summarize by: Sum Click OK Do the same for the Amt field. jnix wrote: Debra: Thanks. It works pretty good until I get to the pivot table. The table just gives me columns headed by different counts and then has "1" in each column where there was something in the table. What didi I do wrong? Thanks, jnix "Debra Dalgleish" wrote: You could add a column to your table, to count the donation occurrence, then use a pivot table to summarize the data. For example, in cell D1 enter the heading DonationNum In cell D2, enter the formula: =COUNTIF($A$1:A2,A2) Copy the formula down to the last row of data. Select a cell in the table, and choose DataPivotTable and PivotChart Report Click Next, check the data range, click Next Click the Layout button Drag DonorID to the Row area Drag DonationNum to the column Area Drag Date and Amount to the Data area Click OK, click Finish Drag the Data button to the right, to arrange the data horizontally If necessary, drag the Donation button to the left of the Data button You may have to format the date columns. jnix wrote: I have a spreadsheet as follows: DonorId Date Amount 1 5/4/04 100 1 7/5 04 50 2 1/5/04 200 I'd like to change it to: DonorId Date Amount Date Amount 1 5/4/04 100 7/5/04 50 2 1/5/04 200 3 There are about 3,700 rows and the number of dtes per donor vary. Any ideas oon how I can do this? Thanks, jnix -- 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 |
|
#8
|
|||
|
|||
|
All of the date fields just have a "1" in themn because they're a count
field. Formatting them for date just gives1/1/01. Now what? Thanks, jnix "jnix" wrote: Debra: More information. In the Data Area, it says Count of Date and Count of Amount and Thanks, jniix "Debra Dalgleish" wrote: You could add a column to your table, to count the donation occurrence, then use a pivot table to summarize the data. For example, in cell D1 enter the heading DonationNum In cell D2, enter the formula: =COUNTIF($A$1:A2,A2) Copy the formula down to the last row of data. Select a cell in the table, and choose DataPivotTable and PivotChart Report Click Next, check the data range, click Next Click the Layout button Drag DonorID to the Row area Drag DonationNum to the column Area Drag Date and Amount to the Data area Click OK, click Finish Drag the Data button to the right, to arrange the data horizontally If necessary, drag the Donation button to the left of the Data button You may have to format the date columns. jnix wrote: I have a spreadsheet as follows: DonorId Date Amount 1 5/4/04 100 1 7/5 04 50 2 1/5/04 200 I'd like to change it to: DonorId Date Amount Date Amount 1 5/4/04 100 7/5/04 50 2 1/5/04 200 3 There are about 3,700 rows and the number of dtes per donor vary. Any ideas oon how I can do this? Thanks, jnix -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#9
|
|||
|
|||
|
Try to change the date field in the wizard:
Right-click on a cell in the Pivot Table Choose Wizard Click the Layout button In the Data area, double-click the 'Count of Date' button For Summarize by, choose Sum Click OK, click OK, click Finish jnix wrote: All of the date fields just have a "1" in themn because they're a count field. Formatting them for date just gives1/1/01. Now what? Thanks, jnix "jnix" wrote: Debra: More information. In the Data Area, it says Count of Date and Count of Amount and Thanks, jniix "Debra Dalgleish" wrote: You could add a column to your table, to count the donation occurrence, then use a pivot table to summarize the data. For example, in cell D1 enter the heading DonationNum In cell D2, enter the formula: =COUNTIF($A$1:A2,A2) Copy the formula down to the last row of data. Select a cell in the table, and choose DataPivotTable and PivotChart Report Click Next, check the data range, click Next Click the Layout button Drag DonorID to the Row area Drag DonationNum to the column Area Drag Date and Amount to the Data area Click OK, click Finish Drag the Data button to the right, to arrange the data horizontally If necessary, drag the Donation button to the left of the Data button You may have to format the date columns. jnix wrote: I have a spreadsheet as follows: DonorId Date Amount 1 5/4/04 100 1 7/5 04 50 2 1/5/04 200 I'd like to change it to: DonorId Date Amount Date Amount 1 5/4/04 100 7/5/04 50 2 1/5/04 200 3 There are about 3,700 rows and the number of dtes per donor vary. Any ideas oon how I can do this? Thanks, jnix -- 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
|
|||
|
|||
|
Debra:
That did it. Thanks for all your help. You're fantastic. jnix "Debra Dalgleish" wrote: Try to change the date field in the wizard: Right-click on a cell in the Pivot Table Choose Wizard Click the Layout button In the Data area, double-click the 'Count of Date' button For Summarize by, choose Sum Click OK, click OK, click Finish jnix wrote: All of the date fields just have a "1" in themn because they're a count field. Formatting them for date just gives1/1/01. Now what? Thanks, jnix "jnix" wrote: Debra: More information. In the Data Area, it says Count of Date and Count of Amount and Thanks, jniix "Debra Dalgleish" wrote: You could add a column to your table, to count the donation occurrence, then use a pivot table to summarize the data. For example, in cell D1 enter the heading DonationNum In cell D2, enter the formula: =COUNTIF($A$1:A2,A2) Copy the formula down to the last row of data. Select a cell in the table, and choose DataPivotTable and PivotChart Report Click Next, check the data range, click Next Click the Layout button Drag DonorID to the Row area Drag DonationNum to the column Area Drag Date and Amount to the Data area Click OK, click Finish Drag the Data button to the right, to arrange the data horizontally If necessary, drag the Donation button to the left of the Data button You may have to format the date columns. jnix wrote: I have a spreadsheet as follows: DonorId Date Amount 1 5/4/04 100 1 7/5 04 50 2 1/5/04 200 I'd like to change it to: DonorId Date Amount Date Amount 1 5/4/04 100 7/5/04 50 2 1/5/04 200 3 There are about 3,700 rows and the number of dtes per donor vary. Any ideas oon how I can do this? Thanks, jnix -- 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
|
|||
|
|||
|
You're welcome! Thanks for letting me know that you got it working.
Debra jnix wrote: Debra: That did it. Thanks for all your help. You're fantastic. jnix "Debra Dalgleish" wrote: Try to change the date field in the wizard: Right-click on a cell in the Pivot Table Choose Wizard Click the Layout button In the Data area, double-click the 'Count of Date' button For Summarize by, choose Sum Click OK, click OK, click Finish jnix wrote: All of the date fields just have a "1" in themn because they're a count field. Formatting them for date just gives1/1/01. Now what? Thanks, jnix "jnix" wrote: Debra: More information. In the Data Area, it says Count of Date and Count of Amount and Thanks, jniix "Debra Dalgleish" wrote: You could add a column to your table, to count the donation occurrence, then use a pivot table to summarize the data. For example, in cell D1 enter the heading DonationNum In cell D2, enter the formula: =COUNTIF($A$1:A2,A2) Copy the formula down to the last row of data. Select a cell in the table, and choose DataPivotTable and PivotChart Report Click Next, check the data range, click Next Click the Layout button Drag DonorID to the Row area Drag DonationNum to the column Area Drag Date and Amount to the Data area Click OK, click Finish Drag the Data button to the right, to arrange the data horizontally If necessary, drag the Donation button to the left of the Data button You may have to format the date columns. jnix wrote: I have a spreadsheet as follows: DonorId Date Amount 1 5/4/04 100 1 7/5 04 50 2 1/5/04 200 I'd like to change it to: DonorId Date Amount Date Amount 1 5/4/04 100 7/5/04 50 2 1/5/04 200 3 There are about 3,700 rows and the number of dtes per donor vary. Any ideas oon how I can do this? Thanks, jnix -- 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 | |||
| repeated transpose from rows to columns with unequal groups | Excel Discussion (Misc queries) | |||
| How do you select two cells in different rows and columns with ou. | Excel Discussion (Misc queries) | |||
| Pivot Table Creating New Columns that Subtract Two Existing Columns | Excel Discussion (Misc queries) | |||
| interchange columns with rows | Excel Discussion (Misc queries) | |||
| Making Rows into Columns | Excel Discussion (Misc queries) | |||
Linear Mode
