Delete Duplicates based on rule

[hzarry]

Hi I have the following data set and I want a rule that will strip out all the duplicates and give a single row per account where the status will be the first instance of a code number but if there is no code number present, then the #N/A value is to be given. For instance, I want this data set:


Account Code
CH0002 #N/A
CH0002 #N/A
CH0002 #N/A
CH0002 #N/A
DUB01 #N/A
DUD01 #N/A
DUD01 #N/A
DUD01 #N/A
DUD01 Zx7Zx4IC
DUD01 #N/A
DUD01 #N/A
EM302 #N/A
EM302 #N/A
EM302 #N/A
HS001 #N/A
HS002 #N/A
HS002 #N/A
HS002 #N/A
PC1017 Zx3x8xIL
PC1017 Zx3x9K6J
PC1017 Zx3x4LYU
PC1020 #N/A
PC1020 #N/A

To look like this:

Account Code
CH0002 #N/A
DUD01 Zx7Zx4IC
EM302 #N/A
HS001 #N/A
HS002 #N/A
PC1017 Zx3x8xIL
PC1020 #N/A



Any help would really be appreciated.
Thanks
T

[simcode]

Hi, hzarry

Maybe like this