I've been assigned an assessment involving excel (2007), which involves me creating a small business system.
Part of this system involves a spreadsheet with a list of engineers and the postcode range that they are allocated to to fix callout problems.
What I would like to know, is if a callout request is made using a postcode, is it possible to automatically assign the engineer to the request?
Example, J Scott's work range is NE10 - NE19
If I entered the postcode NE12 7TD into the callout request form, is it possible to use formula or VB to assign J Scott's ID to the callout request?
All suggestions and help are greatly appreciated, I would request that solutions are kept as simple as possible as long as it's not much to ask for D:.
A possible solution i've thought of is making 2 fields in each of the engineer's rows, one being the lowest area range, and the other being the highest (e.g. J Scott's would be 10 and 19 respectivly) from there, I could possibly use formulae to assign the engineer's ID, and get his details using VLOOKUP.
Column D contains a complete address eg 60 Braugham Road, Wallasey, Merseyside CH46 1LP
I would like to be able to extract the postcode into column E, and then the address into columns F, G, H etc so that I can use the data for mailmerge docs. Is there a forumula or a macro that can be used to identify and extract the postcode? The postcode will usually be either one or two letters with one or two numbers, then a number and two letters. eg it might be as above or B6 9XF etc.
I have a worksheet of customer data which includes full postcode / zip codes. I have a separate sheet where postcodes are listed. I would like to use the postcode list worksheet to find any address matches and then create a new worksheet of the filtered addresses (to include duplicates if found). Have attached a zip file of the spreadsheet data as an example.
How can I sort the attached list both alphabetically and numerically. I have a list of UK postcode sectors which I am trying to sort alphabetically and numerically, the order should be eg AB1 0, AB10 1, G2 1, G20 1 but excel wont allow that sort order. There are three postcode formats
XX11 1 X1 1 X11 1
Where X an Y could be any letters in the alphabet and 1 could be any digit 0 to 9. Have attached a short list and formula in column B which would allow me to sort the list in column A which I can then apply to my full list.
I have a very large spread sheet of about 20,000 rows. I need to output to a cell postcode ranges based on the Zone. Not all the numbers in the postcode zones are consecutive.
For the example below, the output cell for the DWN range would display like this: 0800-0801, 0804, 0810-0813
Each output cell postal range must be specific to each zone.
Is there anyway to do this without manually doing it? I would like to tell you I've tried this and that but I've never done anything like this before so I really don't know where to start.
How would I verify a postcode format that starts with a number followed by one or two letters, space, number, letter, letter, if correct displays correct if incorrect displays incorrect
Excel 2007.I have a list of postcodes (UK) which have different lengths of characters (including spaces) from 6-8, however our system seems to add additional spaces inbetween the postcode, so it could have upto 11/12 characters (inc spaces) Below is what could come out:
What I need is a formula to ensure each postcode only has 8 characters by inserting spaces between if there's less than 8 char and trimming if there's more than 8 char
So from the above postcodes the desired results would be:
EH21 6PQ - 1 Space (8 Char) - This would be correct EH12 9HG - 3 Spaces (10 Char) - Trim off 2 spaces from the middle E1 8DF - 3 Spaces (8 Char) - This would be correct LL5 1GH - 2 Spaces (8 Char) - This would be correct L5 1FG - 1 Space (6 Char) - Insert 2 spaces in the middle
I am working on sales information which includes postcodes. What i need to do is seperate the first or first two text characters from the rest of the postcode. I have attached a small snipet of what i am working on. Currently i am using the =Left(A4,2) but this will give me in some case a numerical value aswell. For example E1 or G1 in the case of the sample attached. Is there a formula that exists where it will just return the text values in a cell and not numerical values.
I want to see if a value in column A appears in any of the cells in Column B and to give the answer in column C.
I am trying to match postcodes, so I want to see if a postcode in a cell in column A appears in column B. I am aware the IF function will see if it appears in the cell in the ajoining row but I need to select the whole column dataset.
how to create the database and the resulting Pivot Table. My Sales Database includes the Sales $ and GP$ for each line. In the Pivot Table I've managed to create a calculated field for GP %, no problem.
i have also managed to figure out how to express each Row as a % of the Pivot Total, also very easy.
What I need to know is how can I sort my Pivot Table High to Low using these calculated fields?
I don't understand all of it, but I observed that it works only with mp3 with tag id v1, not v2, anyway I also see another code for tag id v2 given by NateO.
But before entering in detail of what I want, is it possible to edit tag id v1 or even v2, with VBA? Because I want to complete my mp3 informations that are missing, or modify the wrong one, in excel, and then with a macro update my mp3 files...
I've found this code on the forum to get my mp3 informations :
Option Explicit
Public Type mp3Info Header As String * 3 Title As String * 30 Artist As String * 30 Album As String * 30 Year As String * 4 Comment As String * 30 Genre As Byte End Type
Sub Getmp3Info() Dim mp3ID As mp3Info Dim lngRow As Long, lngFile As Long Dim lngFileCnt As Long
With Application.FileSearch .NewSearch .Filename = "*.MP3" .LookIn = "H:Daniel" 'Change this to the folder you wish to search .SearchSubFolders = True........................
I have a sheet with fixed number of rows but variable number of columns. I have to sort this sheet on the basis of column A starting A2. i am using the following code
But it does not sort properly. To illustrate, i have 10 rows, some 4 columns wide and some 5 columns wide. Since the first row is 5 columns wide , all the rows with 5 columns populated get sorted but the rest get left out.
(There are 500+ more lines like the ones below, in groups of approximately 17 lines per page of information.)
* C.B.A. Bank - 2nd reference to branch near Mike's store. (21/03/1956) * Berwick exams results. (06/06/1877) * Berwick exams (09/05/1877) * Steps taken for erection of Berwick. (11/07/1874) * Berwick tenders for work at home. (11/07/1877) * W. was a storekeeper at Berwick. (25/07/1881) * Berwick Brass. (27/06/1877) * Commercial erected for son. (27/06/1878)
I need to do the folowing to it: (not necessarily in the following order)
- Insert into Excel - Remove brackets - Sort chronologically - Copy back into program I am using again, in same chronological order.
The desired outcome is to be able to throw into Excel all the lines and press a button, (possibly the Sort Ascending one) and have a chronological list of all entries.
I have a table which holds scores as well as other data for players. Is it possible to automatically sort a table based on their points score to see who is top of the league?
Is there a code or some way for me to sort 4 columns together? I want to sort a list of employees and for each employee theres a column with their Lost Business, customer satisfaction, and two more columns. I want to sort all the columns at the same time so that the best employees overall will go to the top of the list and the worst ones will be at the bottom. Whenever I use the sorting feature it does each column independant of the others so everytime I sort a new column it just moves around the last one I sorted.
I have five columns of data A to E. In each row there can be data in either 2 3,4 or 5 of the columns. What I want to do is essentially condense the data. So in columns, say, G,H,I J,K if there is data in column A then It goes into column g, if not it asks if there is any data in column b and so on. Then for H it does the same but one on. I have attached an example to show what I would like to happen.
I created a data base that had a hyperlink to a plan of the store so we can find parts easy. With a lot of help for people here on this web site, it works very well.
My only problem now is that I sorted my Database by the hyperlink column. It made it easy to do one hyperlink and pull the fill handle down for the rest of the same hyperlink reference.
When I went to resort the database so that the parts were in alphabetical order. The sort worked well except that the hyperlink for lets say cell F87 was not sorted. Example : - F87 hyper link presort had the reference to Rack 10 self B. Post sort it had the reference to Rack 16 shelf C. But when clicked on it still took you to Rack 10 self B.
I'm trying to sort this spread sheet so that each email address will have its own column assigned to it. Ie jaz@hello.com will alway be column C and harry@hello.com will always be in D. The problem is I've around 1200 rows of infor and the email addresses are all over the place..
I have around 1900 dates that I need to sort into order but I need the data to be displayed as DD/MM in chronological order eg 01/01, 02/01 etc.
At the moment the dates are DD/MM/YYYY in one column. Have tried sorting them into date order but have found that they're sorted by YYYY. Is there a way of sorting them DD/MM instead?????
I have an array of data that I need sorted. I did a Custom Sort. However, the data did not all sort properly (some of it did). Any ideas as to why this happened and how I can get it to sort correctly?
Im not entirely sure about this but it seems like if I have data in columns A - Z and sort in one of them, the data in Columns AA - AZ does not move accordingly rather it stays fixed.
I have a column showing date DD/MM/YYYY. I want to be able to get a new column stripping out the days so that I can chart changes over a month rather than each day of the last 2 years. I have tried altering the cell format to MM-YYY although this shows the format I want when i run a pivot chart it will still create records for individual days. I then tried copying and pasting the above as text only. This results in the pivot chart sorting A-Z rather than by date.
I have information in columns A (First name),B (Last name), C (Misc), D (misc). Is there a way that someone can enter in new information at the bottom of the list and it will automatically sort by the last name once saved or closed ect.. ? I have people not very familiar with Excel and the keep messing up inserting rows and I don't want them to sort. If they could just enter information at the bottom and save it, then when someone opens it to just view it, it will be in order.
I am mapping this file on the basis of a lot of fields. But the end product has 15 currencies. My requirement is to separate the accounts on the basis of the currencies and then consolidate it on the basis of GL account. For example, first I have to find all the accounts that are in USD and then if there are 2-3 similar GL accounts, then I will consolidate on the basis of the GL accounts. Please find attached file.
