View Full Version : Help about sorting rows and assigning a new field automatically?
badtrip
09-17-2020, 02:46 AM
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
badtrip
09-28-2020, 05:23 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.