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
 
0
jnix (8)
12/21/2004 8:03:02 PM
excel.misc 78881 articles. 5 followers. Follow

10 Replies
478 Views

Similar Articles

[PageSpeed] 24

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.


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

0
dsd1 (5911)
12/21/2004 8:17:59 PM
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
>       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.
> 
> 
> 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
> 
> 
0
jnix (8)
12/21/2004 10:19:02 PM
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
>       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.
> 
> 
> 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
> 
> 
0
jnix (8)
12/21/2004 10:23:12 PM
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
>>      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.
>>
>>
>>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

0
dsd1 (5911)
12/21/2004 10:25:31 PM
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
> >>      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.
> >>
> >>
> >>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
> 
> 
0
jnix (8)
12/22/2004 1:41:02 AM
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
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
>>>>     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.
>>>>
>>>>
>>>>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

0
dsd1 (5911)
12/22/2004 2:03:52 AM
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
> >       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.
> > 
> > 
> > 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
> > 
> > 
0
jnix (8)
12/22/2004 2:37:09 AM
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
>>>      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.
>>>
>>>
>>>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

0
dsd1 (5911)
12/22/2004 3:13:38 AM
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
> >>>      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.
> >>>
> >>>
> >>>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
> 
> 
0
jnix (8)
12/22/2004 1:25:04 PM
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
>>>>>     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.
>>>>>
>>>>>
>>>>>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

0
dsd1 (5911)
12/22/2004 1:44:54 PM
Reply:

Similar Artilces:

angled or sloping column headings in Excel
How do I create angled or sloping headings in Excel Select your cells. Format|Cells|Alignment tab|Play around with the orientation area. PhilipRichards wrote: > > How do I create angled or sloping headings in Excel -- Dave Peterson Select the cells you typed the headings into. Then, Format, Cells, Alignment. Slant as desired using the Orientation area. tj "PhilipRichards" wrote: > How do I create angled or sloping headings in Excel Select the cells; use Format|Cells and open Alignment tab At right-hand side: either drag the line on the gauge or set the angle by ...

tricky problem with 14800 rows (collating figures)
Hi, I'm trying to sort through a very large amount of data, and to get averages from it. (14800 ows in the full version !!). What I need to do is 1) Find out how many times each DELAY (column B1) appears for each AIRLINE (A1) and give a total for each airlines (This is to show which delay affects individual airlines the most) 2) Add up the CPU time spent on delays for each airline 3) Collate the number of ABENDs-periairline, and CPU time-per-airlines in to a grand total I need hedlp with this one as it is totally out of my depth. A1 B1 C1 AIRLINE DEL...

transposing info.
I want to transfer information from one spreadsheet to another with different titles on the columns. How do I do this? I want to direct the values on the column which has a different title column to the sheet that I want to move it to. Thanks guideme wrote: > I want to transfer information from one spreadsheet to another with > different titles on the columns. How do I do this? > > I want to direct the values on the column which has a different title > column to the sheet that I want to move it to. > > Thanks This is more of moving data to another sheet which has di...

How do I merge 3 lines of text to show in one row?
I am doing a family history excel document. One row should have approx. 3 lines of info, however, I can only get one row of info to show. When I dclick that row the info shows but when I print or do a print preview only one line of info shows. I have merged the cells in that row. It sounds like you need to select Wrap Text from the Alignment tab of the Format Cells dialog and/or increase the height of the row to accommodate the numbers of rows of text in the cell. -- Ian -- "pita29" <pita29@discussions.microsoft.com> wrote in message news:E3D24CC3-7B38-4461-9B29-6...

text from one column into multiple columns
Hi, How can I take text entered like this: Mike Smith Toyota 123 Main St Anytown, TX 12345 phone 713 222 1212 fax 713 222 2121 ....and move it to where every address element (name, street, city, etc.) is in its own column? Text to Columns obviously won't work, and I've had no luck trying to make a macro to do it. Thanks, Jeff Hi do you always have the same amount of rows per record? -- Regards Frank Kabel Frankfurt, Germany Jeff Brown wrote: > Hi, > How can I take text entered like this: > Mike Smith Toyota > 123 Main St > Anytown, TX 12345 > phone 713 222 12...

A simple macro to make every 3rd row a specific background color???
How would I make a simple macro script that would make every third row color of my choosing, as in rows 3, 6, 9, etc. Is there an easy way to do such a simple task -- Message posted from http://www.ExcelForum.com You don't need a macro, and if you used one you would lose the integrity of the colouring anytime you sorted the data, or deleted cells etc. Use a conditional format to do this and it will not move when you sort the rows:- Do CTRL+A, then Format / Conditional Formatting / Change 'cell value is' to 'formula is' and put in =MOD(ROW(),2)=1 Now click on the for...

Excel Columns Problem
Hello Fellow Excel users, I have alittle problem which i hope somebody can point me in the right direction Picture my screen in excel Col A is a list of Names with Multiple entries for the same person Col B is a number ranging from 0 to 7 eg A B Joe Bloggs 1 Jim Smith 2 Joe Bloggs 4 Pete Smith 5 Jim Smith 4 What i want to create is another list where Col A is a list of names Just 1 entry per name Col B the sum of all entries for that persons name e...

Display a list of unique values from a column with multiple instances
Hi- I have a list in a column (column A) that has many duplicate values. I am looking to create a list of the unique values from column A into another column (column F). I just want to display the values from col A into col F without deleting any values from col A. From there I am planning on doing a countif based on the value in col F to see how many duplicates I have. I want to do this dynamically because anyone can add a new value in col A but I don't want to have to manually compare unique values in col A against col F to do the counts. Col A contains ID numbers that can be repeat...

Selecting highlighted data from one column and placing them in another column
Hi, I have data in Column A. Some of the rows in Column A are highlighted in yellow. I would like to copy the highlighted data and paste them into Column B. For Example: Column A 2342 1234 2121 5675 2321 Let's assume that 2342 and 2121 are highlighted. So my resullt in Column B will be: 2342 2121 Keep in mind that I have n numbers of data in Column A. Try this Macro: Sub Colour() Dim bCol As Long Dim x As Long bCol = 2 For x = 2 To 1000 'increase as required If Cells(x, 1).Interior.ColorIndex > 0 Then Cells(bCol, 2) = Cells(x, 1).Va...

Check Name Dialogue
Does anyone know whether it is to change the order of the column heading in the "check names" dialogue box. Specifically to place the E-mail Type field first (in order to check whether the address is an e-mail or fax #). That interface is not configurable. -- Russ Valentine [MVP-Outlook] "KEllis" <anonymous@discussions.microsoft.com> wrote in message news:08c301c3b5be$ef885990$a401280a@phx.gbl... > Does anyone know whether it is to change the order of the > column heading in the "check names" dialogue box. > Specifically to place the E-mail Typ...

How to add a 1 to existing column of phone numbers?
Trying to get a 1 to prefix phone numbers in an existing column with thousands of entries. thanks! hi jan, surf to www.asap-utilities.com and download the wonderful free utility featured there. your problem will be answered with this utility yves "Jan" wrote: > Trying to get a 1 to prefix phone numbers in an existing column with > thousands of entries. > > thanks! > What exactly do you presently have in the list now? If, for example, you had: (213) 123-4567 You could use "Edit & Replace", where you "Find" " ( " (no ...

Transposing list of numbers
I'm using Excel 2003. I have a list of 400 numbers in Column A, I would like to transpose it across 7 columns and 58 rows. Is there an easy way to do this? I understand I can do the copy/paste special/transpose for 7 at a time. Thank you. -- jlhcat Sub ColtoRows() Dim rng As Range Dim i As Long Dim J As Long Set rng = Cells(Rows.Count, 1).End(xlUp) J = 1 On Error Resume Next nocols = 7 'InputBox("Enter Number of Columns Desired") For i = 1 To rng.Row Step nocols Cells(J, "A").Resize(1, nocols).Value = _ ...

Transpose question
I have a MASTER summary worksheet with this layout 07-Sep 14-Sep 21-Sep 28-Sep 05 - Oct Total No of late deliveries 36 40 20 10 9 115 No of Drivers 18 32 11 11 4 76 getting its info from WEEKLY worksheets eg 03-Sep 04-Sep 05-Sep 06-Sep 07-Sep Total No of late deliveries 7 8 7 7 7 36 No of Drivers 4 3 1 6 4 18 I would like the master layout to be like : No of late deliveries No of Drivers 07-Sep 36 18 14-Sep 40 32 21-Sep 20 11 28-Sep 10 11 05-Oct 9 4 Total 115 76 But as the values in the MASTER worksheet ...

Macro to Combine 2 columns to make one column without merging
I found this macro courtesy of Dave: Option Explicit Sub testme02() Dim myRng As Range Dim myArea As Range Dim myRow As Range Dim myCell As Range Dim myStr As String Set myRng = Selection Application.DisplayAlerts = False For Each myArea In myRng.Areas If myArea.Columns.Count > 1 Then For Each myRow In myArea.Rows myStr = "" For Each myCell In myRow.Cells myStr = myStr & " " & myCell.Text Next myCell myRow.Merge acros...

Go to the last-new row in a datasheet view
What is the code to go to the last row where is added a new record when you type something? I tryed adding a record to the form recordset but I don't want to leave that record empty, I just want it to be ready to load data into. Greetings from Paraguay. Claudio Bogado Pompa. Use the GotoRecord method. It is explained in VBA Help. If you are using a macro, then it is the GotoRecord action, also in VBA Help. -- Dave Hargis, Microsoft Access MVP "Claudio Bogado Pompa" wrote: > What is the code to go to the last row where is added a new record > when you type something...

How to set Upper case in a column
I have a spread sheet set up in a MS Excel 2002. I know there is a formula =UPPER. I can use it for any individual cell. But I'd like to use this formula in a few columns, so the text in each cell of these columns will be in UPPER case. May someone please tell me how to use formula within a column? Step-by-step instructions will be appreciated. -- Ilya Zeldes Fort Myers, Florida I do NOT recommend you do this but if you insist, Right click sheet tab>view code>copy/paste this>change column K to suit>SAVE workbook. Now anything you type into that column will be change...

TSQL Transpose
Hi All! I'm trying to create a query that does a real transpose similar to Excel function - it is slightly different from Pivot/Unpivot functionality of SQL Server. Basically, I have a date dimension table and I need to list corresponding dates, separated by coma for each month. So, for January 2010, my results would be something like this: Month Dates 2010-01 01/01/2010, 01/02/2010, 01/03/2010...01/31/2010 What would be a way to achieve this? Thanks, Pasha Seems you just need to concatenate the date. Here is method using FOR XML PATH: SELECT DISTI...

how do i combine columns in excel
I need to combine two fields (first name) and (last name) to make one feild name full name =a1&" "&b1 or maybe =a1&", "&b1 psamiam wrote: > > I need to combine two fields (first name) and (last name) to make one feild > name full name -- Dave Peterson use the concatenate function... =concatenate(A1,b1) or =A1&b1 >-----Original Message----- >I need to combine two fields (first name) and (last name) to make one feild >name full name >. > ...

Multiple columns into 0ne
Hi, We get a lot of information that comes into different columns, say Product 1 - 88. We never know how many values there are in every colum or how many Products there are Now we want a macro that puts all the values into one column, say A - can be in sheet 2. We do not want empty rows or columns but it is very important that the figures are in the original order. Regards More information is needed. Is there a header row? If so, what do you want to become of it? Might the be empty cells within the table? How do you recognize how many columns are used, or is the first row alw...

transpose #2
How do I do a transpose in excel 2002? Thanks. Highlight your range > Right click Copy > select a blank cell > Right click Paste Special > Transpose > OK "abc" wrote: > How do I do a transpose in excel 2002? Thanks. > > > ...

Problem with formula when inserting a new column
Hi I have a spreadsheet similar to the below which is updated weekly A B C D NAME WEEKLY SCORE WEEKLY SCORE MEETING HELD John Smith 250 230 01/11/2009 Jane Smith 105 10 07/12/2009 I insert a new column at the beginning of every week to enter in the new score so we have a continual track. I want to create seperate spreadsheets for the team managers for their employees which will update...

Copying one column to another column, but retaining values
Suppose that I have many rows of data. Row A is a function of Row B. Let's say that the cells of cell A12=B12*2+4. Now suppose that I want to copy the column A (control C - not Control X), and paste it to Column Z. I don't want the pasted values to equal AA*2+4. How do I copy/paste cells with formulas without losing my original values? The way I know is a bit involved, but works well. Highlight the cells in column A that you want to move to column z. Don't highlight the whole column. Copy Move the cursor to an empty area below these cells in column A Paste With the same selec...

Columns limit
In Excel 2003 I'm bumping up against a limit at Column IV, or 204 columns in a yearly stock pricing spreadsheet. I need one more column to do year-end sums. First, is there any way to get more columns? Second, why 204? I'm only doing entries for stock market trading days, so 204 is almost enough. But what if you want to record 365 daily entries? (Yeah, you can do it vertically but that's not the optimal way to display data like this.) TIA for any help. xl2003 and below have 256 columns. xl2007 has 16k columns. There's nothing you can do to xl2003...

data series in rows or columns.
I am having problems in a class here which the teacher can't seem to help with. I was making a bar graph, and the A column had East, West, North, and South. The one row had 1st Qtr, 2nd Qtr, 3rd Qtr, 4th Qtr. The bars represent the data. In Chart Wizard, Step 2 of 4, chart source data, there are two check boxes marked, "series in:" and the boxes are marked "rows" and "columns". Here's where I get lost. Which way do you read the data? Hi Mike "rows" uses the headings across the row as the labels for the X axis and graphs the data acc...

more than 10000 rows in combobox
how can i display more than 10000 rows in a combo box ? I tried changing the MaxRecords (tools -> options -> max records) and set it to 15000 - didn't help. i also tried opening a recordset in this way: dim rs as new adodb.recordset rs.open "select ....", CurrentProject.Connection, adOpenDynamic, adLockReadOnly cmb.recordset = rs gave me an error... any ideas ? This site has your answer: http://allenbrowne.com/ser-32.html -- Dave Hargis, Microsoft Access MVP "Daniel" wrote: > how can i display more than 10000 rows in a combo box ? > I tried changi...