Omega Owners Forum

Please login or register.

Login with username, password and session length
Advanced search  

News:

Welcome to OOF

Pages: [1]   Go Down

Author Topic: Office Help - Excel Sort Criteria  (Read 1502 times)

0 Members and 1 Guest are viewing this topic.

Mr Skrunts

  • Get A Life!!
  • *****
  • Offline Offline
  • Gender: Male
  • Skruntie Land.
  • Posts: 25692
  • 3.O Elite Saloon with all the toys,
    • 2003 CD 2.2 Auto
    • View Profile
Office Help - Excel Sort Criteria
« on: 06 January 2011, 14:06:44 »
[size=9]22-Nov-10      POS      2010 19NOV10 , TESCO GARAGE 4041 ,     GB      -       £5.00      £416.60
22-Nov-10      POS      5111 20NOV10 , ORANGE (A/PG/01) , DARLINGTON GB      -       £5.00      £411.60
22-Nov-10      POS      5111 20NOV10 , TESCO STORES SACAT,     GB      -       £4.14      £407.46
22-Nov-10      POS      5111 20NOV10 , BARGAIN BOOZE , SHEFFIELD GB      -       £13.07      £394.39
22-Nov-10      POS      5111 20NOV10 , VODAFONE , BANBURY GB              -       £30.00      £364.39
22-Nov-10      POS      5111 20NOV10 , TESCO GARAGE 4041 ,     ON GB      -       £10.04      £354.35[/size]

The above is just a a small selection copied from online bank statements to an excel spread sheat.

I know how to sort the data by highlighting the couloums, clicking on the key coloumn to sort etc.  But all the description coun starts with a 4 digit ref, in this case mainly 5111 or the date, or ref code then date.

My question is, can I sort by a key word in that coloum to bring all the Tesco garage, or Tesco store into a group so that I can then extract all those lines and cut/insert rows into other sprad sheets.

If that cant be done, I am then assuming I would be better using maybe Access Database and then I can sort by key words.

How easy it it to convert a spread sheet in to a Database.

TIA 
Logged
Ask yourself :  " WHY do I believe in what I believe?"

Remember that my opinions expressed here are not representative of the opinions of other members on the OOF Forum.

Marks DTM Calib

  • Administrator
  • *****
  • Offline Offline
  • Gender: Male
  • West Bridgford
  • Posts: 34037
  • Git!
    • View Profile
Re: Office Help - Excel Sort Criteria
« Reply #1 on: 06 January 2011, 14:35:28 »
Yes, easy

Highlight the data, click on sort (under Data tab),

Under the column list select

Column - X (or which ever column has the text in it)
Sort On - Values
Order - A to Z
Logged

Mr Skrunts

  • Get A Life!!
  • *****
  • Offline Offline
  • Gender: Male
  • Skruntie Land.
  • Posts: 25692
  • 3.O Elite Saloon with all the toys,
    • 2003 CD 2.2 Auto
    • View Profile
Re: Office Help - Excel Sort Criteria
« Reply #2 on: 06 January 2011, 14:39:55 »
Quote
Yes, easy

Highlight the data, click on sort (under Data tab),

Under the column list select

Column - X (or which ever column has the text in it)
Sort On - Values
Order - A to Z

Thats how I do it at the moment mark.  But because of the codes and/or dates at the front I cant sort to get the results I want. 

As you can see in the result above it sorted everything starting 5111

Where as I want Tesco Garage as a group to cost fuel and Tesco shop to price shopping.
Logged
Ask yourself :  " WHY do I believe in what I believe?"

Remember that my opinions expressed here are not representative of the opinions of other members on the OOF Forum.

Marks DTM Calib

  • Administrator
  • *****
  • Offline Offline
  • Gender: Male
  • West Bridgford
  • Posts: 34037
  • Git!
    • View Profile
Re: Office Help - Excel Sort Criteria
« Reply #3 on: 06 January 2011, 14:44:06 »
Ok, didn't realise the txt data was so raw.

So, before doing the sort

Select the Text and click 'Data' and select 'Text to Columns'.

Follow the wizard and this will allow you to split the text you want to sort against into a seperate column

Then carry out the sort
Logged

Mr Skrunts

  • Get A Life!!
  • *****
  • Offline Offline
  • Gender: Male
  • Skruntie Land.
  • Posts: 25692
  • 3.O Elite Saloon with all the toys,
    • 2003 CD 2.2 Auto
    • View Profile
Re: Office Help - Excel Sort Criteria
« Reply #4 on: 06 January 2011, 14:46:16 »
Many thanks Mark, will try that later.   :y :y
Logged
Ask yourself :  " WHY do I believe in what I believe?"

Remember that my opinions expressed here are not representative of the opinions of other members on the OOF Forum.

Marks DTM Calib

  • Administrator
  • *****
  • Offline Offline
  • Gender: Male
  • West Bridgford
  • Posts: 34037
  • Git!
    • View Profile
Re: Office Help - Excel Sort Criteria
« Reply #5 on: 06 January 2011, 14:48:35 »
Before you do it, insert a few blank columns to the right of the text data as its not the brightest wizard and will over right any data present there.
Logged

SteveMJ_new

  • Junior Member
  • **
  • Offline Offline
  • 0
  • Posts: 63
    • View Profile
Re: Office Help - Excel Sort Criteria
« Reply #6 on: 06 January 2011, 23:22:42 »
Try filtering instead of sorting - it may do what you want better.
S
Logged

Vamps

  • Offline Offline
  • Gender: Male
  • Bishop Middleham, Co Durham.
  • Posts: 24708
  • Flying Tonight, so Be Prepared.
    • Mig 2.6CDX and 2.2 Honda
    • View Profile
Re: Office Help - Excel Sort Criteria
« Reply #7 on: 06 January 2011, 23:27:21 »
Quote
[size=9]22-Nov-10      POS      2010 19NOV10 , TESCO GARAGE 4041 ,     GB      -       £5.00      £416.60
22-Nov-10      POS      5111 20NOV10 , ORANGE (A/PG/01) , DARLINGTON GB      -       £5.00      £411.60
22-Nov-10      POS      5111 20NOV10 , TESCO STORES SACAT,     GB      -       £4.14      £407.46
22-Nov-10      POS      5111 20NOV10 , BARGAIN BOOZE , SHEFFIELD GB      -       £13.07      £394.39
22-Nov-10      POS      5111 20NOV10 , VODAFONE , BANBURY GB              -       £30.00      £364.39
22-Nov-10      POS      5111 20NOV10 , TESCO GARAGE 4041 ,     ON GB      -       £10.04      £354.35[/size]

The above is just a a small selection copied from online bank statements to an excel spread sheat.

I know how to sort the data by highlighting the couloums, clicking on the key coloumn to sort etc.  But all the description coun starts with a 4 digit ref, in this case mainly 5111 or the date, or ref code then date.

My question is, can I sort by a key word in that coloum to bring all the Tesco garage, or Tesco store into a group so that I can then extract all those lines and cut/insert rows into other sprad sheets.

If that cant be done, I am then assuming I would be better using maybe Access Database and then I can sort by key words.

How easy it it to convert a spread sheet in to a Database.

TIA 

£5.00 of petrol, tight bugger..... :D :D :D
Logged

CaptainZok

  • Omega Lord
  • *****
  • Offline Offline
  • Gender: Male
  • Bolton
  • Posts: 8093
  • Victim of Cyberbullying.
    • 3.2 MV6 Estate
    • View Profile
Re: Office Help - Excel Sort Criteria
« Reply #8 on: 06 January 2011, 23:29:10 »
Quote
Quote
[size=9]22-Nov-10      POS      2010 19NOV10 , TESCO GARAGE 4041 ,     GB      -       £5.00      £416.60
22-Nov-10      POS      5111 20NOV10 , ORANGE (A/PG/01) , DARLINGTON GB      -       £5.00      £411.60
22-Nov-10      POS      5111 20NOV10 , TESCO STORES SACAT,     GB      -       £4.14      £407.46
22-Nov-10      POS      5111 20NOV10 , BARGAIN BOOZE , SHEFFIELD GB      -       £13.07      £394.39
22-Nov-10      POS      5111 20NOV10 , VODAFONE , BANBURY GB              -       £30.00      £364.39
22-Nov-10      POS      5111 20NOV10 , TESCO GARAGE 4041 ,     ON GB      -       £10.04      £354.35[/size]

The above is just a a small selection copied from online bank statements to an excel spread sheat.

I know how to sort the data by highlighting the couloums, clicking on the key coloumn to sort etc.  But all the description coun starts with a 4 digit ref, in this case mainly 5111 or the date, or ref code then date.

My question is, can I sort by a key word in that coloum to bring all the Tesco garage, or Tesco store into a group so that I can then extract all those lines and cut/insert rows into other sprad sheets.

If that cant be done, I am then assuming I would be better using maybe Access Database and then I can sort by key words.

How easy it it to convert a spread sheet in to a Database.

TIA 

£5.00 of petrol, tight bugger..... :D :D :D

He's from yorkshire, what do you expect. ;D
Logged
PM me for code reading/clearing
TuBy's new whipping boy.

Mr Skrunts

  • Get A Life!!
  • *****
  • Offline Offline
  • Gender: Male
  • Skruntie Land.
  • Posts: 25692
  • 3.O Elite Saloon with all the toys,
    • 2003 CD 2.2 Auto
    • View Profile
Re: Office Help - Excel Sort Criteria
« Reply #9 on: 06 January 2011, 23:46:26 »
Quote
Quote
Quote
[size=9]22-Nov-10      POS      2010 19NOV10 , TESCO GARAGE 4041 ,     GB      -       £5.00      £416.60
22-Nov-10      POS      5111 20NOV10 , ORANGE (A/PG/01) , DARLINGTON GB      -       £5.00      £411.60
22-Nov-10      POS      5111 20NOV10 , TESCO STORES SACAT,     GB      -       £4.14      £407.46
22-Nov-10      POS      5111 20NOV10 , BARGAIN BOOZE , SHEFFIELD GB      -       £13.07      £394.39
22-Nov-10      POS      5111 20NOV10 , VODAFONE , BANBURY GB              -       £30.00      £364.39
22-Nov-10      POS      5111 20NOV10 , TESCO GARAGE 4041 ,     ON GB      -       £10.04      £354.35[/size]

The above is just a a small selection copied from online bank statements to an excel spread sheat.

I know how to sort the data by highlighting the couloums, clicking on the key coloumn to sort etc.  But all the description coun starts with a 4 digit ref, in this case mainly 5111 or the date, or ref code then date.

My question is, can I sort by a key word in that coloum to bring all the Tesco garage, or Tesco store into a group so that I can then extract all those lines and cut/insert rows into other sprad sheets.

If that cant be done, I am then assuming I would be better using maybe Access Database and then I can sort by key words.

How easy it it to convert a spread sheet in to a Database.

TIA 

£5.00 of petrol, tight bugger..... :D :D :D

He's from yorkshire, what do you expect. ;D


and allways keep a 5 litre can in the boot with a fivers worth of fuel in it, coz if you help some one by the road side thats run out you can gaurantee they wont have more than a fiver on them to cover the fuel you pour in thier car to help them out.   :-/
Logged
Ask yourself :  " WHY do I believe in what I believe?"

Remember that my opinions expressed here are not representative of the opinions of other members on the OOF Forum.

Vamps

  • Offline Offline
  • Gender: Male
  • Bishop Middleham, Co Durham.
  • Posts: 24708
  • Flying Tonight, so Be Prepared.
    • Mig 2.6CDX and 2.2 Honda
    • View Profile
Re: Office Help - Excel Sort Criteria
« Reply #10 on: 07 January 2011, 00:21:52 »
Quote
Quote
Quote
Quote
[size=9]22-Nov-10      POS      2010 19NOV10 , TESCO GARAGE 4041 ,     GB      -       £5.00      £416.60
22-Nov-10      POS      5111 20NOV10 , ORANGE (A/PG/01) , DARLINGTON GB      -       £5.00      £411.60
22-Nov-10      POS      5111 20NOV10 , TESCO STORES SACAT,     GB      -       £4.14      £407.46
22-Nov-10      POS      5111 20NOV10 , BARGAIN BOOZE , SHEFFIELD GB      -       £13.07      £394.39
22-Nov-10      POS      5111 20NOV10 , VODAFONE , BANBURY GB              -       £30.00      £364.39
22-Nov-10      POS      5111 20NOV10 , TESCO GARAGE 4041 ,     ON GB      -       £10.04      £354.35[/size]

The above is just a a small selection copied from online bank statements to an excel spread sheat.

I know how to sort the data by highlighting the couloums, clicking on the key coloumn to sort etc.  But all the description coun starts with a 4 digit ref, in this case mainly 5111 or the date, or ref code then date.

My question is, can I sort by a key word in that coloum to bring all the Tesco garage, or Tesco store into a group so that I can then extract all those lines and cut/insert rows into other sprad sheets.

If that cant be done, I am then assuming I would be better using maybe Access Database and then I can sort by key words.

How easy it it to convert a spread sheet in to a Database.

TIA 

£5.00 of petrol, tight bugger..... :D :D :D

He's from yorkshire, what do you expect. ;D


and allways keep a 5 litre can in the boot with a fivers worth of fuel in it, coz if you help some one by the road side thats run out you can gaurantee they wont have more than a fiver on them to cover the fuel you pour in thier car to help them out.   :-/

I like your thinking but you suggest that you never go out.... ::) ::) ::)
« Last Edit: 07 January 2011, 00:22:23 by floodm »
Logged

Mr Skrunts

  • Get A Life!!
  • *****
  • Offline Offline
  • Gender: Male
  • Skruntie Land.
  • Posts: 25692
  • 3.O Elite Saloon with all the toys,
    • 2003 CD 2.2 Auto
    • View Profile
Re: Office Help - Excel Sort Criteria
« Reply #11 on: 07 January 2011, 00:24:44 »
I dont much.

Didnt turn a wheel while the car was iced up, and only short journeys since to pick up or drop off GF when she has fancied a drink.  :-/
Logged
Ask yourself :  " WHY do I believe in what I believe?"

Remember that my opinions expressed here are not representative of the opinions of other members on the OOF Forum.
Pages: [1]   Go Up
« previous next »
 

Page created in 0.012 seconds with 17 queries.