+ Reply to Thread
Results 1 to 13 of 13

Schematic postcode map in excel ?

  1. #1
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London, England
    MS-Off Ver
    EXCEL 2013
    Posts
    320

    Schematic postcode map in excel ?

    Hey Guys

    sorry for posting out of the blue, but looking at indivudual forums, this seems best suited from the answers provided

    i would like to produce the following map (http://mappery.com/map-of/Schematic-UK-Postcode-Map) in excel, when i run a particular query, for eg provide top 5 travel merchants within the uk, i would like pins to flag within the uk map through a vba code.

    is this viable ? is so where do i begin

  2. #2
    Forum Contributor
    Join Date
    10-18-2012
    Location
    Telford, England
    MS-Off Ver
    Excel 2010 (2003)
    Posts
    294

    Re: Schematic postcode map in excel ?

    Yes I'm sure it can be done. Interesting project.
    What kind of pins? Presumably a graphic symbol that appears in front? What if the top five are all in the same postcode?
    And also I guess that if Scroggin's Mega Travel is at TF2 9TZ, you'd want a flag close to TF square.
    Had you thought of using a comment (they do not have to be rectangular)?

  3. #3
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,146

    Re: Schematic postcode map in excel ?

    I think that Bryn's suggestion about comments is an interesting one. The other way is just to put an image of a pin in the correct cells, but it becomes a little more fiddly if you have more than one as bryn has stated.

    I'd be tempted to keep things simple and colour the cells depending on the number of hits (maybe conditional formatting). You could also add comments like bryn has suggested or add pin graphics to the actual cell

  4. #4
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London, England
    MS-Off Ver
    EXCEL 2013
    Posts
    320

    Re: Schematic postcode map in excel ?

    Hi guys

    Byrn you raise an important issue. The possibility of multiple travel customers in the same geographical area are slim, But it could happen. Ideally i would like a flag to populate with each individual travel customer

    And tbh I haven’t thought about pin types. I just wasn’t sure, whether it was viable to do in excel.

    From listening to you two guys it seems that it can be done. But where do I start ?
    I tried searching for answers, but haven’t had much luck 
    can you two point me in the correct direction

  5. #5
    Registered User
    Join Date
    03-30-2011
    Location
    Northampton, England
    MS-Off Ver
    MS Office 2010 Professional Plus
    Posts
    14

    Re: Schematic postcode map in excel ?

    Hi,

    Could you not put a countif in the cells that count from a list of 5 post codes and use some kind of font or another if to say if greater than 1?

    Dumb suggestion!

  6. #6
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London, England
    MS-Off Ver
    EXCEL 2013
    Posts
    320

    Re: Schematic postcode map in excel ?

    scottmoss - how am i going to reference the cells ? do i need to cut them out , name each postcode as a range, and reference that range in the background ?

  7. #7
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London, England
    MS-Off Ver
    EXCEL 2013
    Posts
    320

    Re: Schematic postcode map in excel ?

    --Bump Thread--

  8. #8
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London, England
    MS-Off Ver
    EXCEL 2013
    Posts
    320

    Re: Schematic postcode map in excel ?

    == Bump thread ==

  9. #9
    Forum Contributor
    Join Date
    10-18-2012
    Location
    Telford, England
    MS-Off Ver
    Excel 2010 (2003)
    Posts
    294

    Re: Schematic postcode map in excel ?

    OK, I have constructed the mechanism and attached a spreadsheet.
    I entered only the wide line starting with Swansea.
    It assumes you can place the first two characters of the winners in the range "Choice", at top left.

    You might want to move the named range Choise somewhere else.
    I flag with blobs (1-5) the bottom of each post code cell.
    It should be the start you need.

    Complete sheet in a moment
    Attached Files Attached Files
    Last edited by brynbaker; 11-08-2012 at 07:42 PM. Reason: completed

  10. #10
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London, England
    MS-Off Ver
    EXCEL 2013
    Posts
    320

    Re: Schematic postcode map in excel ?

    Hi

    thank you for you post, and providing a work sheet

    looking at the doc, it looks like you created three separate lines to form one box ?
    for ex
    1) postcode
    2)city/town
    3) formula

    can you explain to me what the first part of that formula does ?

    also how did you get the red dots to appear ?

  11. #11
    Forum Contributor
    Join Date
    10-18-2012
    Location
    Telford, England
    MS-Off Ver
    Excel 2010 (2003)
    Posts
    294

    Re: Schematic postcode map in excel ?

    postcodes 2.xls

    Completed.

    REPT is a neat graphical tool that simply repeats the character in his case one that in Windings shs as a blob.
    We reapt according to how often the postcode appears.

  12. #12
    Forum Contributor
    Join Date
    10-18-2012
    Location
    Telford, England
    MS-Off Ver
    Excel 2010 (2003)
    Posts
    294

    Re: Schematic postcode map in excel ?

    I had another look at the result and opine that:
    • it is too squat with three rows per box
    • The town names are squashed
    So I tried a different approach (actually two of them)
    In this preferred example, the Postcode prefix gains a (1) [etc] and goes red to indicate the flag.It also separates the data from the function, placing the original prefixes in hidden cells.
    I hope you like it - if you, please click my star!
    postcodes 3.xls

  13. #13
    Registered User
    Join Date
    11-03-2016
    Location
    Bradford, England
    MS-Off Ver
    2013
    Posts
    1

    Re: Schematic postcode map in excel ?

    I appreciate this thread is a bit old but on looking for something similar to this I couldn't find anything better so I thought I'd share how I'd customised it to make it more interactive.
    If anyone can provide any suggestions for improvements they will be greatly appreciated. I'm not an expert in Excel so please be kind!
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1