Consulting

Results 1 to 2 of 2

Thread: Help about sorting rows and assigning a new field automatically?

  1. #1
    VBAX Newbie
    Joined
    Apr 2016
    Posts
    2
    Location

    Help about sorting rows and assigning a new field automatically?

    Hi there.

    I need a favor. I have an excel file which i use at work. I have lots of customers in there and i sort the customer names and add a new column and give them "ID Numbers" per each (The same names get the same ID's)

    So Its ok to do this while the list is small but when the list is more then 100 rows then sometimes errors occur..

    Is there a way for me to open a new column and make excel write Customer1, customer2 and so on? just like the example below. (with VB or not)


    Thank you very much.

    Sabi RAZON


    John Person1
    John Person1
    John Person1
    Sally Person2
    Sally Person2
    Andy Person3
    Andy Person3
    Paul Person4
    John Person1
    Sally Person2

  2. #2
    VBAX Newbie
    Joined
    Apr 2016
    Posts
    2
    Location
    I did find a way at last, to make it out doing lots of lots research.

    There may be some guys like me (newbe) who might be asking for help so i decided to also write down how i solved my problem. So this post could be useful for anyone who is looking for help


    Here is my Table

    ItemNumber UserName Product Details
    1 ............. Tony
    2 ..............Badtrip
    3 ..............Zoey
    4 ..............Marc
    5 ............. Badtrip
    6 ............. Marc
    7 ..............Marc
    8 ............. Tony


    So the table that i'm getting from my boss is ItemNumber sorted. I'm sorting that table alphabetically using the username column!
    The item number is all mixed and is really not important at all.


    (Next steps shown in the below table)

    After its all sorted i'm adding a new Index column with a simple formula in it

    INDEX
    =IF(B8<>B7;L7+1;L7)

    With this formula i'm checking if the UserName in the row if it is different then the name above, if yes then i'm adding 1 to the index, if its the same name then index number stays the same.
    And at the same time i added a new column for the PersonID. The formula representing these cells are just combining "Person" and Index Values.

    PersonID
    ="Persons"&-L8

    A B C L
    ItemNumber UserName PersonID Product Details INDEX
    2 .............. Badtrip .................................... 1
    5 ...............Badtrip .................................... 1
    6 ...............Marc ........................................2
    7 ...............Marc ........................................2
    1 ...............Tony ........................................3
    8 ...............Tony ....................................... 3
    3 .............. Zoey ........................................4



    And this is what i was looking for...
    Hope it helps to anyone else looking for like i did.




    ItemNumber UserName UserID Product Details INDEX
    2...............Badtrip ....Person1 ...................1
    5...............Badtrip ....Person1 ...................1
    6...............Marc .......Person2 ...................2
    7...............Marc .......Person2 ...................2
    1...............Tony .......Person3 ...................3
    8...............Tony .......Person3 ...................3
    3...............Zoey .......Person4 ...................4
    Last edited by badtrip; 09-28-2020 at 05:40 AM.

Posting Permissions

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