Search

Using the Excel Spreadsheet and A J Munday's Magazine Index Program

Return to Colin Ushers Engineering Web Page

The following notes are for users not familiar with Microsoft Excel and can be disregarded by 'computer buffs'.

When doing searches it is all too easy to totally scramble the data. Only sort THE WHOLE DATABASE. (by selecting the extreme top left hand box) If you do scramble it by mistake, close the file BUT DO NOT SAVE. The original file will be unaffected or you can just re-load from the un-zipped master copy, in the worst case download a brand new file off the web.

The main point of all these files, in fact the only point, is to enable you to FIND information or articles etc. as quickly as possible. Which magazine had an article on Compound Locomotives. Where are the all articles by LBSC. Virtually impossible without these files !!

To do a full spreadsheet search click on Edit then Find and in the Find What box enter a word say "compound" then click on Find Next box. Excel finds each occurrence of this word in turn. The drop down box can get in the way but can be moved around by "dragging and dropping" with the mouse, using it's blue top margin. By highlighting columns (fields) or linked sections of the spreadsheet, searches can be confined to these areas. Otherwise it searches the whole database. Find Next moves on to the next entry etc.

This is not really the best way to search as it only gives one result at a time By highlighting one particular field or column your selection can be even more refined. First select a data field (column) by clicking in the very top margin, the entire column will turn blue. (you may find that some columns are too wide to fit onto the screen, use Format, Column, Width to reduce as required - 150 is about right) Then select Data - Filter - Auto Filter(on) A little arrow will appear on the top right hand side of the data field selected. Click on the arrow and a drop down menu appears, select "custom" (third choice down) a new box appears with four blank fields. Click the arrow in the Top LH box and select "contains" (last but one) In the top RH box type in the search word ( say Fury ) a further selection can be made using the two lower boxes. For example you could select "contains" and use the search word "compound". Click on OK and all the rows in the selected field of the database containing the word Fury AND compound will be shown. If you do not find the word you are looking for try alternatives i.e. 5" or try a different data field (column) for example Author and use the search word Martin Evans. You will of course get ALL the articles written by Martin Evans but at least you can refine this sub-set of data further and filter out the unwanted articles. Note that the required information may be continued on the next set of data and may even start on the previous set. The search is not case sensitive. Fury or fury will work just as well. To return to the full data sheet click Data - Filter - Auto(off) Other types of selection can be used as required, equals, does not contain, etc.

There is yet a third way to find information. Select the whole sheet by clicking on the very top box in X and Y margins. The whole spread sheet turns blue. Click Data - Sort. A Sort By box opens up, click the Header Row button (on) and select a column to Sort By, Then By, etc. Choose either Ascending or Descending then click on OK. This will re-sort the whole database using the selected column(s) as a reference. This method is dangerous as if you do not select the whole sheet initially you can totally scramble the database. (it does warn you first) This is NOT reversible. If you do make this error Close the current sheet, but DO NOT SAVE. If you SAVE you will overwrite and scramble your file copy as well. You can of course always download a new file off the web and there is an fresh update every month anyway !! This method is a bit crude but does have it's uses. You could select all the Authors to find all article by a specific person etc. Just use the method that suits. I use all three as required.

The Model Engineer Index does not have any line numbers. To resort back to its original order use the above method and sort by Volume, Issue, Page in that order. I have now added line numbers to the last free column, but as mentioned below these will be lost at the first update.

Beware of making any alterations, changes or additions to your database. These will be lost at the first update unless you are prepared to take special precautions. These search procedures seem a bit drawn out but it takes far longer to try and explain the method than to actually do it. Once you have done it a few times it is very simple and quick. Just play around and it will all soon fall into place.

All rights reserved. No part of the Data may be reproduced or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise without the written permission of the publisher. It may however be used, without permission, freely by private individuals for personal use only.

These files have now been used by many thousands of Model Engineers from all over the world and have proved to be very robust. That is they work. If you have problems it is almost 100% certain that the problem lies in either your Internet connection or your Computer.

Note:- It is essential that WinZip and Microsoft Excel are installed on your PC. Alan J Munday's Index.exe program does not need WinZip or Excel installed.

They are very large files and will take some time to download, particularly the Model Engineer files.

All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical or photocopying, recording or otherwise without the prior permission of the copyright holder. Except for private & non-profit use.