Spreadsheet

Started by Dorsetmike, May 13, 2017, 05:38:24 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Dorsetmike

I have downloaded a spreadsheet (.xls) of SR shed allocations, it is sorted by loco number
columns for loco number, class, date and shed)

Is there an easy way to sort out all entries for one shed and copy to a new spreadsheet, I can "Find" all entries for a shed but so far it's a case of select the row (or sometimes consecutive rows) and copy/paste, it would make sense to me for there to be a way of having found all entries for "shed" being able to selecting all relevant rows and copy the lot with one command.

I'm using Libre Office.

My hope is that I can use the result to renumber locos to what would be allocated to that area.
Cheers MIKE
[smg id=6583]


How many roads must a man walk down ... ... ... ... ... before he knows he's lost!

Chetcombe

Not sure about Libre Office, but in Excel you can select 'data' then 'filter' then 'auto filter'. This puts a little down arrow on the header for each column, which if you click it will show the data entries included for that column. So for the shed column you can click on say 80A and only the rows that have that particular shed entry will be shown.

For this to work the spreadsheet must be formatted with the Column Name in the top row and the data being in all the subsequent rows. You may need to cut and paste the data or reformat the spreadsheet to make this work.

Good luck!
Mike

See my layout here Chetcombe
Videos of Chetcombe on YouTube

Buzzard


njee20

Ctrl+shift+L is the keyboard shortcut to add filters in Excel, a lot of shortcuts are the same on Libre Office too, try it. Otherwise you're looking for a funnel symbol.

Dorsetmike

Buzzard, where do you think I downloaded the spreadsheet from ::)

Myself and another SEMG member started that off, other members kicked in whatever they could come up with and thus we compiled those shed allocation listings many years ago, somebody else then did the spreadsheets. They are not complete, we dredged the info from many diferent books and other sources, DL  Bradley's books providied me with most of the LSWR and early SR

I was just hoping for a lazy old man's way of getting the data I want extracted.
Cheers MIKE
[smg id=6583]


How many roads must a man walk down ... ... ... ... ... before he knows he's lost!

njee20

Quote from: Dorsetmike on May 13, 2017, 06:41:11 PM
I was just hoping for a lazy old man's way of getting the data I want extracted.

And you've been offered it... filter, or just sort, and copy in blocks.

CarriageShed

I'll be happy to do any sorting and extracting for you, Mike. Excel is something I handle regularly at work so I'm pretty familiar with it. All data provided in plain XLS if that's what you prefer.

njee20

Yep, likewise, sounds like the work of a few seconds if you want to ping it across.

Dorsetmike

Finished up doing it by using the SEMG sorted by shed lists, selected shed then highlighted the period and did a copy/paste into notepad, selected other nearby sheds, repeat as necesary.

Some locos that I have  were not shedded anywhere local a few can be justified, but that still leaves a few rule 1 endorsements on my Modellers Licence.
Cheers MIKE
[smg id=6583]


How many roads must a man walk down ... ... ... ... ... before he knows he's lost!

Please Support Us!
March Goal: £100.00
Due Date: Mar 31
Total Receipts: £77.34
Below Goal: £22.66
Site Currency: GBP
77% 
March Donations