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

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

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

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

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

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

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

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

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

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