Automatic "Numbering" of unique rows with A,B,C.... or AA,AB,AC... with power query

nahaku

nahaku

Board Regular
Joined
Mar 19, 2020
Messages
106
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello, I would like to create template where when i insert data it woull add something like numbering but with alphabetic symbols.
It should start from A,B,C... if it makes simplier it could start with AA,AB,AC... because on the end of the day it can be that It will need more symbols then is in basic alphabet, so it will need to combine atleast two letters.
The Location column is basically Unique for each sequence of A,B,C, it should be always reseted for new Location, as on the example what i want to achieve.
Name column will be unique but only within the location, it can be same for multiple Names.
Is there some function I could use in power Query or Excel itself? To create this column of IDs.
IC sorting data.xlsx
JKLM
1locationNAMEidID VERSION2
2MSKU8416005V074-UKAN1008358-N12AAA
3MSKU8416005V085-UKATPN1000668-N12BAB
4MSKU8416005V289-UKAN1012044-N10CAC
5MSKU8416005V289-UKAN1012044-N9DAD
6MSKU8416005V002-UKAKKOA131073-N12EAE
7MSKU8416005V009-UKAPLN1011875-N3FAF
8MSKU8416005V263-UKAKKOA147545-N4GAG
9MSKU8416005V286-UKAKKOGN1006376-N5HAH
10MSKU8416005V286-UKAKKOGN1006376-N6IAI
11TCLU6297933R708004AAA
12TCLU6297933R708005BAB
13TCLU6297933R708007CAC
14TCLU6297933R804021DAD
15TCLU6297933R804024EAE
16TCLU6297933R901013FAF
Upload format
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
why alphabet?
with numbers is faster and easier
locationNAMEIndex
MSKU8416005V074-UKAN1008358-N121
MSKU8416005V085-UKATPN1000668-N122
MSKU8416005V289-UKAN1012044-N103
MSKU8416005V289-UKAN1012044-N94
MSKU8416005V002-UKAKKOA131073-N125
MSKU8416005V009-UKAPLN1011875-N36
MSKU8416005V263-UKAKKOA147545-N47
MSKU8416005V286-UKAKKOGN1006376-N58
MSKU8416005V286-UKAKKOGN1006376-N69
TCLU6297933R7080041
TCLU6297933R7080052
TCLU6297933R7080073
TCLU6297933R8040214
TCLU6297933R8040245
TCLU6297933R9010136
 
Upvote 0
sandy666 said:
why alphabet?
with numbers is faster and easier
locationNAMEIndex
MSKU8416005V074-UKAN1008358-N121
MSKU8416005V085-UKATPN1000668-N122
MSKU8416005V289-UKAN1012044-N103
MSKU8416005V289-UKAN1012044-N94
MSKU8416005V002-UKAKKOA131073-N125
MSKU8416005V009-UKAPLN1011875-N36
MSKU8416005V263-UKAKKOA147545-N47
MSKU8416005V286-UKAKKOGN1006376-N58
MSKU8416005V286-UKAKKOGN1006376-N69
TCLU6297933R7080041
TCLU6297933R7080052
TCLU6297933R7080073
TCLU6297933R8040214
TCLU6297933R8040245
TCLU6297933R9010136
Click to expand...
Alphabet because it easier recognizable then numbers in area what we are doing
 
Upvote 0
like here?

locationNAMEID
MSKU8416005V074-UKAN1008358-N12A
MSKU8416005V085-UKATPN1000668-N12B
MSKU8416005V289-UKAN1012044-N10C
MSKU8416005V289-UKAN1012044-N9D
MSKU8416005V002-UKAKKOA131073-N12E
MSKU8416005V009-UKAPLN1011875-N3F
MSKU8416005V263-UKAKKOA147545-N4G
MSKU8416005V286-UKAKKOGN1006376-N5H
MSKU8416005V286-UKAKKOGN1006376-N6I
TCLU6297933R708004A
TCLU6297933R708005B
TCLU6297933R708007C
TCLU6297933R804021D
TCLU6297933R804024E
TCLU6297933R901013F

but imho example from post#2 is better
 
Upvote 0
sandy666 said:
like here?

locationNAMEID
MSKU8416005V074-UKAN1008358-N12A
MSKU8416005V085-UKATPN1000668-N12B
MSKU8416005V289-UKAN1012044-N10C
MSKU8416005V289-UKAN1012044-N9D
MSKU8416005V002-UKAKKOA131073-N12E
MSKU8416005V009-UKAPLN1011875-N3F
MSKU8416005V263-UKAKKOA147545-N4G
MSKU8416005V286-UKAKKOGN1006376-N5H
MSKU8416005V286-UKAKKOGN1006376-N6I
TCLU6297933R708004A
TCLU6297933R708005B
TCLU6297933R708007C
TCLU6297933R804021D
TCLU6297933R804024E
TCLU6297933R901013F

but imho example from post#2 is better
Click to expand...
yes this would be good, does it continue further after it reaches Z ? like Z, AA, AB or something else? as i mentioned there can be even 200 unique pairs
 
Upvote 0
no, this will end with a Z and any subsequent ones from the same series will be empty, that's why I said with numbers is better

locationNAMEIDlocationNAMEIndex
MSKU8416005V074-UKAN1008358-N12AMSKU8416005V074-UKAN1008358-N121
MSKU8416005V085-UKATPN1000668-N12BMSKU8416005V085-UKATPN1000668-N122
MSKU8416005V289-UKAN1012044-N10CMSKU8416005V289-UKAN1012044-N103
MSKU8416005V289-UKAN1012044-N9DMSKU8416005V289-UKAN1012044-N94
MSKU8416005V002-UKAKKOA131073-N12EMSKU8416005V002-UKAKKOA131073-N125
MSKU8416005V009-UKAPLN1011875-N3FMSKU8416005V009-UKAPLN1011875-N36
MSKU8416005V263-UKAKKOA147545-N4GMSKU8416005V263-UKAKKOA147545-N47
MSKU8416005V286-UKAKKOGN1006376-N5HMSKU8416005V286-UKAKKOGN1006376-N58
MSKU8416005V286-UKAKKOGN1006376-N6IMSKU8416005V286-UKAKKOGN1006376-N69
MSKU8416005V074-UKAN1008358-N12JMSKU8416005V074-UKAN1008358-N1210
MSKU8416005V085-UKATPN1000668-N12KMSKU8416005V085-UKATPN1000668-N1211
MSKU8416005V289-UKAN1012044-N10LMSKU8416005V289-UKAN1012044-N1012
MSKU8416005V289-UKAN1012044-N9MMSKU8416005V289-UKAN1012044-N913
MSKU8416005V002-UKAKKOA131073-N12NMSKU8416005V002-UKAKKOA131073-N1214
MSKU8416005V009-UKAPLN1011875-N3OMSKU8416005V009-UKAPLN1011875-N315
MSKU8416005V263-UKAKKOA147545-N4PMSKU8416005V263-UKAKKOA147545-N416
MSKU8416005V286-UKAKKOGN1006376-N5QMSKU8416005V286-UKAKKOGN1006376-N517
MSKU8416005V286-UKAKKOGN1006376-N6RMSKU8416005V286-UKAKKOGN1006376-N618
MSKU8416005V074-UKAN1008358-N12SMSKU8416005V074-UKAN1008358-N1219
MSKU8416005V085-UKATPN1000668-N12TMSKU8416005V085-UKATPN1000668-N1220
MSKU8416005V289-UKAN1012044-N10UMSKU8416005V289-UKAN1012044-N1021
MSKU8416005V289-UKAN1012044-N9VMSKU8416005V289-UKAN1012044-N922
MSKU8416005V002-UKAKKOA131073-N12WMSKU8416005V002-UKAKKOA131073-N1223
MSKU8416005V009-UKAPLN1011875-N3XMSKU8416005V009-UKAPLN1011875-N324
MSKU8416005V263-UKAKKOA147545-N4YMSKU8416005V263-UKAKKOA147545-N425
MSKU8416005V286-UKAKKOGN1006376-N5ZMSKU8416005V286-UKAKKOGN1006376-N526
MSKU8416005V286-UKAKKOGN1006376-N6MSKU8416005V286-UKAKKOGN1006376-N627
MSKU8416005V002-UKAKKOA131073-N12MSKU8416005V002-UKAKKOA131073-N1228
MSKU8416005V009-UKAPLN1011875-N3MSKU8416005V009-UKAPLN1011875-N329
MSKU8416005V263-UKAKKOA147545-N4MSKU8416005V263-UKAKKOA147545-N430
MSKU8416005V286-UKAKKOGN1006376-N5MSKU8416005V286-UKAKKOGN1006376-N531
MSKU8416005V286-UKAKKOGN1006376-N6MSKU8416005V286-UKAKKOGN1006376-N632
TCLU6297933R708004ATCLU6297933R7080041
TCLU6297933R708005BTCLU6297933R7080052
TCLU6297933R708007CTCLU6297933R7080073
TCLU6297933R804021DTCLU6297933R8040214
TCLU6297933R804024ETCLU6297933R8040245
TCLU6297933R901013FTCLU6297933R9010136
 
Last edited:
Upvote 0
Like with vba code you can do this by iterating a variable and setting that variable to a number
such that it will comply with the ascii characters of excel and converting each item to a letter with
char() function.
 
Upvote 0
Yotelc_Z said:
Like with vba code you can do this by iterating a variable and setting that variable to a number
such that it will comply with the ascii characters of excel and converting each item to a letter with
char() function.
Click to expand...
So basically I would need to create table with 1 = A ,2 = B .... 150 = EF .... ? and then replace those Index numbers what Sandy666 created ?
 
Upvote 0
You could use a formula
=SUBSTITUTE(ADDRESS(1,COUNTIFS(J$2:J2,J2),4),1,"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

VBA learner ITG
  • Question
Power Query Advice on Import and Merge Files in a Folder
Replies
2
Views
439
VBA learner ITG
VBA learner ITG
V
  • Question
Power Query data input+changed columns in original file
Replies
4
Views
2K
teylyn
teylyn
I
  • Question
Power Query / DAX solution to Excel formulas (or if this is even appropriate)
Replies
2
Views
2K
IR15H
I
G
  • Question
Unique ID AA AB AC...AAA-> create a new UNID for unique entry
Replies
0
Views
1K
gogeta1998
G
P
  • Question
Sum total unique overlapping days within multiple ranges, based on a condition
Replies
0
Views
437
plshalp
P

Forum statistics

Threads
1,214,649
Messages
6,120,731
Members
448,987
Latest member
marion_davis

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top