Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort data
Each month I have to download a spreadsheet containing many columns of
information. When I save it, I normally do so as an excel document, then i spend time sorting it out for analysis. This is time consuming and i'm looking for a way of downloading into a spread sheet that automatically sorts it the way I want it to and deletes unwanted columns and then totals a particular column. The information is sorted as follows. The spreadsheet comes up with a random list containing information on 3 companies. I want it to sort into company order so, Company 1 Company3 Company3 Company 2 Compamy 1 Company 2 Becomes Company 1 Company 1 Company 2 Company 2 Company 3 Company 3 The only reason I sort it is since it makes it easier to total the figures for each company. It does not matter what this spreadsheet looks like so a simpler way could be for Excel to detect a particular company and total the values corresponding to the particular company and automatically imput them into a seperate box. Can anyone help? Kind Regards Barry |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort data
Barry,
Don't sort. In fact, don't do anything to your input data. Simply use a Pivot table based on your database, and use the Pivot table can create the totals that you are interested in. HTH, Bernie MS Excel MVP "Barry Walker" wrote in message ... Each month I have to download a spreadsheet containing many columns of information. When I save it, I normally do so as an excel document, then i spend time sorting it out for analysis. This is time consuming and i'm looking for a way of downloading into a spread sheet that automatically sorts it the way I want it to and deletes unwanted columns and then totals a particular column. The information is sorted as follows. The spreadsheet comes up with a random list containing information on 3 companies. I want it to sort into company order so, Company 1 Company3 Company3 Company 2 Compamy 1 Company 2 Becomes Company 1 Company 1 Company 2 Company 2 Company 3 Company 3 The only reason I sort it is since it makes it easier to total the figures for each company. It does not matter what this spreadsheet looks like so a simpler way could be for Excel to detect a particular company and total the values corresponding to the particular company and automatically imput them into a seperate box. Can anyone help? Kind Regards Barry |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort data
Hey Bernie,
Thanks for the reply. I don't know what a pivot table is or what it does. Also will it work directly from a download or only through Excel to another spread sheet? Kind Regards Barry "Bernie Deitrick" wrote: Barry, Don't sort. In fact, don't do anything to your input data. Simply use a Pivot table based on your database, and use the Pivot table can create the totals that you are interested in. HTH, Bernie MS Excel MVP "Barry Walker" wrote in message ... Each month I have to download a spreadsheet containing many columns of information. When I save it, I normally do so as an excel document, then i spend time sorting it out for analysis. This is time consuming and i'm looking for a way of downloading into a spread sheet that automatically sorts it the way I want it to and deletes unwanted columns and then totals a particular column. The information is sorted as follows. The spreadsheet comes up with a random list containing information on 3 companies. I want it to sort into company order so, Company 1 Company3 Company3 Company 2 Compamy 1 Company 2 Becomes Company 1 Company 1 Company 2 Company 2 Company 3 Company 3 The only reason I sort it is since it makes it easier to total the figures for each company. It does not matter what this spreadsheet looks like so a simpler way could be for Excel to detect a particular company and total the values corresponding to the particular company and automatically imput them into a seperate box. Can anyone help? Kind Regards Barry |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort data
Here's another way you can try, Barry:
Click on any value in the Company column and Sort Ascending (A--Z) Then go to Data Menu -- Subtotals. Indicate at each change in company, you wish use a specified function on a selected column of data. "Barry Walker" wrote: Each month I have to download a spreadsheet containing many columns of information. When I save it, I normally do so as an excel document, then i spend time sorting it out for analysis. This is time consuming and i'm looking for a way of downloading into a spread sheet that automatically sorts it the way I want it to and deletes unwanted columns and then totals a particular column. The information is sorted as follows. The spreadsheet comes up with a random list containing information on 3 companies. I want it to sort into company order so, Company 1 Company3 Company3 Company 2 Compamy 1 Company 2 Becomes Company 1 Company 1 Company 2 Company 2 Company 3 Company 3 The only reason I sort it is since it makes it easier to total the figures for each company. It does not matter what this spreadsheet looks like so a simpler way could be for Excel to detect a particular company and total the values corresponding to the particular company and automatically imput them into a seperate box. Can anyone help? Kind Regards Barry |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort data
Thats what I used to do anyway, but i just wanted to make it simpler that
that so that as soon as it was downloaded and moved into the new ex cel spread sheet it would automatically do all of those things without me having to. Thanks though "SyrNO" wrote: Here's another way you can try, Barry: Click on any value in the Company column and Sort Ascending (A--Z) Then go to Data Menu -- Subtotals. Indicate at each change in company, you wish use a specified function on a selected column of data. "Barry Walker" wrote: Each month I have to download a spreadsheet containing many columns of information. When I save it, I normally do so as an excel document, then i spend time sorting it out for analysis. This is time consuming and i'm looking for a way of downloading into a spread sheet that automatically sorts it the way I want it to and deletes unwanted columns and then totals a particular column. The information is sorted as follows. The spreadsheet comes up with a random list containing information on 3 companies. I want it to sort into company order so, Company 1 Company3 Company3 Company 2 Compamy 1 Company 2 Becomes Company 1 Company 1 Company 2 Company 2 Company 3 Company 3 The only reason I sort it is since it makes it easier to total the figures for each company. It does not matter what this spreadsheet looks like so a simpler way could be for Excel to detect a particular company and total the values corresponding to the particular company and automatically imput them into a seperate box. Can anyone help? Kind Regards Barry |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort data
Turn on the macro recorder while you do it manually. then turn off the macro
recorder when you are done. That should give you the basic code you need. -- Regards, Tom Ogilvy "Barry Walker" wrote: Thats what I used to do anyway, but i just wanted to make it simpler that that so that as soon as it was downloaded and moved into the new ex cel spread sheet it would automatically do all of those things without me having to. Thanks though "SyrNO" wrote: Here's another way you can try, Barry: Click on any value in the Company column and Sort Ascending (A--Z) Then go to Data Menu -- Subtotals. Indicate at each change in company, you wish use a specified function on a selected column of data. "Barry Walker" wrote: Each month I have to download a spreadsheet containing many columns of information. When I save it, I normally do so as an excel document, then i spend time sorting it out for analysis. This is time consuming and i'm looking for a way of downloading into a spread sheet that automatically sorts it the way I want it to and deletes unwanted columns and then totals a particular column. The information is sorted as follows. The spreadsheet comes up with a random list containing information on 3 companies. I want it to sort into company order so, Company 1 Company3 Company3 Company 2 Compamy 1 Company 2 Becomes Company 1 Company 1 Company 2 Company 2 Company 3 Company 3 The only reason I sort it is since it makes it easier to total the figures for each company. It does not matter what this spreadsheet looks like so a simpler way could be for Excel to detect a particular company and total the values corresponding to the particular company and automatically imput them into a seperate box. Can anyone help? Kind Regards Barry |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort data
Yeah I have tried to record macros before but how do i access the codes after
i have finished with it? Barry "Tom Ogilvy" wrote: Turn on the macro recorder while you do it manually. then turn off the macro recorder when you are done. That should give you the basic code you need. -- Regards, Tom Ogilvy "Barry Walker" wrote: Thats what I used to do anyway, but i just wanted to make it simpler that that so that as soon as it was downloaded and moved into the new ex cel spread sheet it would automatically do all of those things without me having to. Thanks though "SyrNO" wrote: Here's another way you can try, Barry: Click on any value in the Company column and Sort Ascending (A--Z) Then go to Data Menu -- Subtotals. Indicate at each change in company, you wish use a specified function on a selected column of data. "Barry Walker" wrote: Each month I have to download a spreadsheet containing many columns of information. When I save it, I normally do so as an excel document, then i spend time sorting it out for analysis. This is time consuming and i'm looking for a way of downloading into a spread sheet that automatically sorts it the way I want it to and deletes unwanted columns and then totals a particular column. The information is sorted as follows. The spreadsheet comes up with a random list containing information on 3 companies. I want it to sort into company order so, Company 1 Company3 Company3 Company 2 Compamy 1 Company 2 Becomes Company 1 Company 1 Company 2 Company 2 Company 3 Company 3 The only reason I sort it is since it makes it easier to total the figures for each company. It does not matter what this spreadsheet looks like so a simpler way could be for Excel to detect a particular company and total the values corresponding to the particular company and automatically imput them into a seperate box. Can anyone help? Kind Regards Barry |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort data
After you have clicked on "Tools/Macro/Stop Recording" click on
"Tools/Macro/Macros" You should see the Macro# you just recorded along with any others that reside in whatever workbooks you have open. You can click and choose "This Workbook" to make the field easier to see. Highlight the one in question and click on "edit". If the source data is always on the same columns you can use the macro as it stands. Otherwise you will need to do some editing. Good luck, Jim "Barry Walker" wrote: Yeah I have tried to record macros before but how do i access the codes after i have finished with it? Barry "Tom Ogilvy" wrote: Turn on the macro recorder while you do it manually. then turn off the macro recorder when you are done. That should give you the basic code you need. -- Regards, Tom Ogilvy "Barry Walker" wrote: Thats what I used to do anyway, but i just wanted to make it simpler that that so that as soon as it was downloaded and moved into the new ex cel spread sheet it would automatically do all of those things without me having to. Thanks though "SyrNO" wrote: Here's another way you can try, Barry: Click on any value in the Company column and Sort Ascending (A--Z) Then go to Data Menu -- Subtotals. Indicate at each change in company, you wish use a specified function on a selected column of data. "Barry Walker" wrote: Each month I have to download a spreadsheet containing many columns of information. When I save it, I normally do so as an excel document, then i spend time sorting it out for analysis. This is time consuming and i'm looking for a way of downloading into a spread sheet that automatically sorts it the way I want it to and deletes unwanted columns and then totals a particular column. The information is sorted as follows. The spreadsheet comes up with a random list containing information on 3 companies. I want it to sort into company order so, Company 1 Company3 Company3 Company 2 Compamy 1 Company 2 Becomes Company 1 Company 1 Company 2 Company 2 Company 3 Company 3 The only reason I sort it is since it makes it easier to total the figures for each company. It does not matter what this spreadsheet looks like so a simpler way could be for Excel to detect a particular company and total the values corresponding to the particular company and automatically imput them into a seperate box. Can anyone help? Kind Regards Barry |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort data
in Excel to Alt+F11
to get to the VBE. -- Regards, Tom Ogilvy "Barry Walker" wrote: Yeah I have tried to record macros before but how do i access the codes after i have finished with it? Barry "Tom Ogilvy" wrote: Turn on the macro recorder while you do it manually. then turn off the macro recorder when you are done. That should give you the basic code you need. -- Regards, Tom Ogilvy "Barry Walker" wrote: Thats what I used to do anyway, but i just wanted to make it simpler that that so that as soon as it was downloaded and moved into the new ex cel spread sheet it would automatically do all of those things without me having to. Thanks though "SyrNO" wrote: Here's another way you can try, Barry: Click on any value in the Company column and Sort Ascending (A--Z) Then go to Data Menu -- Subtotals. Indicate at each change in company, you wish use a specified function on a selected column of data. "Barry Walker" wrote: Each month I have to download a spreadsheet containing many columns of information. When I save it, I normally do so as an excel document, then i spend time sorting it out for analysis. This is time consuming and i'm looking for a way of downloading into a spread sheet that automatically sorts it the way I want it to and deletes unwanted columns and then totals a particular column. The information is sorted as follows. The spreadsheet comes up with a random list containing information on 3 companies. I want it to sort into company order so, Company 1 Company3 Company3 Company 2 Compamy 1 Company 2 Becomes Company 1 Company 1 Company 2 Company 2 Company 3 Company 3 The only reason I sort it is since it makes it easier to total the figures for each company. It does not matter what this spreadsheet looks like so a simpler way could be for Excel to detect a particular company and total the values corresponding to the particular company and automatically imput them into a seperate box. Can anyone help? Kind Regards Barry |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort data
Barry, see if this code from Bob Philips is of help. It was to me
Sub Test() Dim iLastRow As Long Dim i As Long Dim tmpSub As Double, tmpTotal As Double iLastRow = Cells(Rows.Count, "A").End(xlUp).Row tmpSub = Range("A1").Value For i = 2 To iLastRow + 1 If Cells(i, "A") < "" Then tmpSub = tmpSub + Cells(i, "A").Value Else Cells(i, "A").Value = tmpSub tmpTotal = tmpTotal + tmpSub tmpSub = 0 End If Next i Cells(i, "A").Value = tmpTotal End Sub -- HTH Bob Phillips -- Robert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data, sort option is grayed. how to sort on a column? | Excel Discussion (Misc queries) | |||
Pls. reply Sort Data and copy to next coulmn when sort order chang | Excel Programming | |||
Sort Data and copy to next coulmn when sort data changes | Excel Programming | |||
Sort Data and copy to next coulmn when sort data changes | Excel Programming | |||
How do I sort a column of data and have each data row sort accordi | Excel Discussion (Misc queries) |