Continuing abnormal series in column

%3CLINGO-SUB%20id%3D%22lingo-sub-2712242%22%20slang%3D%22en-US%22%3EContinuing%20abnormal%20series%20in%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2712242%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20continue%20an%20abnormal%20series.%20Generally%2C%20it%20is%20easy%20to%20click%20and%20drag%20to%20continue%20a%20series%20in%20the%20same%20column%2C%20but%20when%20I%20am%20trying%20to%20do%20the%20same%20technique%20with%20this%20series%2C%20the%20newly%20filled%20cells%20simply%20repeat%20the%20selected%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20my%20series%20to%20be%20in%20the%20same%20column%2C%20as%20follows%3A%20EBL21-001-A%2C%20EBL21-001-B%2C%20EBL21-001-C%2C%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20clarify%2C%20if%20I%20were%20to%20type%20in%20the%20first%20three%2C%20highlight%20them%2C%20then%20drag%20down%2C%20the%20result%20would%20be%20a%20repetition%20of%20...A%2C...B%2C...C%2C...A%2C...B%2C..C%2C...A%2C...B%2C....C.%20I%20would%20like%20it%20to%20continue%20as%20A%2CB%2CC%2CD%2CE%2C...%2CZ%2CAA%2CAB%2CAC%2C%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20figure%20that%20I%20need%20to%20format%20the%20cell(s)%20with%20a%20custom%20number%20format%20but%20I%20can%20not%20get%20it%20to%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2712242%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2712508%22%20slang%3D%22en-US%22%3ERe%3A%20Continuing%20abnormal%20series%20in%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2712508%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1144726%22%20target%3D%22_blank%22%3E%40MarcR10%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20your%20data%20is%20in%20A%3AA%2C%20I%20would%20create%20a%20helper%20column%20to%20track%20new%20occurrences.%20In%20B1%2C%20try%20%3DIF(COUNTIF(%24A%241%3A%24A1%2CA1)%3D1%2CA1%2C%22%22)%2C%20then%20in%20C1%2C%26nbsp%3B%3DFILTER(Z%3AZ%2CZ%3AZ%26lt%3B%26gt%3B%22%22).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EB%20column%20will%20list%20every%20new%20code%2C%20then%20the%20formula%20in%20C%20will%20filter%20out%20all%20the%20unneeded%20blanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20just%20want%20the%20last%20digit%2C%20create%20a%20second%20column%20using%20%3DRIGHT(A1%2C1)%20and%20drag%20down.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello,

 

I am trying to continue an abnormal series. Generally, it is easy to click and drag to continue a series in the same column, but when I am trying to do the same technique with this series, the newly filled cells simply repeat the selected cells.

 

I would like my series to be in the same column, as follows: EBL21-001-A, EBL21-001-B, EBL21-001-C, etc.

 

To clarify, if I were to type in the first three, highlight them, then drag down, the result would be a repetition of ...A,...B,...C,...A,...B,..C,...A,...B,....C. I would like it to continue as A,B,C,D,E,...,Z,AA,AB,AC, etc.

 

I figure that I need to format the cell(s) with a custom number format but I can not get it to work.

 

Thanks for the help.

2 Replies

@MarcR10 

Assuming your data is in A:A, I would create a helper column to track new occurrences. In B1, try =IF(COUNTIF($A$1:$A1,A1)=1,A1,""), then in C1, =FILTER(Z:Z,Z:Z<>"").

 

B column will list every new code, then the formula in C will filter out all the unneeded blanks.

 

If you just want the last digit, create a second column using =RIGHT(A1,1) and drag down. 

@MarcR10 

Do you mean that you want to have a auto running number, from 001 to 999?

 

="EBL"& RIGHT("00"&(21+FLOOR.MATH((ROW(A1)-1)/(999*3))),2) & "-" & 
 RIGHT("000"& (MOD(INT((ROW(A1)-1)/3),999)+1),3) & "-" & 
 IF(MOD(ROW(A1),3)=1,"A",IF(MOD(ROW(A1),3)=2,"B","C"))

 

Note:

1) you may replace 999*3 with 2997

2) after row 2997, it will be EBL22-001-A, EBL22-001-B, EBL22-001-C, ...etc 

3) copy and paste the formula to the row you need (after that, you may copy the cell and paste with text to fix the value)