+ Reply to Thread
Results 1 to 11 of 11

How to assign numbers to a list of addresses

  1. 04-05-2017, 06:38 PM #1
    suvamg
    suvamg is offline
    Registered User
    Join Date
    04-05-2017
    Location
    Melbourne
    MS-Off Ver
    2010
    Posts
    6

    How to assign numbers to a list of addresses

    Hi,
    I am trying to assign numbers to a series of addresses but having trouble to come up with a dynamic formula.

    I have used the following formula but it means that I have to type in the addresses in the formula.

    =MATCH(E6:E15,{"66 COLLIER RD","1 STURT VALLEY RD","19 MITCHELL CT","1 TANTIVY ST","75 BALRANALD St","3 ROCHESTER DR",""},0)

    Address Assigned number
    66 COLLIER RD 1
    1 STURT VALLEY RD 2
    1 STURT VALLEY RD 2
    19 MITCHELL CT 3
    1 TANTIVY ST 4
    75 BALRANALD St 5
    75 BALRANALD St 5
    3 ROCHESTER DR 6
    3 ROCHESTER DR 6
    66 COLLIER RD 1
    1 STURT VALLEY RD 2
    1 STURT VALLEY RD 2
    75 BALRANALD St 5

    It there a better way of doing this? Please help.
    Last edited by suvamg; 04-05-2017 at 06:42 PM.
    Reply With QuoteRegister To Reply
  2. 04-05-2017, 06:47 PM #2
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: How to assign numbers to a list of addresses

    you can try: =INDEX($H$1:$H$6,MATCH(A2,$G$1:$G$6,0))
    you don't need type manually addresses in the formula, copy range with addresses, paste into any blank cell, DATA-Remove duplicates, then select any blank cell, type there: = , next select range with no duplicates, , go to formula bar, select range, press F9, copy all without = sign, paste it to the match()
    =MATCH(A2,{"66 COLLIER RD";"1 STURT VALLEY RD";"19 MITCHELL CT";"1 TANTIVY ST";"75 BALRANALD St";"3 ROCHESTER DR"},0)
    see att.
    Attached Files Attached Files
    Last edited by sandy666; 04-05-2017 at 06:57 PM.
    Reply With QuoteRegister To Reply
  3. 04-05-2017, 07:58 PM #3
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,728

    Re: How to assign numbers to a list of addresses

    With your addresses in cells from E6 downwards, as your formula suggests, you can put this formula in cell F6:

    =IF(COUNTIF(E$6:E6,E6)=1,MAX(F$5:F5)+1,VLOOKUP(E6,$E$5:$F5,2,0))

    then copy down to the bottom of your list, as shown in the attached file.

    Hope this helps.

    Pete
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 04-05-2017, 08:30 PM #4
    suvamg
    suvamg is offline
    Registered User
    Join Date
    04-05-2017
    Location
    Melbourne
    MS-Off Ver
    2010
    Posts
    6

    Re: How to assign numbers to a list of addresses

    Thanks sandy666 for the reply. For some reason I cant seem to download the attached file.
    Reply With QuoteRegister To Reply
  5. 04-05-2017, 08:33 PM #5
    suvamg
    suvamg is offline
    Registered User
    Join Date
    04-05-2017
    Location
    Melbourne
    MS-Off Ver
    2010
    Posts
    6

    Re: How to assign numbers to a list of addresses

    Thanks Pete_UK. This worked like a charm.
    Reply With QuoteRegister To Reply
  6. 04-05-2017, 08:45 PM #6
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,728

    Re: How to assign numbers to a list of addresses

    You're welcome - thanks for the rep.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

    Pete
    Reply With QuoteRegister To Reply
  7. 04-06-2017, 12:56 AM #7
    suvamg
    suvamg is offline
    Registered User
    Join Date
    04-05-2017
    Location
    Melbourne
    MS-Off Ver
    2010
    Posts
    6

    Re: How to assign numbers to a list of addresses

    Hi Pete_UK

    I have tried to replicate the formula for a larger data set and I am having trouble in making the formula work. Is there something that I need to do if the data set is more than 10000 addresses?
    Reply With QuoteRegister To Reply
  8. 04-06-2017, 05:08 AM #8
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,728

    Re: How to assign numbers to a list of addresses

    It should work - what kind of problem(s) are you having?

    Pete
    Reply With QuoteRegister To Reply
  9. 04-06-2017, 07:20 PM #9
    suvamg
    suvamg is offline
    Registered User
    Join Date
    04-05-2017
    Location
    Melbourne
    MS-Off Ver
    2010
    Posts
    6

    Re: How to assign numbers to a list of addresses

    In my spread sheet I have the addresses in column P and the assigned numbers in column BB. I have modified the formula as follows.

    =IF(COUNTIF(P$11:P11,P11)=1,MAX(BB10:BB$11)+1,VLOOKUP(P11,$P$10:$BB10,39,0))

    But its coming up with a circular reference and when I drag the formula to the bottom it starts assigning the same value to different addresses.
    Reply With QuoteRegister To Reply
  10. 04-06-2017, 07:26 PM #10
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,728

    Re: How to assign numbers to a list of addresses

    The bit in the middle should be this:

    ... MAX(BB$10:BB10)+1 ...

    and I assume that you have the formula in cell BB11.

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  11. 04-06-2017, 09:32 PM #11
    suvamg
    suvamg is offline
    Registered User
    Join Date
    04-05-2017
    Location
    Melbourne
    MS-Off Ver
    2010
    Posts
    6

    Re: How to assign numbers to a list of addresses

    Thanks Pete. At first glance it looks like its working. Cheers!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Assign sequential numbers to unique values in a list
    By penfold in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-30-2017, 02:11 PM
  2. Need a macro that will help me cleanse a list of physical addresses and phone numbers
    By Vertical Media in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-28-2015, 07:10 PM
  3. identify and assign addresses
    By Akela928 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-17-2015, 11:03 PM
  4. Create a list of Email addresses from selecting contacts in Outlook's global contact list
    By nimrod77 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-29-2014, 07:30 AM
  5. Sort odd and even numbers of addresses
    By unley in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-31-2013, 01:45 AM
  6. [SOLVED] Assign incremental numbers to matching Invoice numbers
    By berger01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2013, 10:54 AM
  7. Converting list of IP Addresses to list of Hostname
    By M. Eteum in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-23-2006, 02:20 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1