Help creating Excel workbook please.

  oo7juk 09:50 12 Jan 2007


Can someone please help with the following please.

I have a spreadsheet that contains about 50,000 lines of data (addresses). I want to use this data to create a workbook for the following purposes.

1. generate a master workbook that splits the data into 3 main services and also assigns then route numbers and uplift days according to their service.

2. would like to be able to total the number of properties per route, per day according to service too.

3. also indicate flatted properties too and totals etc like above.

Previously there was only one service and before my time a spreadsheet was generated with different tabs at the bottom indicating each route. I'm looking to create a doc that can provide all this info at a glance, so that if someone has an address they can use the find option and be provided with all the info they require and more if needed.

I have created a doc that might help explain what I'm trying to do, but my knowledge of excel is limited so hoping someone can point me in the right direction. I tried to use a book but find it difficult to follow.

Many thanks,

  VoG II 10:11 12 Jan 2007

First you should be aware that Excel is limited to 65,536 rows per sheet (although this is increased in Excel 2007).

What you have described seems an ideal candidate for a VLOOKUP. VLOOKUP is a way of using Excel as a database. The syntax is

=VLOOKUP(what, where, col, True or False)

what is the value that you want to lookup

where specifies the range that you want to look in

col is the column number in your range that you want to lookup.

True looks for the nearest match in a sorted list; False looks for an exact match.

Say you have a list of names in A1:A10, ages in B1:B10 and phone numbers in C1:C10

=VLOOKUP("Colin", A1:C10, 2, False) will give Colin's age

=VLOOKUP("Ben", A1:C10, 3, False) will give Ben's phone number.

what must be to the left of the data that you are trying to look up (in the examples, column A is to the left of columns B and C).

  oo7juk 12:36 14 Jan 2007


Does that mean I have to create a database from the large spreadsheet I have? Do I have to insert extra columns into the database. Had a look at vlookup but couldn't get it to work.


  VoG II 12:42 14 Jan 2007

You shouldn't need to insert extra columns. Say your info is all on sheet1 with the addresses in column A then on another sheet type an address in A1 and use a lookup like

=VLOOKUP(A1, 'Sheet1'!$A$1:$X$50000, 2, False)

to return other information.

"Had a look at vlookup but couldn't get it to work." - have you tried creating a simple example like the one I posted.

  oo7juk 21:20 16 Jan 2007


Created an example but get 'circular reference error' and when I tried the other way I just got a '0' as an answer.


  VoG II 21:27 16 Jan 2007

Please take look at this click here

  amyfleur 22:31 16 Jan 2007

for info.

  Dazza40 22:41 16 Jan 2007

bookmarked for info.
excellent advise VoG™. Clearly explained

  oo7juk 00:06 18 Jan 2007


Thanks for the link, managed to get the example to work but I no what I want to do for my project but can't seem to get it to work when I use the formula with my data. Maybe I'm wanting it to do too much, I really dont know to be honest.


  VoG II 15:03 27 Jan 2007

See if this helps click here

Turn the volume up a bit to hear the commentary.

  oo7juk 23:17 28 Jan 2007

Thanks VoG,

Managed to create a sample spreadsheet using the data I have. So far so good is doing what I want from it, but I have a few questions re spreadsheet:-

I have set up it using the lookup function as you suggested. I have created 2 workbboks one contain the large data file and the other containing the results I want.

I have names a cell 'search' and in this I enter the address and it generatrs my route no etc, but I have a few questions to see if you can help.

1. Some of the streets belong to the same district but different towns i.e. brown st, kirkintilloch and brown st bearsden. How would I be able to find brown st in bearsden.

2. Also when I type the address I have to type the whole address is there anyway it could be done in a predictive way.

3. Also only the large spreadsheet there is particular rows that I want to delete. Can I do it all together rather than individually as it would take forever.

Many thanks to all who have helped so far.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5T review: Hands-on

Illustrator Andrés Lozano on his improv line work, brazen use of colours & hand sketching

iPhone X review

Comment envoyer gratuitement des gros fichiers ?