Help with adding unique codes to numbers

[Cruiser69]

Hi all.
Just joined and would like to know if anyone could please answer a query I have.
I have a sheet with multiple pallet codes in column A as in the example below.
One pallet number could contain up to 500 items which can all be different.

123456
123456
123456
111222
111222
222112
222112

Is it possible to add a two letter code of my choice to column B as below

123456 BA
123456
123456
111222
111222
222112
222112

Then for it to autofill down and when it gets to the next unique number, it will change as below
123456 BA
123456 BA
123456 BA
111222 BB
111222 BB
222112 BC
222112 BC

I'm not sure if this is possible, but I'm sure one of you experts out there will let me know either way.


Thanks for looking,

Graham

 

[mumps]

How would you decide on which 2 letter code to assign to which unique number?

 

[Cruiser69]

I would just filter the pallet numbers from low to high, then give the first one the unique number

I should have given a better example as below

111111 BA
111111 BA
111111 BA
222222 BB
222222 BB
222222 BB
333333 CC
333333 CC

Graham

 

[Eric W]

Maybe:

	A	B
Pallet	Code
BA
BA
BA
BB
BB
BC
BC

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]123456[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]123456[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]123456[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]111222[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]111222[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]222112[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]222112[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=IF(A3=A2,B2,LEFT(ADDRESS(1,COLUMN(INDIRECT(B2&1))+1,4),2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Put the 2-letter code you want in B2, then the formula in B3 and drag down.

 

[Cruiser69]

Hi Eric.
All I am getting is what you see below

111111 BA BA
111111 0
111111 #REF !
222222 0
222222 0
222222 0
333333 #REF !
333333 0

Graham

 

[Cruiser69]

Sorry Eric, my bad. I didn't read you answer correctly. I was adding the formula to column C

This works a treat. Thanks for your help

Graham

 

[Eric W]

Glad to help.