Matching data

Question

I am not too sure what I need to do is called but let me try to explain it.

I have onesheet that has 5 digit numbers in it beside each of these numbers there is a(about 1000 numbers/words in total). In anothersheet I have about 10,000 unique numbers. What I need to do is put thefrom the firstsheet beside the number that starts with the same 5 digits in the firstsheet. In the secondsheet the numbers may be longer then 5 digits e.g. 8 but i need to match them up withthat links to the first 5 digits.

Answer

that function is called VLOOKUP

you want to bring sheet1 names with longer than 5 digits numbers to sheet2 which only have 5 digit numbers

use the following formula, assuming that: 1.- the numbers are in column A and the names on column B =VLOOKUP(A1&"*",Sheet1!A:B,2,0)

if your numbers are in column C and the names on column E then you can change you formula like this: =VLOOKUP(A1&"*",Sheet1!C:D,3,0)

Notes: you 5+ digits numbers MUST be on text format, and those numbers MUST be on the first column of the Vlookup Range if you don¡¯t want those numbers to be in text format (cause you might need to sum them up) then I suggest to use another column to set a formula which turn them into text formula like this: =TEXT(A1,"@")

Question

I would be very grateful if you could shine a light on the following for me_ I have two worksheets containing data for a traffic entering and leaving a car park, I need to identify how long each vehicle stayed in the car park, and what type it is. eg. Work sheet no. 1 Entering Carpark Reg. Time Type 112 08:18 1 302 08:21 2 etc etc.

Work sheet no. 2 Exiting Carpark Reg. Time Type 302 08:49 2 112 08:54 1

Is it possible to sort the two sets of data so that they appear side by side eg Reg. Time Type Reg. Time Type 112 08:18 1 112 08:54 1

Answer

You can physically bring the data in by using macros, but I would suggest that the easiest solution would be to use a vlookup formula (see http://www.aidanheritage.byethost3.com/excel/xlfdic01.xls for examples of this and otherformulas.

Any issues feel free to ask follow up questions and if it helps my email is aidan.heritage@virgin.net