How to fix this database?

[kmprice710]

I am using MIcrosoft Office Professional Plus 2019

I have a spreadsheet on which every client has a unique numeric ID code. Then the client also has an alphanumeric ID code. Each numeric ID code is supposed to have only one alphanumeric ID code, but I found hundreds of mistaken alphanumeric ID codes.

I arranged the bad data on this sheet shown in the screenshot by sorting the numeric ID code. You can see the first numeric ID code, 1454952, has two alphanumeric ID codes associated, JB0058305 and JB0160111. I need each row of numeric ID codes to have only one alphanumeric ID code and the alphanumeric ID code must have the "lower" or "smaller" number.

In other words, 58305 is smaller than 160111 so the correct alphanumeric ID code is JB0058305. I need to put the correct alphanumeric ID code in column C.

How would I do this?

 

[myall_blues]

Is there always only two characters at the beginning of the alphanumeric code?

 

[kmprice710]

Is there always only two characters at the beginning of the alphanumeric code?

Yes, the first two initials of the client's name.

 

[myall_blues]

I'd probably tackle this in a couple of steps. You can omit step 1 if you're sure that column B is always sorted such that the 'incorrect' codes are at the end of each sequence, but I did it for certainty.

Step 1:
- Add a helper column that removes the first two characters from the alphanumeric ID using the formula =MID(B2,3,99) and copied down.
- Sort the data by numeric ID, ascending and Helper ascending. In your test data it didn't make a difference but just to be sure.
Step 2:
- In D3 add the formula =IF(A3=A2,B2,B3) and copy it down to the end.

See below.

Book1
	A	B	C	D
1	numeric ID	alphanumeric ID	Helper	final alpha ID
2	1454952	JB0058305	0058305
3	1454952	JB0058305	0058305	JB0058305
4	1454952	JB0160111	0160111	JB0058305
5	1458134	HK0043783	0043783	HK0043783
6	1458134	HK0043783	0043783	HK0043783
7	1458134	HK0158253	0158253	HK0043783
8	1461842	JW0057129	0057129	JW0057129
9	1461842	JW0057129	0057129	JW0057129
10	1461842	JW0159970	0159970	JW0057129
Sheet1

Cell Formulas
Range	Formula
C2:C10	C2	=MID(B2,3,99)
D3:D10	D3	=IF(A3=A2,B2,B3)

Regards

Murray

 

[kmprice710]

I'd probably tackle this in a couple of steps. You can omit step 1 if you're sure that column B is always sorted such that the 'incorrect' codes are at the end of each sequence, but I did it for certainty.

Step 1:
- Add a helper column that removes the first two characters from the alphanumeric ID using the formula =MID(B2,3,99) and copied down.
- Sort the data by numeric ID, ascending and Helper ascending. In your test data it didn't make a difference but just to be sure.
Step 2:
- In D3 add the formula =IF(A3=A2,B2,B3) and copy it down to the end.

See below.

Book1
	A	B	C	D
1	numeric ID	alphanumeric ID	Helper	final alpha ID
2	1454952	JB0058305	0058305
3	1454952	JB0058305	0058305	JB0058305
4	1454952	JB0160111	0160111	JB0058305
5	1458134	HK0043783	0043783	HK0043783
6	1458134	HK0043783	0043783	HK0043783
7	1458134	HK0158253	0158253	HK0043783
8	1461842	JW0057129	0057129	JW0057129
9	1461842	JW0057129	0057129	JW0057129
10	1461842	JW0159970	0159970	JW0057129
Sheet1

Cell Formulas
Range	Formula
C2:C10	C2	=MID(B2,3,99)
D3:D10	D3	=IF(A3=A2,B2,B3)

Regards

Murray

Hi Murray,

Thanks for responding. That worked for the MOST part but not always. When I got down to situations in which there were four or more instances of the numeric ID code, then I had problems. See screenshot.

How can I avoid these errors?

 

[Alex Blakenburg]

If you are happy to stick with sorting by Column A and then by the Helper column, the you can try this.
Put it in D2 and copy it down.

=IF(A2<>A1,B2,D1)