Entering Series

How to enter a series of numbers automatically in Excel

I have so many records but I need to enter serial numbers automatically in Excel 2007. I have hundreds of records. Instead of entering them manually I want them to show up automatically. Please give me short example for 1 to 10.

If you want to pick cell entries from a list then you have a couple of non-code based options Data Validation, which is very well covered at Debra Dalgleish's site Excel's autocomplete feature I would recommend The Data Validation approach where creating a list of your 100 records in a single column, provide a range name to this list, then using Data Validation's List option sample from Debra's site below, click on the first link above to access it.

up vote 1 down vote accepted

you need to fill only starting 2-3 numbers (or text for that matter) and then drag the range down using fill handle. MS Excel will identify the series by itself and will fill the range till where you drag down the range. The below image shows the ‘Fill Handle’.

Using Excel to create a series of dates

This screencast takes you through:

• Creating a series of dates using Microsoft Excel
• Making the data ready to import into TourCMS

Within TourCMS you can load individual dates and prices (for fixed date trips using the departures travel product type) as well as load by the season (using freesale season travel product type). In some situations where freesale season travel products would work, some TourCMS users prefer the departure functionality as it enables more "micro level" price and availability control. However, as a result, this often means loading many, many price and date combinations.

The following steps describe exactly the steps followed in the screencast above:

Step 1: Create your tour departure start dates

• Open up Microsoft Excel (or other spreadsheet software)
• In one cell, put a start date
• In the cell below, put a subsequent start date. If you are going to produce dates for "Every Saturday" this 2nd date should be 7 days later than the first date
• Highlight both dates
• With your mouse, select the bottom right hand corner of the 2nd date - and scroll down - new dates will be created

Step 2: Create your end dates
Each departure needs both a start and an end date

• If you are running a day trip, the end date will be same as the start date
• Using Excel, you can create the end date column to add a default duration to the start date. The format for the end date column is =b5+7 (where b5 is the cell address for the start date and 7 is the default duration)

Step 3: Add your prices
As a minimum you need a sale price and a supplier cost

• Paste your created dates into Notepad (or other text editing software)
• Remove the gap between the two dates (start and end date) and replace with a comma
• Add your sale price and supplier cost (can be different per date) as well as your departure product code (if required). Pay attention regarding the number of commas you use

Step 4: Copy and paste into TourCMS

Go to a tour, into dates & prices, then Create multiple departures

• Paste your data from Notepad into the box
• Ensure that you tick Enable booking immediately (i.e. not manually closed) otherwise you will have to open up for sale each date individually (which could take rather a long time, if you are adding multiple dates)

The screen, within TourCMS, where you can place your date & price data

Notes & tricks

• Remember there is no "mass delete" - so check your dates and prices before you load them! (you can delete individual dates only)
• The date format will be whatever date format you have configured for your account (for example US style, European style or Chinese style)
• If you have configured multiple rate types (perhaps adult, child, concession etc, rather than just a single rate) then pay attention regarding the format that TourCMS requires for these rates. This will be explained on the mass departures loading screen within TourCMS, depending upon your settings for that tour
• If you have a series of dates that operate on Monday, Tuesday and Wednesday (but not other days of the week) do all Mondays, then all Tuesdays then all Wednesdays. This will probably be easier to work out than playing with Excel to do complex date schemes

Excel space issues

In Excel, if when pasting, you see something like this:

16/10/2008,03/11/2008,  2750    ,0,     code

Then use a cell function like =A1&B1&C1 which would give you

16/10/2008,03/11/2008,2750,0,code

i.e. the ampersand & can be used as a function to merge cells without spaces.

Alternative Excel method

 
Excel supports the export of data in CSV (Comma Separated Value) type. First, enter your dates into Excel as detailed above, and then save the file as a CSV type. Then, open this file in Notepad (right click and 'open with'). You will then see your list of dates & prices, complete with commas and without any gaps.

Adding a Series of Numbers with the Fill Handle

 

You can use the fill handle to add a series of numbers to an Excel spreadsheet.
To do so, you need to enter enough data to show Excel the pattern - such as counting by two's that you want to use.
Once you have done this, the fill handle can be used to repeat the series as often as needed.
Example Using the Fill Handle to Add a Series of Data:
Note: For help with this example, see the image to the right.

1. Type the number 2 in cell D2 and press ENTER on the keyboard.
   
   
2. Type the number 4 in cell D3 and press ENTER on the keyboard.
   
   
3. Drag select cells D2 and D3 to highlight them.
   
   
4. Click and hold down the mouse pointer on the fill handle in the bottom right corner of the cell D3.
   
   
5. Drag the fill handle down to cell D6.
   
   
6. Cells D2 to D6 should contain the numbers : 2, 4, 6, 8, 10.