XL 2016 concat de résultats index equiv

[Bonz]

Bonjour à tous,

Débutant en VBA, je monte un outils de gestion de sortie d'échantillons biologiques pour mon travail.
Pour ne pas toucher à ma base de donnée originale, j'ai créé un outils à part, tout allait bien jusqu'au moment où je dois afficher les positions de mes cellules dans des boites d'azote.

Pour faire plus concret j'ai extrait une base de donnée avec toutes les informations dont les utilisateurs auront besoin pour leur sélection d'échantillons.
Une fois ce choix fait un tableau permettra de voir où aller chercher les cellules pour la sortie : feuille Extraction sélection, simplifié dans le fichier joint.
Cette information est présente dans une autre BDD, schématisée dans le fichier joint.
Il faut donc que l'utilisateur ait dans le tableau d'extraction le référencement des positions de toutes les ampoules de manière claire (deux infos essentielles le numero de boite (B) et la position dans celle-ci (P).
A savoir que pour le moment j'ai 50 boites de 100 positions chacune, mais que pour un échantillon à un maximum de 20 tubes, donc positions, mais rarement plus de 5.
Je mets ceci ici car nos pc ne sont pas tout jeunes et beaucoup d'échantillons peuvent être rechercher à en même temps.

3 solutions possibles de plus en plus complexes mais rêvons grand:

1. Pour chaque position le code entier Boite"B" Numéro de boite position"P" numéro position (B01P1) se succédant les uns après les autres B01P1, B02P2...
2. Idem que précédent mais en ne répétant pas les numéro de boite quand plusieurs tubes sont dans la même boite B01P1, P2... / B02P8
3. Idem que précédent mais en prenant en compte le moment ou plus de deux tubes se suivent B01P1-4, P6 / B05P7

Et l'idéale serait que des sauts de lignes soit intégrer pour ne pas surcharger l'information.

J'ai cherché un peu partout et les solutions proposées ne me convienne pour ce problème.
Je demande beaucoup, mais je suis enthousiaste même pour des réponses partielle au problème qui m'aideraient à avancer. Une chose importante est que je ne peux pas ajouter des colonnes ou lignes au tableau car cela alourdirait grandement les sorties qui se font sur beaucoup d'échantillons à la fois, la lisibilité est donc très importante.

Merci beaucoup,


*Bien que relu plusieurs fois, ma dyslexie peut avoir laisser passer des fautes d'orthographe dans ce message, je m'en excuse.

 

[job75]

Bonjour Bonz, bienvenue sur XLD,

Voyez le fichier joint et cette fonction VBA utilisant la macro de tri bien connue Quick sort :

Function MaConcat$(id$, plage As Range)
Dim nlig&, tablo, i&, a$(), n&, x$, s
id = Trim(id) 'supprime les espaces superflus...
nlig = Application.CountA(plage) / 2
tablo = plage.Resize(nlig) 'matrice, plus rapide
For i = 2 To nlig
    If Trim(tablo(i, 1)) = id Then
        ReDim Preserve a(n) 'base 0
        a(n) = tablo(i, 2)
        n = n + 1
    End If
Next
tri a, 0, n - 1
x = Split(a(0))(0)
For i = 1 To n - 1
    s = Split(a(i))
    If s(0) = x Then a(i) = ", " & s(1) Else x = Split(a(i))(0): a(i) = vbLf & a(i)
Next
MaConcat = Join(a, "")
End Function

Sub tri(a, gauc, droi) ' Quick sort
Dim ref, g, d, temp
ref = a((gauc + droi) \ 2)
g = gauc: d = droi
Do
    Do While a(g) < ref: g = g + 1: Loop
    Do While ref < a(d): d = d - 1: Loop
    If g <= d Then
      temp = a(g): a(g) = a(d): a(d) = temp
      g = g + 1: d = d - 1
    End If
Loop While g <= d
If g < droi Then Call tri(a, g, droi)
If gauc < d Then Call tri(a, gauc, d)
End Sub

Le code doit être placé impérativement dans un module standard.

Le résultat obtenu en colonne H de la 1ère feuille est bien suffisant, faut pas pousser grand-mère...

A+

 

[Bonz]

Merci beaucoup job75,

J'ai préféré en demander beaucoup d'un coup plutôt que de faire revenir des gens sur le code encore et encore pour pousser plus loin.

En effet c'est très lisible ainsi, et me permet de voir qu'il me reste beaucoup à apprendre en VBA. Je vais tenter de décortiquer cette solution.

Petite question est ce que l'information des différentes positions identifiées est récupérable ou stockée quelque par autre que la cellule. En gros par la suite quand l'utilisateur aura retirer une ampoule de la boite ma prochaine étape et de lui faire dire via un userform de qu'elle position il a pris sa ou ses tubes. du coup pouvoir lui proposer directement ces position serait un plus pour éviter les erreurs.

C'est vraiment bonus je me suis pas encore vraiment penché sur cette partie et ne vois pas encore la stratégie.

En espérant ne pas pousser grand mère ...

*Bien que relu plusieurs fois, ma dyslexie peut avoir laisser passer des fautes d'orthographe dans ce message, je m'en excuse.

 

[job75]

Pendant le calcul de la fonction les informations sont stockées dans le tableau a, puis affectées à la fonction. Au final seule la cellule les stocke.

Je n'ai pas compris ce que vous voulez faire d'autre avec ces informations.

 

[Bonz]

C'est bien ce que je pensais.

En gros lorsque l'utilisateur a utilisé ces informations de localisation de tubes dans les boites, il faut ensuite actualiser les deux BDD sources (ma base de donnée échantillons et ma base de données boite azote).
Pour l'aider je veux faire un userform où il m'indiquer combien de tubes il a pris et depuis quelles positions. Donc, si je veux l'assister dans cette tache, il faudra que soit je relise cette case, soit que je réitère la recherche.

 

[Bonz]

Re-bonjour,

désolé pour le délais, avec mes vacances (coté positif) et l'adaptation de mon laboratoire à ce second confinement (point négatif), je n'ai pu me mettre que maintenant à l'intégration de cette nouvelle formule à mon fichier.

Et il s'avère que j'ai des problèmes lorsque je l'utilise à partir de de mon tableau final.
En copie vous trouverez le fichier avec cette en feuille 'BDD' la véritable version de mon exportation des positions de mes ampoules à l'azote.

J'ai essayé de le faire coller au maximum au fichier que vous m'avez fournit mais sans succès (expériences absentes de ce fichier, où je vous livre la version la plus proche de ce que j'ai actuellement).
Je ne comprends pas l'erreur. Encore une fois j'aurai besoin de votre aide.

merci

 

[job75]

Bonjour Bonz,

Dans votre fichier 2 raisons font que la fonction MaConcat renvoie #VALEUR! :

1) il y a des valeurs d'erreur en colonne H de la feuille BDD, pour y remédier :

If Trim(CStr(tablo(i, 1))) = id Then 'CStr en cas de valeur d'erreur

2) C8 et C10 n'existent pas en colonne H de la feuille BDD, pour y remédier avant le tri :

If n = 0 Then Exit Function 'si la valeur id n'est pas trouvée

PS : bien noter que la colonne contenant la fonction est au format "Renvoyer à la ligne...".

Mais ne pas oublier d'appliquer la commande "Ajuster la hauteur de ligne".

A+