OK, a really tricky one now for Excel gurus!

A perfect response before, this time a little trickier!

I have two data sources:

Data A) a column of approx. 3000 cells featuring the 'Post Code' only from a 
database of addresses.
Data B) The Australian postcode database as found and downloaded from the 
below link:

http://www1.auspost.com.au/postcodes/index.asp?sub=2

What I want to happen is each postcode from source 'A' to match up with the 
corresponding post code from source 'B' (this is in column A). Once it 
matches, the 'Delivery Office' (column E) is to appear in column B of Data 
Source Sheet A....I can then sort my data by delivery office...

I hope that makes sense!!

Many thanks
Tim.


0
tim7439 (3)
10/25/2005 11:29:37 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
612 Views

Similar Articles

[PageSpeed] 38

Simple VLOOKUP

=VLOOKUP(A1,'Data B Sheetname'!A1:B3000,2,False)

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"tim :/" <tim@timland.com> wrote in message
news:435e1722$1@news.rivernet.com.au...
> A perfect response before, this time a little trickier!
>
> I have two data sources:
>
> Data A) a column of approx. 3000 cells featuring the 'Post Code' only from
a
> database of addresses.
> Data B) The Australian postcode database as found and downloaded from the
> below link:
>
> http://www1.auspost.com.au/postcodes/index.asp?sub=2
>
> What I want to happen is each postcode from source 'A' to match up with
the
> corresponding post code from source 'B' (this is in column A). Once it
> matches, the 'Delivery Office' (column E) is to appear in column B of Data
> Source Sheet A....I can then sort my data by delivery office...
>
> I hope that makes sense!!
>
> Many thanks
> Tim.
>
>


0
bob.phillips1 (6510)
10/25/2005 11:51:18 AM
=VLOOKUP(A2,'pc-full_20050916.csv'!$A:$E,5,FALSE)

-- 
Ian
--
"tim :/" <tim@timland.com> wrote in message 
news:435e1722$1@news.rivernet.com.au...
>A perfect response before, this time a little trickier!
>
> I have two data sources:
>
> Data A) a column of approx. 3000 cells featuring the 'Post Code' only from 
> a database of addresses.
> Data B) The Australian postcode database as found and downloaded from the 
> below link:
>
> http://www1.auspost.com.au/postcodes/index.asp?sub=2
>
> What I want to happen is each postcode from source 'A' to match up with 
> the corresponding post code from source 'B' (this is in column A). Once it 
> matches, the 'Delivery Office' (column E) is to appear in column B of Data 
> Source Sheet A....I can then sort my data by delivery office...
>
> I hope that makes sense!!
>
> Many thanks
> Tim.
>
> 


0
me1 (409)
10/25/2005 11:55:52 AM
Reply:

Similar Artilces:

EXCEL and Query
Microsoft Query won't give me access to data in a Microsoft Excel list. After connecting to the data source (another EXCEL spreadsheet) the error message 'No tables visible' is displayed. I have named the source spreadsheet as described in the on- line help, but still no result. Is there something else I need to switch-on? Help please. Have you tried giving a name to the table in the source spreadsheet. Eg. Data is in A1:F2500 - highlight that area and go Insert / Name / Define.... call it MyData - that table name should become visible when you do the query from the ot...

Consecutive page number with one master
I have created a "Gift Certificate" in publisher 2003. Is there a way that I can print 100 of these certificates and have publisher automatically put a consecutive number on each certificate? Thanks Jane Jalco <Jalco@discussions.microsoft.com> was very recently heard to utter: > I have created a "Gift Certificate" in publisher 2003. Is there a > way that I can print 100 of these certificates and have publisher > automatically put a consecutive number on each certificate? Yes, and there's a tutorial on how to do it at http://www.publishermvps.com --...

how to make a report to run on one account
How do I pass the AccountID to a report? I want the report to run on one single account, so the parameter must be one single GUID... On Jul 21, 8:54=A0pm, Thomas <tdem...@gmail.com> wrote: > How do I pass the AccountID to a report? I want the report to run on > one single account, so the parameter must be one single GUID... If you're using the 'CRMAF' trick, when you run the report from the form it should only run for that record. This might help http://www.microsoft.com/dynamics/crm/using/customizing/reporttutorial.mspx Leon Tribe Want to hear me talk about all thi...

Number of Graphs in Excel
Have heard rumours that there is a maximum no of graphs that can be in an Excel workbook. What is that number. I currently have 126 and am looking to increase, but don't want to lose everything in the process. What is the work around if there is a limit, or is there a patch? :confused: ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ...

how to create a chart in excel base on date?
hi..im a newbie to excel.. i need to create a bar chart..... i have only one column containing dates... which is this 03-Aug-05 03-Aug-05 03-Aug-05 03-Aug-05 03-Aug-05 04-Aug-05 04-Aug-05 04-Aug-05 04-Aug-05 04-Aug-05 04-Aug-05 04-Aug-05 04-Aug-05 .... .... ... until..... 31-Dec-05 total up to 10000 records of dates its a long list of dates and i need to create a bar chart showing th number of times in a day how many rows of them.. which means i have a bar chart showing on august and 3 days...etc...and a bar chart showing on sept....and so on.. i also need a bar chart on the 5 months..au...

Exporting from Outlook to Excel
Is it possible to export my Outlook contacts into an Excel spreadsheet? Thanks for your help... Hi in Outlook try using 'file - Import/Export' for this -- Regards Frank Kabel Frankfurt, Germany daviskb wrote: > Is it possible to export my Outlook contacts into an Excel > spreadsheet? Thanks for your help... In Outlook: File | Import and Export | Export to File. Choose Excel out of the list of files types available. Follow the Wizard and Map Fields as necessary. tj "daviskb" wrote: > Is it possible to export my Outlook contacts into an Excel spreadsheet...

One result from multiple memo fields 04-25-07
If I have duplicated this post I am sorry, my computer froze, so I wasn't sure if the first post got thru. I am an access newbie so I am not even sure this can be done... I need a report to pull material listing from a memo field [Notes] or show which jobs have missing materials... My report has the following record source: SELECT tblJob.Job, tblNotes.Notes FROM tblJob LEFT JOIN tblNotes ON tblJob.ID=tblNotes.JOBID; The report has a Group header on Job and a [Material] text field with the following control source: =IIf([Notes] Like "Material*",[Notes],"No Material listin...

Pasting from Excel to Word
Hi I have all settings re Dictionary set to UK. When I paste cells fro Excel to Word the table in Word will change the Language to US. Th rest of the document will stay the same. Please help - feel as if I am going round in circles. Sara -- Sarah Stit ----------------------------------------------------------------------- Sarah Stitt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=857 View this thread: http://www.excelforum.com/showthread.php?threadid=26638 I cannot reproduce this. Although Canadian, I use UK speller since Microsoft thinks Canadians can...

Timing is off by one hour
For some reason, time stamps of emails being sent, appointments, etc are all off by one hour. I have verified that the clients are all configured for central time zone. The Exchange server is set for central time zone, but for some reason, everytime anyone sends an email, the recepient shows it to be one hour behind. Also, if I setup an appointment in my calendar for 3:00pm and invite others to it, they will all show 2:00pm for appointment. Anyone have any idea on what is going on here? - Marty Check out the daylight savings checkbox on box the server and the clients. -- Martin ...

Help in transferring data from one sheet to another..
I have set up a table with the first row holding labels every 6 cells and below row 2 contains headings row over 2 columns of data under each label. I now wish to find a way to transfer this information to another sheet set up in a different layout with the data running in rows with the labels from sheet one running down column one. I hope I am making sense as I am fairly new to this thanks in advance for help Play around with Edit>Copy and Edit>Paste Special>Transpose Notes..........if running <2007 version you are limited to 256 to transpose to columns. Also you cannot overl...

Excel ODBC and dates...
Hi all, I'm using a label printing prog (Seagull - Bartender) to print some labels using ODBC data from Excel. (Win 2K and Office 2K - using "user DSN" for XLS files) Now dates appear OK in Excel ie: dd/mm/yyyy But when the data is accessed with ODBC they appear as follows: yyyy/mm/dd hh:mm:ss Help.. any ideas what's going on here? Regards, Doz ...

Setting up one server and one client in a linux network
I am trying to test active directory log in on a network with a linux dns and dhcp server. I have set up a windows 2003 server, and windows vista client. I now enter the name of the vista computer in Active Directory on the server. The server then tell me to use http://COMPUTERNAME/ConnectComputer so i type in http://<ip address of server>/ConnectComputer in a browser on the vista machine. It now runs a program called nshelp.exe. In that application I can now login using the AD administrator password. I can then assign different users to the vista client. However when I p...

These templates are amazing! This really shows you care about us!
Re: http://office.microsoft.com/en-us/templates/CT011815441033.aspx There was no place to post comments directly on that page, so I have to post here... perhaps a link on that page itself for comments? Or where should this go... ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the messa...

Database in Excel
Hi guys, Would anyone be able to give a little advise? I am trying to make a database in Excel, due to License restrictions (and cost), Excel is the only program we have a site license for. The database needs to be able to hold regions of data, which will show contact details for each region. In each region it would show around 20 Technicians, 2 Supervisors, 1 Manager, a Field manager, and finally the Head of Department. I would also like to make it possible that they just enter the Region Code or Tech ID and it bring up the region they work in. I know it sounds like a tall order but if so...

ok
ok! tanks!! -- tanks you obrigada e aguardo Critsina Cristina Vieira <CristinaVieira@discussions.microsoft.com> was very recently heard to utter: > ok! tanks!! Yo welcub. (I hab a bid of a code too) -- Ed Beddedd - MBP Microsoft Bublisher man you must have one heck of a toad up ur node...MBP ? ! I lub et. ...

can some one tell me whats wrong
well this problem has been going on for about 3 days.. the fan in my computer is making a loud noise that it never use to make before, andi dont kno whats wrong. i tried turning off the computer for hours and later i turned it back on and its still making the noise.. Julio, This forum is about Microsoft Access databases. You need to ask this question in a more appropriate forum to get help. That said, fans, like any mechanical device can fail. Try cleaning it if you can. Otherwise bring your PC to a service store near you or if you feel up to it buy a new one and replace it yourse...

Really Delete mail via Right click option
Is there a way I can add a menu item when i right click to "really" delete an item instead of just moving it to the delted folder. This would be very helpful with spam and adds that just take up space Jeff In news:1126660357.808429.51090@o13g2000cwo.googlegroups.com, orbojeff <jeff.callahan@orbograph.com> typed: > Is there a way I can add a menu item when i right click to "really" > delete an item instead of just moving it to the delted folder. > This would be very helpful with spam and adds that just take up space > > Jeff Not that I know of. Sel...

Why is Computer Freezing With Excel?
I'm trying to save the spreadsheet from a microsoft template and when try to open the file it freezes the computer. I thought it was th macros but when I deleted the macros and tried again it's stil freezing. Why is this Happening? Thank you, below is the link http://office.microsoft.com/en-us/templates/TC011684031033.aspx?CategoryID=CT01048287103 -- heitorfj ----------------------------------------------------------------------- heitorfjr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3049 View this thread: http://www.excelforum.com/showthread.php?...

Concatenation not working in Excel 2003 #2
I did the same thing and it is not working. I have even selected th entire contents of the spread sheet and made it text. This i frustrating to say the least. I have zipped up two records if someone wants to give it a shot +------------------------------------------------------------------- |Filename: Concatenation Problem.zip |Download: http://www.excelforum.com/attachment.php?postid=2728 +------------------------------------------------------------------- -- gschose ----------------------------------------------------------------------- gschoser'...

How to transpose data from multiple lines to one line
Imagine you have schedule d data that's formatted such that one line = one transaction. ie. entry and exit cross lines. How do you then transpose this data so that you can match up entry and exits and record them on one line like a schedule d form? I currently have a spreadsheet where each line is a separate transaction but I'd like to make it so that the same downloaded CSV data can be used whereby I can track PnL per trade instead overall. Robert, I will help if I can. I do not believe that most people(including me) understand what a schedule d form is. If you can provide sample...

Which Excel download should I get?
I need to be able to work with Excel from my home computer . I will mostly be doing scheduling, changing spreadsheets that are emailed to me, and probably no more. Which download should I get? Where do I get it and how much will/should it cost? My needs are simple. Julie There is no 'download' other than the time-limited beta version of Excel 2007. The least expensive version of Office 2003 is the Student & Teacher one (be aware that this cannot be upgraded). Not sure if one can buy a S&T version of just Excel - do some internet shopping (Futureshop, Staples, CWD, Ti...

Excel automation problems from VB/VBA
Hi, I am using Visual Basic 6 to automate Excel. When the reference to Excel is early bound (bound to Excel at design time), everything works fine, for example e.Range(e.Cells(w, x), e.Cells(y,z)).HorizontalAlignment = xlCenter centers the text in a merged cell. When changing that to early binding, I get errors telling me that the HorizontalAlignment is not part of the e.range class, though other operations work OK. Anyone have any ideas? Cheers, Daniel When changing to late binding???? Maybe it's not .horizontalalignment that's the problem. Maybe it's xlcenter. I'd t...

can not delivery to more than one Recipient
Hi guys, MS Exchange Server (tested on 5.5 and 2003) has a bug ... If you send Messages with long message ids (>189 bytes?)to more than one recipient (cc), the message will not delivered correctly ... there is no correct logging !!, the messages will be delivered to only one Recipient ... the message to the other will be lost !! I have send this issue to Microsoft (10.2003) ... some months later (05.2004) I got the fix, but not public ... store.exe (6.5.6980.81) with some reg settings fixes (workaround ;-) the problem. I have my server Suffering this issue. Any one Know w...

Refresh all anomoly Excel 2010
Hello May I seek the wisdom of this group? I have an XML feed. When I load, it maps properly and shows the correct connection. However when I press refresh all, it goes to an entirely different URL (instead or as well, not sure which) , one that I used in the past, although that old URL is nowhere in my VBA code. It then declares it can't find this URL. I have cleared out all the old connections and saved. How is this old code coming up? Is there some cache I am unaware of? Could it be embedded in the xml itself? Is writing over old jxml maps leaving them behind in some form I&#...

How do I re-download Excel onto my computer (it crashed)?
My computer crashed and I lost everything on it. I need to re-install Excel. How do I do that? sueb262, you will need a CD with excel on it, do you have it from when you got the computer? -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "sueb262" <sueb262@discussions.microsoft.com> wrote in message news:11BF91E6-DB5E-419F-BBE0-8188E61B2FCD@microsoft.com... > My computer crashed and I lost everything on it. I need...