Excel Quick Question

Discussion in 'OT Technology' started by Voltekker, Jul 16, 2008.

  1. Voltekker

    Voltekker Active Member

    Joined:
    Jan 15, 2004
    Messages:
    8,095
    Likes Received:
    0
    Im working on areport that the first column is employee names and each time they provide/sell a service its one line so for example.Each new row represents a service that has been performed and billed for. I wanted to use somthing to turn every john in to Employee 1 and ever doe into employee 2 with out having to making table for v look up. I was thing of a complicated type of autofill. Everytime the employee name is changed excel changes the name to the next succession. Is this possible :?

    John
    John
    John
    John
    John
    John
    Doe
    Doe
    Doe
    Doe
    Doe
    Doe
     
  2. trouphaz

    trouphaz New Member

    Joined:
    Sep 22, 2003
    Messages:
    2,666
    Likes Received:
    0
    Wirelessly posted via wap.offtopic.com (Mozilla/2.0 (compatible; MSIE 3.02; Windows CE; PPC; 240x320) BlackBerry8703e/4.1.0 Profile/MIDP-2.0 Configuration/CLDC-1.1 VendorID/105)

    Hmm. I can't picture what you want to do. Can you just post a small spreadsheet showing what you want? How about a screen cap?
     
  3. EkriirkE

    EkriirkE Zika Xenu OT Supporter

    Joined:
    Jan 11, 2004
    Messages:
    14,799
    Likes Received:
    0
    Location:
    Dublin & San Francisco, CA
    for my method to work you need a header row, or the data to start after row 1 somewhere... and the data to be sorted by name

    lets assume A = name, B = ID and data starts on row 2
    the formula (in B2 and down) would be

    =IF(A2=A1,A1,A1+1)




    "If the current name is the same as the name above then use the ID from above, otherwise use the above ID+1"

    if you don't want a number on the blanks, just wrap another if around it

    =IF(A2,IF(A2=A1,A1,A1+1),"")
     
  4. cmsurfer

    cmsurfer ºllllllº

    Joined:
    Jun 6, 2003
    Messages:
    5,079
    Likes Received:
    0
    Location:
    NJ
    ^^ Damn, you seem to know Excel pretty well ;)
     
  5. Voltekker

    Voltekker Active Member

    Joined:
    Jan 15, 2004
    Messages:
    8,095
    Likes Received:
    0
    Thank you for the quick response. This works but only if I have a limited number of names. Heres what im trying to do, I work in healthcare and I recieve a revenue report every month. I was going to contract out a build but I needed to make an example of the revenue report. I wanted to change the name of each physician into Physician 1,2 etc. Each time the physician bills out a procedure it represents one line in the report. When im doing it I use the subtotal feature to count it all. At each change in physician, subtotal MTD and YTD numbers. Basically I want to tell excel to change every physicians names to a generic one and at each change in physician go to the next part of sequence. Let me know if this is crazy.
     
  6. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    I'm missing it - it works with an infiinite number of names, and more the the point, you shouldn't need to change the name anyway if they're unique, just sort the list first and then subtotal on each change of the name.
     

Share This Page