how do I transpose columns and rows

G

Guest

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
 
D

Debra Dalgleish

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
Data>PivotTable 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.
 
G

Guest

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
 
G

Guest

Debra:
More information. In the Data Area, it says Count of Date and Count of
Amount and
Thanks,
jniix
 
D

Debra Dalgleish

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.
 
G

Guest

Debra:

That works great for the Amount row. How do I get the Date row to show date
instead of count?
Thanks,
jnix
 
D

Debra Dalgleish

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 Format>Cells
 
G

Guest

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
 
D

Debra Dalgleish

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top