+ Reply to Thread
Results 1 to 3 of 3

IF formula question:create a excel IF formula

  1. #1
    Registered User
    Join Date
    07-18-2008
    Location
    England
    Posts
    9

    IF formula question:create a excel IF formula

    Hi,

    I need to create a excel IF formula that will do the following:

    There will be a list of numbers (e.g. 1,2,3,4,5) in column A in their individual rows and I need to create a formula that adds "_1" or "_2" etc at the end of the numbers if there are duplicates. e.g.

    00000001.tif
    00000002.tif
    00000003.tif
    00000003_1.tif
    00000003_2.tif
    00000004.tif
    00000004_1.tif
    00000005.tif

    I have created the following formula:

    =IF(A2=A1,CONCATENATE(LEFT(A2,8),"_1",".tif"),A2)

    This works fine if there is only one duplicate but I want to use _2 if _1 already exists and _3 if _2 already exists etc.

    Please help

  2. #2
    Registered User
    Join Date
    07-10-2008
    Location
    India
    Posts
    17
    Data in Column A
    B1=IF(COUNTIF(A$1:A1,A1)>1,CONCATENATE(A1,"_",COUNTIF(A$1:A1,A1)-1),A1)
    Best wishes
    Sreedhar

  3. #3
    Registered User
    Join Date
    07-18-2008
    Location
    England
    Posts
    9
    Ahh amazing thanks =]!

+ 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