+ Reply to Thread
Results 1 to 4 of 4

Giving each row in a spreadsheet a unique ID, but giving repeating rows the same ID

  1. #1
    Registered User
    Join Date
    11-05-2013
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    3

    Giving each row in a spreadsheet a unique ID, but giving repeating rows the same ID

    Hi there,
    I'm working on a spreadsheet for a hotel where I have 1500 (ish) Different customers.
    I want to give each customer a unique number to identify them.
    However, some customers appear more than once, where all the fields in the row are the same as previous row, so I'd like to give the repeating rows the same number to avoid confusion.

    I'm not very good with Excel and this has been driving me insane trying to work out so any help would be very much appreciated!

    Many Thanks!

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Giving each row in a spreadsheet a unique ID, but giving repeating rows the same ID

    welcome to the forum, gavlaar69. how do you want it to look like? assuming company name starts in A2 & if you just want numbers, maybe:
    =IF(COUNTIF(A$2:A2,A2)=1,SUMPRODUCT(1/(COUNTIF(A$2:A2,A$2:A2))),VLOOKUP(A2,A$1:B1,2,0))

    if you want a fix number of digits like 4 maybe to start with preceding zeroes, then:
    =TEXT(IF(COUNTIF(A$2:A2,A2)=1,SUMPRODUCT(1/(COUNTIF(A$2:A2,A$2:A2))),VLOOKUP(A2,A$1:B1,2,0)),"0000")

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    11-05-2013
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Giving each row in a spreadsheet a unique ID, but giving repeating rows the same ID

    That's almost it, but I want it to check several columns are the same in each row.
    for example:
    First name Last name Phone Address Town Post code email

    I tried to adapt your solution to make it fit but it still isn't working. It's also displaying the name of the repeated name rather than displaying the ID of the Repeated value

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Giving each row in a spreadsheet a unique ID, but giving repeating rows the same ID

    Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

    Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] INDEX giving identical values for diferent rows with different information
    By thmsjlmnt in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-10-2013, 03:34 PM
  2. Giving them what they ask for vs giving them what they want
    By Solus Rankin in forum The Water Cooler
    Replies: 3
    Last Post: 07-05-2013, 07:09 AM
  3. Replies: 1
    Last Post: 02-17-2013, 10:57 AM
  4. Replies: 1
    Last Post: 11-01-2012, 05:34 AM
  5. autofilter giving me jip??!
    By cashflowpro in forum Excel General
    Replies: 1
    Last Post: 04-17-2008, 11:48 AM

Tags for this Thread

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