XL 2019 Recherche valeur (Critère date et ID) et copier valeur dans tableau dans la cellule correspondante

[Rabeto]

Bonjour,

J'aimerai faire une recherche de valeur dans la colonne Type en se basant sur 2 critères (ID et Date) et placer les résultats dans la cellule concernée du tableau.

Je vous joint un fichier pour mieux expliquer la demande, mais c'est juste un exemple.

Il se trouve que je pourrai avoir une centaine d'ID avec des dates allant jusqu'à la fin de l'année.

Par macro si possible

merci,

 

[Dranreb]

Adapté avec les données de l'autre classeur.

 

[patty58]

Bonjour à tous
Il semblerait que ce sujet soit résolu ou ai-je mal vu ?
S'il est résolu, il serait intéressant de savoir quelle solution a été retenue ??
Merci pour tous

 

[Rabeto]

Bonjour Cousinhub, et à tous,

Je m'y attendais par ta réponse avec Powerquery
J'y ai travaillé aussi, mais ta proposition sera la bienvenue pour moi

@patty58 : ta réponse sur le post 7 a résolu la demande seulement les contraintes qu'on avait sur les ID doublons ne marchait pas avec la macro.

Je vais garder le post 7 comme étant la solution en guise du temps que as consacré pour me répondre, mais j'accepte aussi d'autre alternative

 

[Cousinhub]

Bonjour,
J'ai un peu observé dans les différentes réponses un choix de période...
Que veux-tu obtenir comme résultat?
- Toutes les dates?
- Un mois en particulier (mois précédent, par ex.)?
Pour info, au premier calcul (donc connexion non effective), environ 3.5 secondes, puis quasi-immédiat par la suite.

 

[Rabeto]

@Cousinhub

Je souhaite des résultats sur toutes les dates, du 01 Janvier 2024 au 31 Décembre 2024 par exemple.
J'y ai pensé sur ta proposition avec powerquery,

Si je me trompe pas, powerquery ne traite que des données depuis une base brute, si la date est présente dans la base, elle s'affiche, mais si elle n'est pas présente, la date sera absente du tableau.
Donc, à mettre par défaut une date du 01 Janvier 2024 au 31 Décembre 2024.

Le but est :

- Si j'alimente le fichier avec de nouvelle donnée, les cellules se remplissent automatiquement, par les valeurs depuis colonne Type selon l'ID et la date concernée.
Si par exemple l'ID XXXX a CP (dans colonne Type) date début 01/01/2024 date Fin 03/01/2024 : Toutes les cellules entre ces dates, il y a CP.

Effectivement, sur les remarques de @patty58, il se peut qu'il y ai des ID sui se répètent sur une même date, et oui tu as trouvé la raison, car un ID, peut avoir 2 ou 3 demandes en une journée (CP 1/2 journée + TT) par exemple, il faut juste concaténer les données comme tu l'as proposé (CP/TT/ect.....)

 

[Cousinhub]

Re-,
Pour les dates, j'avais déjà pris en compte. (dans ce fichier, je prends l'année en cours - on peut modifier si tu veux)
Par contre, est-ce qu'il ne serait pas plus "raisonnable", de ne remplir le tableau que jusqu'à la dernière date prise en compte dans le tableau de données (ici, 07/02/24)?
Au fur et à mesure, le tableau va se mettre à jour, même s'il y a des jours vides.
J'ai également pris en compte le fait qu'un ID ne prenne rien dans la période, mais apparaisse quand même.
Premier jet

 

[Rabeto]

re Cousinhub

Donc, càd si la date n'est pas présent dans la base, la date s'affiche toujours et garde des cellules vides ?
Si c'est le cas c'est top,

J'ai également pris en compte le fait qu'un ID ne prenne rien dans la période, mais apparaisse quand même.

C'est partie c'est top,

Y a t-il une possibilité avec PQ de faire la MAJ des données sans que les données ne changent ?
"Précision : La base est issu d'une extraction"

Si par exemple : j'ai déjà les données MAJ du 01 Janvier au 07 Février 2024 comme dans le fichier, quand je lance PQ avec des nouvelles données, seul les données des nouvelles dates se mettent à jour le restent ne change pas.
Si j'intègre par exemple les données sur 08 Février, une colonne 08 Février s'ajoute et les dates qui ont déjà des données ne changent pas.

 

[Cousinhub]

Re-,
Je ne comprends pas le besoin...
Si les données changent dans la base, il est évident que les résultats de la requête vont être modifiés
Si des données sont juste ajoutées aux anciennes, elles seront prises en compte, sans modifier les anciennes valeurs...
Si c'est pour conserver un format, il faut le spécifier dans les options du Tableau

 

[Cousinhub]

re Cousinhub

Donc, càd si la date n'est pas présent dans la base, la date s'affiche toujours et garde des cellules vides ?
Si c'est le cas c'est top

Oui, je rajoute une ligne en début de tableau, avec la date min et la date max, ainsi toutes les dates comprises entre ces deux dates sont ajoutées.
Et je supprime cette ligne en fin de requête

 

[job75]

Bonjour à tous,

Par formule sur la plage H3:NI32 cette formule en H3 est très simple :

=REPT($B3;ET(H$2>=ENT($C3);H$2<ENT($D3)+1))

Sur 300 lignes le tableau est recalculé chez moi en 0,37 seconde ce qui est très acceptable.

Maintenant si vous voulez absolument du VBA mettez cette macro dans le code de la feuille :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim P As Range, Q As Range, r&, h&
Set P = [B3:D32] 'à adapter
Set Q = [H3:NI32] 'à adapter
Set Target = Intersect(Target, P)
If Target Is Nothing Then Exit Sub
Application.EnableEvents = False 'désactive les évènements
For Each Target In Target.EntireRow.Areas 'si entrées multiples
    r = Target.Row
    h = Target.Rows.Count
    Q.Rows(r - Q.Row + 1).Resize(h) = "=REPT($B" & r & ",AND(H$2>=INT($C" & r & "),H$2<INT($D" & r & ")+1))"
    Q.Rows(r - Q.Row + 1).Resize(h) = Q.Rows(r - Q.Row + 1).Resize(h).Value 'mettre en commentaire pour voir la formule
Next
Application.EnableEvents = True 'réactive les évènements
End Sub

Modifiez les valeurs en colonnes B C D.

Pour recalculer tout le tableau H3:NI32 copiez la colonne B sur elle-même.

A+

 

[Cousinhub]

Bonsoir Job,
Je pense qu'il manque cette info :

Effectivement, sur les remarques de @patty58, il se peut qu'il y ai des ID sui se répètent sur une même date, et oui tu as trouvé la raison, car un ID, peut avoir 2 ou 3 demandes en une journée (CP 1/2 journée + TT) par exemple, il faut juste concaténer les données comme tu l'as proposé (CP/TT/ect.....)

Bonne soirée

 

[Rabeto]

Bonsoir à tous, et merci pour vos réponses,

Je vais prendre un peu de temps pour assimiler vos propositions, mais je tiens fortement à vous remercier.

Premièrement, mes excuses, car je n'étais pas clair au début, et je n'ai pas directement mis le bon fichier final en pensant que je modifie juste des valeurs dans la macro proposé par patty58 (macro ci-dessous) pour avoir la réponse, mais je me suis trompé.

@job75 : Après un premier coup d'œil, le code VBA donne les résultats souhaités, sauf que si je change les valeurs dans les colonnes B à D, les données dans le tableau s'efface, alors que les données dans ces colonnes servent à alimenter le tableau et doivent y rester pour garder les historiques.

je pense que cette réponse par patty58 en post 7 a peut être résolu le sujet, sauf qu'elle ne prend pas en compte si l'ID a des doublons sur une même date et je n'arrive pas à l'adapter avec le fichier ci-joint.

Global DLig, Ws, DatDeb, DatFin, DColDeb, DColFin
Sub Placer()
Set Ws = ActiveSheet
DLig = Ws.Range("A65536").End(xlUp).Row
DColDeb = 8
DColFin = 38
'rech date corresp col date deb
For ind = 2 To DLig
DatDeb = DateValue(Ws.Cells(ind, 3))
DatFin = DateValue(Ws.Cells(ind, 4))
For ColD = DColDeb To DColFin
If Ws.Cells(2, ColD) = DatDeb Then
ColDat = ColD
Call RechercheLigneType(ind, Ws.Cells(ind, 1), ColDat)
Exit For
End If
Next ColD
Next ind
End Sub

Sub RechercheLigneType(LigTyp, Id, Col)
For Lig = 3 To DLig + 1
If Ws.Cells(Lig, 7) = Id Then
Ws.Cells(Lig, Col) = Ws.Cells(LigTyp, 2)
Col = Col + 1
While Ws.Cells(2, Col) <= DatFin And Col < DColFin
Ws.Cells(Lig, Col) = Ws.Cells(LigTyp, 2)
Col = Col + 1
Wend
End If
Next
End Sub

Je vais essayer d'être clair et éviter que vous soyez confus : (encore mes excuses si j'étais pas clair au début)

Mes besoins :
1 - Je fais une extraction de donnée (comme dans : colonne A jusqu'à H de ce fichier) puis je colle les données.
2 - Je souhaite que le tableau à partir de colonne AH16 (Tableau Colonne = ID / Ligne = Date) soit remplis par les valeurs depuis colonne D Type (selon les critères ID et date)
3- Il se peut qu'il y ai des ID qui se répètent sur une même date, car un ID, peut avoir 2 ou 3 types en une journée ex (CP + TT) par exemple, il faut juste concaténer les données comme (CP/TT/ect.....) si même date et même ID.

Finalité :
1 - Chaque fois que j'alimente ma base (changé les données dans colonne A:H; après une nouvelle extraction, le tableau soit à jour.
2 - Je peux rajouter des ID de façon manuelle dans la colonne AG car ID = liste de personne

@ Cousinhub : Ta proposition par PowerQuery est une autre alternative, sauf que je ne t'ai pas indiqué que la colonne AG doit servir de source pour les ID et à un moment, je dois rajouter un ID.
PQ ne prend que les ID qui sont déjà présent dans la base à traiter.


Encore une fois, merci

 

[Cousinhub]

Re-,
OK, fin de mes interventions
PS, PQ prend tout ce qu'on lui donne, et s'il y a un rajout d'ID en Colonne B du tableau de base, cet ID sera bien évidemment rajouté... Et le fait de le rajouter en colonne AG, quel intérêt, s'il n'est pas présent dans le tableau de données???????
Bref, bon courage

 

[job75]

Concaténer plusieurs Types (B) dans une même cellule me paraît une idée saugrenue puisqu'une cellule correspond à un seul jour.

L'historique est conservé tant qu'on ne modifie pas les données sources des colonnes B C D.

Pour ajouter des dates sur un même ID il suffit d'ajouter de nouvelles lignes et de trier ensemble les 2 tableaux sur la colonne A.

 

[Cousinhub]

Hi,
Job, les jours sont sécables (matin, apm), et il peut y avoir 2 lignes pour le même jour, et le même ID.
Par contre, je te rejoins complètement sur le pseudo-historique que voudrait Rabeto...
Bref, il ne sait pas lui-même ce qu'il veut, et découvre au fur et à mesure des réponses, ce qu'il pourrait avoir...
Donc, fin pour moi
Bonne soirée