erreur formule suite a modification entre du numerique et du texte

[GHISLAIN]

bonjour a tous ,

en cherchant a contourner un probleme avec le 0 numerique j ai transformé des valeurs en format numerique en format texte .

effectivement les exceliens m'ont appris que le 0 devant un chiffre ex: 0123 devient en numerique123 et vu que j ai besoin de ce 0 je dois utiliser du format texte . Ce 0 en numerique n est pas compte en nombre de caracteres

du coup une de mes formules ne fonctionne plus et je ne sais comment l'adapter pour qu'elle prenne en compte le format texte.

cette formule sert a recuperer des donnees dans des colonnes suivant une selection de liste de choix :

=SI(SOMMEPROD((TITREA=$O$3)*(Result=$O$4);DECALER(Result;LIGNES(P$2929);0))=0;"";SOMMEPROD((TITREA=$O$3)*(Result=$O$4);DECALER(Result;LIGNES(P$2929);0)))

ma premiere question , comment modifier cette formule pour que soit pris en compte le format texte SANS REPASSER PAR DU NUMERIQUE .


seconde question cette formule recupere les donnees d une seule colonne par rapport a la selection des listes de choix , serais t il possible de l ameliorer dans le sens ou si , plusieurs colonnes contiennent en entete les valeurs des liste de choix , de recuperer toute les colonnes dans une seule triée

ci joint un fichier plus explicite du resultat attendu

amicalement

ghislain

 

[R@chid]

Re : erreur formule suite a modification entre du numerique et du texte

Bonjour Ghislain,
si j'ai bien compris,
en P6 :

=DECALER($B$6;LIGNES($6:6);EQUIV($O$3&$O$4;LigneVerte&Result;0))

@ valider par Ctrl+Maj+Entree
@ tirer vers le bas

Mais je voie la colonne Bleu284 deux fois, il faut preciser, pas claire....
Amicalement

 

[GHISLAIN]

Re : erreur formule suite a modification entre du numerique et du texte

bonsoir Rachid_0661
un grand merci pour ta formule qui fonctionne en validant par ctrl maj et entree en matricielle
pour ta question je cherche a savoir si dans mon tableau on retrouve plusieurs fois bleu 284 si une formule pouvait recuperer les valeur trouvé de toute les colonnes repondant a ce critere
si oui que c est donnees soient recuperées dans une seule colonne et trié du plus petit au plus grand



ex:
c0lonne c en cel c5 = bleu et en cel c6 =284 valeur a recuperer a partir de la ligne7=1 ligne8=2 la ligne9=3

c0lonne d en cel c5 = bleu et en cel c6 =284 valeur a recuperer a partir de la ligne7=4 ligne8=5 la ligne9=6


resultat attendu en colonne t


c0lonne T les valeurs ainsi recuperer en ligne12=1 ligne13=2 la ligne14=3 ligne15=4 ligne16=5 la ligne17=6
si possible trié

en esperant avoir etait plus explicite

cordialement

ghislain






ligne

 

[R@chid]

Re : erreur formule suite a modification entre du numerique et du texte

Bonsoir GHISLAIN,
Definir un champ : MaPlage ==>

=DECALER(Feuil1!$B$7:$B$25;;EQUIV(Feuil1!$O$3&Feuil1!$O$4;Feuil1!$C$5:$I$5&Feuil1!$C$6:$I$6;0);;SOMMEPROD(N((Feuil1!$C$5:$I$5=Feuil1!$O$3)*(Feuil1!$C$6:$I$6=Feuil1!$O$4))))

et en P6 :

=SI(LIGNES($6:6)<=NBVAL(MaPlage);PETITE.VALEUR(SI(MaPlage<>"";CNUM(MaPlage));LIGNES($6:6));"")

@ valider par Ctrl+Maj+Entree
@ tirer vers le bas
Amicalement

 

[GHISLAIN]

Re : erreur formule suite a modification entre du numerique et du texte

bonsoir Rachid_0661,

apres avoir testé ta proposition , elle ne correspond pas exactement a ce que je souhaite.
effectivement ta formule reunie les colonnes si celle ci sont adjacentes l'une de l autre .
Ce que je souhaite c est que toutes les colonnes repondant aux choix soient reuni dans une seule ;
dans l exemple si la selection est bleu 284 est colonne C, D et J que dans ma colonne P soient reunies les valeurs de 001 a 031, 37 a 63 et 253 a 257 .


autre exemple si ma selection est TILT 287 le resultat attendu en colonne p devrai etre de 145 a 180 et de 217 a 241

autre exemple si ma selection est vert 283 le resultat attendu en colonne p devrai etre de 73 a 97 et de 181 a 202

etc

les colonnes ou l'on doit récuperer les donnees ne seront jamais fixes et celle ci peuvent s etaler jusqu'a IV

ci joint le fichier peut etre plus explicite

merci encore de ton aide et de ta disponibilité

cordialement

ghislain

 

[R@chid]

Re : erreur formule suite a modification entre du numerique et du texte

Bonsoir GHISLAIN,
Definir le champ Base comme ca :

=DECALER(base!$C$7;;;MAX(SI(base!$C$7:$J$1565<>"";LIGNE(INDIRECT("1:"&LIGNES(base!$C$7:$J$1565)))));8)

En P6 :

=SI(LIGNES($6:6)<=SOMME(($C$5:$J$5=$M$13)*($C$6:$J$6=$M$15)*(Base<>""));PETITE.VALEUR(SI(($C$5:$J$5=$M$13)*($C$6:$J$6=$M$15)*(Base<>"");CNUM(Base));LIGNES($6:6));"")

@ valider par Ctrl+Maj+Entree
@ tirer vers le bas

Voir PJ.

Amicalement

 

[GHISLAIN]

Re : erreur formule suite a modification entre du numerique et du texte

bonsoir Rachid_0661

deja un grand merci pour ta rapidité de reaction et du resultat obtenu qui repond a ma demande
toutefois en cherchant a comprendre ton travail exeptionnel je l avoue plusieur chose m interpelle dans la façon de modifier ces formules .

ta proposition s'arrete a la colonne J , alors que moi je voudrai utiliser plus de colonne , voir jusqu a la colonne IV
j ai tenté de modifier tes formules , mais sans grand résultat

une question , le fait d'utiliser des formules matricielles , et de vouloir utiliser les 256 colonnes ne sera pas trop long d execution ?

je suis parvenu a changer de feuille le resultat obtenu , mais ne parviens pas a modifier les colonnes prises en charge

ci joint le fichier modifier

merci encore de ton aide et de ta disponibilité et de competence dans les dites formules

cordialement

ghislain

 

[R@chid]

Re : erreur formule suite a modification entre du numerique et du texte

Bonsoir GHISLAIN,
Tu peux changer sur la formuyle de deifintion du champ Base :
=DECALER(base!$C$7;;;MAX(SI(base!$C$7:$J$1565<>"";LIGNE(INDIRECT("1:"&LIGNES(base!$C$7:$J$1565)))));8)

base!$C$7 ==> Remplacer par la cellule ou commence ta Base
8 ==> Remplacer par le nombre de colonne qui determine la largeur.

Mais je suis sur ca va etre tres lent lors de l'execution.
Amicalement

 

[GHISLAIN]

Re : erreur formule suite a modification entre du numerique et du texte

bonjour rachid ,
je te remercie de ton aide et je suis parvenu a ce que je souhaitais
toutefois , je vais demander sur le site si par macro il n existerai pas une solution qui reagisse plus rapidement dans le traitement des donnees
cordialement

ghislain

 

[david84]

Re : erreur formule suite a modification entre du numerique et du texte

Bonsoir,

je vais demander sur le site si par macro il n existerai pas une solution qui reagisse plus rapidement dans le traitement des donnees

je n'ai pas tout suivi mais une version par macro à tester pour voir si cela convient :

Sub Extraction()
Dim Pl As Range, PlCrit As Range, DerLig As Long, Dercol As Integer, Critere As Range
Dim c, PremAdresse, s As Variant, Nb As Long, NbT As Long, i As Long, j As Long, k As Long
Dim T()
With Sheets("Base")
    DerLig = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
    Dercol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
    Set Pl = .Range(.Cells(5, 1), .Cells(DerLig, Dercol))
    Set Critere = .Range(.Cells(5, 1), .Cells(5, Dercol))
End With

With Critere
    Set c = .Find([Coul], LookIn:=xlValues, lookat:=xlWhole)
    If Not c Is Nothing Then
            PremAdresse = c.Address
            Do
            k = 1
            If c.Offset(1).Value = [Num] Then
                Set PlCrit = Sheets("Base").Range(Sheets("Base").Cells(7, c.Column), Sheets("Base").Cells(DerLig, c.Column))
                Nb = Application.WorksheetFunction.CountA(PlCrit)
                NbT = NbT + Nb
                ReDim Preserve T(NbT - 1)
                For i = j To UBound(T)
                    T(i) = PlCrit(k, 1): k = k + 1
                Next i
                j = NbT
            End If
        Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> PremAdresse
    End If

End With

With Sheets("Resultat")
    .Range("B2:B10000").ClearContents
    .Range("B2").Resize(UBound(T) + 1) = Application.Transpose(T)
End With
End Sub

Appuyer sur le bouton pour lancer la macro (il sera toujours temps si les tests sont concluants de la lancer autrement).
A+

 

[GHISLAIN]

Re : erreur formule suite a modification entre du numerique et du texte

bonjour david84,


je viens de regarder ta proposition , que je trouve excellente et d une rapidité incomparable avec une formule matricielle

deux remarques si je peux abusé

la premiere : si les valeurs recherchées n existe pas , j ai l erreur d execution9


j ai donc rajouté : On Error Resume Next
With Sheets("Resultat")
.Range("B2:B10000").ClearContents
On Error Resume Next
.Range("B2").Resize(UBound(T) + 1) = Application.Transpose(T)
End With

es ce la bonne solution??

la seconde remarque , est il possible une fois extraite , que la liste soit triée ??

cordialement

Ghislain
nb: j ai répondu sur l autre post

 

[david84]

Re : erreur formule suite a modification entre du numerique et du texte

Re

si les valeurs recherchées n existe pas , j ai l erreur d execution9


j ai donc rajouté : On Error Resume Next
With Sheets("Resultat")
.Range("B2:B10000").ClearContents
On Error Resume Next
.Range("B2").Resize(UBound(T) + 1) = Application.Transpose(T)
End With

es ce la bonne solution??

C'est une solution mais pas la meilleure. Il est préférable de passer par une condition permettant de vérifier si le tableau T comporte des valeurs (s'il en comporte c'est qu'il existe des valeurs répondant à la double condition de couleur et de numéro)

If NbT > 0 Then

la seconde remarque , est il possible une fois extraite , que la liste soit triée ??

Utiliser la méthode Sort pour obtenir le tri :

 .Range("B2").Sort Key1:=[B2], Order1:=xlAscending, Header:=xlGuess

Sub Extraction()
Dim Pl As Range, PlCrit As Range, DerLig As Long, Dercol As Integer, Critere As Range
Dim c, PremAdresse, s As Variant, Nb As Long, NbT As Long, i As Long, j As Long, k As Long
Dim T()
With Sheets("Base")
    DerLig = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
    Dercol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
    Set Pl = .Range(.Cells(5, 1), .Cells(DerLig, Dercol))
    Set Critere = .Range(.Cells(5, 1), .Cells(5, Dercol))
End With

With Critere
    Set c = .Find([Coul], LookIn:=xlValues, lookat:=xlWhole)
    If Not c Is Nothing Then
            PremAdresse = c.Address
            Do
            k = 1
            If c.Offset(1).Value = [Num] Then
                Set PlCrit = Sheets("Base").Range(Sheets("Base").Cells(7, c.Column), Sheets("Base").Cells(DerLig, c.Column))
                Nb = Application.WorksheetFunction.CountA(PlCrit)
                NbT = NbT + Nb
                ReDim Preserve T(NbT - 1)
                For i = j To UBound(T)
                    T(i) = PlCrit(k, 1): k = k + 1
                Next i
                j = NbT
            End If
        Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> PremAdresse
    End If

End With

With Sheets("Resultat")
    .Range("B2:B10000").ClearContents
If NbT > 0 Then
    .Range("B2").Resize(UBound(T) + 1) = Application.Transpose(T)
    .Range("B2").Sort Key1:=[B2], Order1:=xlAscending, Header:=xlGuess 'tri
End If
End With
End Sub

A+

 

[GHISLAIN]

Re : erreur formule suite a modification entre du numerique et du texte

bonjour david84,

je viens de tester ta proposition , excellent !!!!
une rapidité d exécution plus que satisfaisante dans le traitement des données
je te remercie vivement de ton aide et du temps accordé a mes fils,

bon weekend a toi

cordialement

ghislain

 

[GHISLAIN]

Re : erreur formule suite a modification entre du numerique et du texte

re David84,

je reviens vers toi soulever une erreur incomprehensible
ton dernier code fonctionne parfaitement bien, mais lorsque je l integre dans mon fichier finale celui me met une erreur d execution 13 a la ligne suivante : Set c = .Find([Coul], LookIn:=xlValues, lookat:=xlWhole)
j ai beau chercher je ne parviens pas a resoudre ce phenomene

cordialement

ghislain

 

[david84]

Re : erreur formule suite a modification entre du numerique et du texte

Re
[Coul] correspond à la cellule nommée placée en E4 (clique dans cette cellule et regarde ce qui est affiché dans la zone Nom). Donc il te faut nommer cette cellule, ou remplacer [Coul] par Sheets("Base").Range("E4") par exemple.
A+