Omega Owners Forum
Chat Area => General Discussion Area => Topic started by: Mr Skrunts 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
-
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
-
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.
-
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
-
Many thanks Mark, will try that later. :y :y
-
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.
-
Try filtering instead of sorting - it may do what you want better.
S
-
[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
-
[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
-
[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. :-/
-
[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.... ::) ::) ::)
-
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. :-/