Increment a number based on four adjoing cells

[Nolene]

I am using an xlsx spreadsheet for indexing metadata for file folders. Based
on the metadata selected from dropdowns in columns H, I & J, the spreadsheet
builds a file number in the format AA-AA-NN-nnnnn (A=alpha; N=predetermined
number; nnnnn= sequential number).

I'm using the following formulas:
Col B =VLOOKUP(H577,FUNCTIONLU,2)
Col C =VLOOKUP(I577,CATEGORYLU,2)
Col D =VLOOKUP(J577,TYPELU,2)
Col E
=IF(D576="","",IF(COUNTIF($D$2:$D576,$D576)=1,TEXT(0,"00001")+0,IF(COUNTIF($D$2:$D576,D576)>1,INDEX($E$2:$E576,MATCH($D576,$D$2:$D576,0))+COUNTIF($D$2:$D576,D576)-1)))

B, C & D work great. However, Col E should +1 based on all four columns, but
it's only incrementing based on Col D.

Here's some sample data of what should happen:

B C D E
------------------------------------------------
LE EN 01 00001
LE EN 01 00002
LE CO 02 00001
LE EN 01 00003
CO IA 01 00001 <=== My formula is making this 00004
LE CO 02 00002
CO IA 01 00002

Do I have to do some sort of concatenation for it to look at all the fields?

 

[Jacob Skaria]

Hi Nolene

Try the below in cell E2 and copy down as required..

=SUMPRODUCT(($B$2:B2=B2)*(B$2:$C2=C2)*($D$22=D2))


'If you want this as text then try the below
=TEXT(SUMPRODUCT(($B$2:B2=B2)*(B$2:$C2=C2)*($D$22=D2)),"00000")

 

[Nolene]

Thanks, this works fine. However, it takes a really long time to save the
spreadsheet now. If there is a way to speed that up great, otherwise -- since
this is just a stopgap solution -- we'll just have to wait to get a proper
program to do this.