@kna

Can't respond to you in French, sorry. From what understand is that the long formula you have is not giving the correct result. Perhaps the formula I added in the last column of the table does. If not, please show the result you want to achieve in the your file.

Merci Rincy c est bien le résultat recherche et je suis même impressionné par la simplicité de votre raison et je vous en remercie cependant pouvez vous apporter une amélioration à la formule suivante : =SI([@[N° Facture]]<>"";SIERREUR(SI(NB.SI.ENS(INDIRECT("$F$2:F"&LIGNE()-1);$F3;INDIRECT("$B$2:b"&LIGNE()-1);[@Mois])=0;NB.SI.ENS(INDIRECT("$B$2:b"&LIGNE()-1);[@Mois])+1;INDEX(Tableau1[[N° Enreg]:[Libellé]];EQUIV($F3;[N° Facture];0);1));"");SI([@Référence]<>"";SIERREUR(SI(NB.SI.ENS( INDIRECT("$G$2:g"&LIGNE()-1);$G3;INDIRECT("$B$2:b"&LIGNE()-1);[@Mois])=0;NB.SI.ENS( INDIRECT("$B$2:b"&LIGNE()-1);[@Mois])+1;INDEX(Tableau1[[N° Enreg]:[Libellé]];EQUIV($G3;[Référence];0);1));"");"")) qui se trouve dans la colonne N° Enreg afin d obtenir le même résultat. Merci d avance  

Desole Monsieur Riny pour l erreur sur votre nom

@kna 

No worries!

I understand you want an improvement on your the formula you pasted. Though, it's in French and  I'm not sure it's the same as in your file. But why would you want to change a rather complex formula if mine (quite simple) seems to resolve your problem?

@riny En fait c est du à un melange de frustration quand je me rend compte que j ai passé des jours pour quelque chose de si complexe aussi la resolution de cette enigme me permettra de revoir mon approche et mon analyse des situations parce que je veux atteindre un niveau d efficacite similaire au votre ce n est pas encore le cas.

voici un traduction du code en anglais =IF([@[Invoice No.]]<>"";IFERROR(IF(COUNTIFS.SENS(INDIRECT("$F$2:F"&LINE()-1);$F3;INDIRECT("$B$2:b"&LINE()-1);[@Month])=0;COUNTIFS.SENS(INDIRECT("$B$2:b"&LINE()-1);[@Month])+1;INDEX(Table1[[Record No.]:[Label]];EQUIV($F3;[Invoice No.];0);1));"");IF([@Reference]<>"";IFERROR(IF(COUNTIFS.SENS( INDIRECT("$G$2:g"&ROW()-1);$G3;INDIRECT("$B$2:b"&ROW()-1);[@Month])=0;COUNTIFS( INDIRECT("$B$2:b"&ROW()-1);[@Month])+1;INDEX(Table1[[Reg No.]:[Label]];EQUIV($G3;[Reference];0);1));"");""))

@kna 

OK, then that is indeed the formula you had in your file. According to my "translator" your last message reads as follows:

"In fact it is due to a mixture of frustration when I realise that I spent days for something so complex also the resolution of this enigma will allow me to review my approach and my analysis of the situations because I want to reach a level of efficiency similar to yours this is not yet the case."

I don't understand the bolded part. Your current formula is very complicated and I understand that it doesn't return the correct results, but please correct me if I'm wrong.

@riny En fait je veux dire mon souhaite est d être aussi bon et efficace que vous pour l heure je n ai pas encore atteint ce niveau;

Merci pour votre excellente réponse.

aussi je souhaite crée un numéro de pièce compose du code journal BDA du mois 02 et du numéro que vous avez créé 001 002 ...  010. QUAND J ESSAIE AVEC LA FONCTION CONCATENER LES NUMERO ENREGISTREMENT NE FONCTIONNE PLUS CORRECTEMENT

@knAPS

Perhaps like in column T in the attached file??

@riny Bonjour merci pour la réponse cependant pouvez vous m explique la formule dans la colonne T pour ma compréhension.

@kna

Sure. what is does is concatenate the values from three cells using the &-sign and TEXT function to format numbers in a particular way.

The formula (English):

=[@[Code Jl]]&TEXT([@Mois],"00")&TEXT([@Column1],"000")

Step-by step:

[@[Code Jl]] takes the value from the current row (indicated by the @ sign) in column "Code J"

TEXT([@Mois],"00") takes the value from the current row in column "Mois" and converts it into a text, always with two digits. So, a 2 become "02"

TEXT([@Column1],"000") is similar to the previous step, though it converts the number to a three-digit text. Here "Column1" refers to the values in the column I added earlier. In your case you would point it to "N° Enreg".

Joint the three together with an &-sign and you get something like:

"BDA" & "02" & "001" which becomes the text "BDA02001" as shown in column T.

@riny Merci beaucoup Monsieur bon dimanche à vous et plus tard pour le prochain sujet.

@riny Bonjour Monsieur, vu la simplicité avec laquelle vous avez résolu ma difficulté pouvez me donnez la logique algorithmique que vous avez suivie par la même occasion donnez une démarche méthodologique en terme d algo pour Excel que je pourrai utiliser pour résoudre mes problèmes a l avenir.

@kna 

Your question in English:

"Hello Sir, given the simplicity with which you solved my problem can you give me the algorithmic logic that you followed at the same time give a methodological approach in terms of algo for Excel that I can use to solve my problems in the future."

Oh, that's difficult to answer. It's more based on experience than pure logic or a methodical approach. You have to know the functions, have learned about what they do. When you don't know about certain functions, you may end up using the ones you do know, but in a very complicated manner. It might work. Sometimes not. When it does work in a very complicated manner, you should expect that there's probably an easier way. That's typical Excel. Many possible solutions to one single problem. Practice makes perfect! Take a course, search the web, and ask questions on forums like this. After a while you will get a feel for the right solutions.

R