A voir également:
- Format siret excel
- Liste déroulante excel - Guide
- Telecharger format factory - Télécharger - Conversion & Codecs
- Formule excel - Guide
- Format apfs - Guide
- Si et excel - Guide
12 réponses
pijaku
Messages postés
12263
Date d'inscription
jeudi 15 mai 2008
Statut
Modérateur
Dernière intervention
4 janvier 2024
2 745
Modifié par pijaku le 28/06/2012 à 09:47
Modifié par pijaku le 28/06/2012 à 09:47
Bonjour,
Ou se trouvent tes n° de SIREN (nom de la feuille et colonne)? Idem pour les SIRET...
Nom d'une feuille vide dans le classeur?
Tu n'es pas allergique aux macros?
Cordialement,
Franck P
Ou se trouvent tes n° de SIREN (nom de la feuille et colonne)? Idem pour les SIRET...
Nom d'une feuille vide dans le classeur?
Tu n'es pas allergique aux macros?
Cordialement,
Franck P
PHILOU10120
Messages postés
6371
Date d'inscription
lundi 16 avril 2012
Statut
Contributeur
Dernière intervention
5 mai 2024
797
28 juin 2012 à 09:48
28 juin 2012 à 09:48
Bonjour
Première solution ajouter des zéro derrière le SIREN pour avoir des libellés que l'ont puisse trier les 2 listes ensembles
la formule =CTXT($A$4&"00000";0;VRAI)
mettre la cellule au format standard puis grouper vos listes et trier
deuxième solution
extraire les 9 premiers caractères dans une colonne la formule =GAUCHE(B5;9)
et grouper vos listes et chercher les doublons la formule pour chercher les doublons
=SI(NB.SI($C$4:$C$9;C4)>1;1;0)
dans mon exemple le SIREN est en colonne C
Première solution ajouter des zéro derrière le SIREN pour avoir des libellés que l'ont puisse trier les 2 listes ensembles
la formule =CTXT($A$4&"00000";0;VRAI)
mettre la cellule au format standard puis grouper vos listes et trier
deuxième solution
extraire les 9 premiers caractères dans une colonne la formule =GAUCHE(B5;9)
et grouper vos listes et chercher les doublons la formule pour chercher les doublons
=SI(NB.SI($C$4:$C$9;C4)>1;1;0)
dans mon exemple le SIREN est en colonne C
pijaku
Messages postés
12263
Date d'inscription
jeudi 15 mai 2008
Statut
Modérateur
Dernière intervention
4 janvier 2024
2 745
28 juin 2012 à 10:04
28 juin 2012 à 10:04
Bon, la demande évoluant, peux tu nous "bricoler" un fichier exemple avec des noms, siren et siret et autres données bidons comme c'est actuellement dans ton fichier, mais dans les bonnes colonnes ET le résultat attendu. Tu peux nous le transférer grâce à cjoint.com...
Excusez moi, je n'ai peut être pas été très claire dans l'exposition de ma demande.
J'ai un fichier client de l'entreprise qui me donne le montant de l'assurance accordé et je dois le mettre a jours avec les données fournis hier par l'assurance.
ça me donne cela: http://cjoint.com/?BFCkpcP4v9I
pour le fichier assurance, je n'ai mis que les colonnes qui me servent, il y a plein d'autres information qui ne me servent pas.
Autre point: une fois la mise a jours effectué, je ne concerve pas les informations assurance.
J'ai un fichier client de l'entreprise qui me donne le montant de l'assurance accordé et je dois le mettre a jours avec les données fournis hier par l'assurance.
ça me donne cela: http://cjoint.com/?BFCkpcP4v9I
pour le fichier assurance, je n'ai mis que les colonnes qui me servent, il y a plein d'autres information qui ne me servent pas.
Autre point: une fois la mise a jours effectué, je ne concerve pas les informations assurance.
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
pijaku
Messages postés
12263
Date d'inscription
jeudi 15 mai 2008
Statut
Modérateur
Dernière intervention
4 janvier 2024
2 745
28 juin 2012 à 10:51
28 juin 2012 à 10:51
Dans ton fichier test, tous les n° de SIRET forment un "bloc", aucune cellule vide entre chaque n°. Est ce le cas de ton fichier ? ou alors il y a des cellules vides par ci par la?
J'ai 3700 entrées sur 4900 ayant un SIRET ou SIREN, en effet j'ai des entrées ou il est affiché que le SIREN.
Merci de vos réponses et de votre patience .
Merci de vos réponses et de votre patience .
pijaku
Messages postés
12263
Date d'inscription
jeudi 15 mai 2008
Statut
Modérateur
Dernière intervention
4 janvier 2024
2 745
28 juin 2012 à 11:09
28 juin 2012 à 11:09
Merci de vos réponses et de votre patience Il n'y a aucun problème. Le truc c'est qu'avec une macro, il faut absolument connaitre la place de chaque élément dans ton fichier.
Donc je récapitule :
dans ta feuille nommée : AdresseClient France (sans espace entre Adresse et Client, mais un espace entre Client et France)
tu as une "base de données" de A1 à Sx composée, dans les 5000 premières lignes de ta base clients avec n° de SIRET (14 chiffres), puis dans les 5000 lignes suivantes tes données avec : en colonne A les mêmes références clients que de A2 à A5000 et en colonne O les SIREN (9 chiffres)
Tu souhaites obtenir, dans une même feuille (que j'appellerai résultat) la liste complète de tes données avec deux lignes contigües par client, une qui contient le SIRET et une qui contient le SIRENE
ex :
A2 : 411001 O2 : 50010050040000
A3 : 411001 A3 : 500100500
avec bien sur conservation des données de toute la ligne...
C'est bien ça???
Donc je récapitule :
dans ta feuille nommée : AdresseClient France (sans espace entre Adresse et Client, mais un espace entre Client et France)
tu as une "base de données" de A1 à Sx composée, dans les 5000 premières lignes de ta base clients avec n° de SIRET (14 chiffres), puis dans les 5000 lignes suivantes tes données avec : en colonne A les mêmes références clients que de A2 à A5000 et en colonne O les SIREN (9 chiffres)
Tu souhaites obtenir, dans une même feuille (que j'appellerai résultat) la liste complète de tes données avec deux lignes contigües par client, une qui contient le SIRET et une qui contient le SIRENE
ex :
A2 : 411001 O2 : 50010050040000
A3 : 411001 A3 : 500100500
avec bien sur conservation des données de toute la ligne...
C'est bien ça???
C'est bien ça a une petite erreur prêt: la référence client de l'assurance n'est pas la même.
Le fichier de l'assurance ne contient aucun en-tête du coup pour s'y retrouver c'est un peut la m***
Du coup je ne récupére que le SIREN et le montant d'assurance.
Mon but final étant d'avoir pour un SIRET donné correspondant au SIREN, le montant de l'assurance dans la colonne S.
Si les 9 premiers caractères corresponde alors copier "montant assurance" dans la colonne S.
Le fichier de l'assurance ne contient aucun en-tête du coup pour s'y retrouver c'est un peut la m***
Du coup je ne récupére que le SIREN et le montant d'assurance.
Mon but final étant d'avoir pour un SIRET donné correspondant au SIREN, le montant de l'assurance dans la colonne S.
Si les 9 premiers caractères corresponde alors copier "montant assurance" dans la colonne S.
pijaku
Messages postés
12263
Date d'inscription
jeudi 15 mai 2008
Statut
Modérateur
Dernière intervention
4 janvier 2024
2 745
28 juin 2012 à 11:29
28 juin 2012 à 11:29
du coup, dernières questions :
1- les 9 premiers chiffres de tes siret sont tous différents, pas comme dans ton fichier exemple :
50010050040000
50010050050000
50010050060000
50010050070000
50010050080000
50010050090000
50010050100000
50010050110000
50010050120000
2- tu veux que les chiffres transmis par l'assurance qui se trouvent donc entre S5001 (à peu près) et S10000 se placent entre S2 et S5001 à côté des bons numéros Siret.
OK?
1- les 9 premiers chiffres de tes siret sont tous différents, pas comme dans ton fichier exemple :
50010050040000
50010050050000
50010050060000
50010050070000
50010050080000
50010050090000
50010050100000
50010050110000
50010050120000
2- tu veux que les chiffres transmis par l'assurance qui se trouvent donc entre S5001 (à peu près) et S10000 se placent entre S2 et S5001 à côté des bons numéros Siret.
OK?
Oui ils sont tous différents. J'ai mis les mêmes par soucis de simplification sur l'exemple.
La seule colonne qui m'importe c'est la colonne du montant des assurances.
Je parlais de coller la ligne de l'assurance en dessous de la ligne entreprise uniquement pour mettre l'information sur le montant assurance manuelement, mais c'est vrai que si sa peut aussi s'automatisé, alors allons-y.
Les siren je les ai mis dans la même colonne que les siret car je pensais pouvoir, via un tri ou des filtres, résoudre mon problème. Donc s'ils ce retrouvent dans une autre colonne, ce n'est pas un problème.
En poussant la reflexion j'ai pensé a une formule du genre:
SI(GAUCHE(U2;9)=GAUCHE(O2;9);V2;"")
Dans la colonne U il y a les SIREN et dans V les montants. Mais ça ne marche pas...
La seule colonne qui m'importe c'est la colonne du montant des assurances.
Je parlais de coller la ligne de l'assurance en dessous de la ligne entreprise uniquement pour mettre l'information sur le montant assurance manuelement, mais c'est vrai que si sa peut aussi s'automatisé, alors allons-y.
Les siren je les ai mis dans la même colonne que les siret car je pensais pouvoir, via un tri ou des filtres, résoudre mon problème. Donc s'ils ce retrouvent dans une autre colonne, ce n'est pas un problème.
En poussant la reflexion j'ai pensé a une formule du genre:
SI(GAUCHE(U2;9)=GAUCHE(O2;9);V2;"")
Dans la colonne U il y a les SIREN et dans V les montants. Mais ça ne marche pas...
pijaku
Messages postés
12263
Date d'inscription
jeudi 15 mai 2008
Statut
Modérateur
Dernière intervention
4 janvier 2024
2 745
28 juin 2012 à 12:37
28 juin 2012 à 12:37
En fait, j'ai laissé tomber la macro inutile dans ce cas.
Je suppose tes SIRET en colonne O de O2 à O5001, tes SIREN, au format nombre en colonne O, de O5005 à O10006, les montants accordés par l'assurance en colonne S, de S5005 à S10006, tu veux tes résultats donc en colonne S, de S2 à S50001.
En S2 saisir la formule :
=SI(O2="";"";RECHERCHEV(CNUM(GAUCHE(O2;9));$O$25:$S$34;5;FAUX))
Formule à "étirer" méthode de recopie jusqu'en S5001...
Je suppose tes SIRET en colonne O de O2 à O5001, tes SIREN, au format nombre en colonne O, de O5005 à O10006, les montants accordés par l'assurance en colonne S, de S5005 à S10006, tu veux tes résultats donc en colonne S, de S2 à S50001.
En S2 saisir la formule :
=SI(O2="";"";RECHERCHEV(CNUM(GAUCHE(O2;9));$O$25:$S$34;5;FAUX))
Formule à "étirer" méthode de recopie jusqu'en S5001...
Je ne comprend pas le $O$25:$S34$ dans la formule.
Je viens de transcrire la formule en S2 et j'ai bien mis les informations là ou tu ma dis de les mettres mais j'ai #N/A qui apparait.
Je viens de transcrire la formule en S2 et j'ai bien mis les informations là ou tu ma dis de les mettres mais j'ai #N/A qui apparait.
pijaku
Messages postés
12263
Date d'inscription
jeudi 15 mai 2008
Statut
Modérateur
Dernière intervention
4 janvier 2024
2 745
28 juin 2012 à 15:28
28 juin 2012 à 15:28
Normal, je m'as gouré!:... Mes tests ont fonctionnés sur cette plage. Y faut remplacer par $O$5001:$S$10006...
Ooops!
=SI(O2="";"";RECHERCHEV(CNUM(GAUCHE(O2;9));$O$5005:$S$10006;5;FAUX))
Ooops!
=SI(O2="";"";RECHERCHEV(CNUM(GAUCHE(O2;9));$O$5005:$S$10006;5;FAUX))
Merci de vos réponses.
Je n'arrive malheureusement pas a appliquer vos formules :(
Pour évité de devoir descendre jusqu'a la ligne 5005 la prochaine fois que ma responsable de stage ce servira du fichier, j'ai mis les SIREN en colonne U et les montants en V, comme ça elle aura juste a faire un copier/coller des colonnes et a les mettre respectivement en U2 et V2.
donc j'ai sa maintenant: http://cjoint.com/?BFCp2mCGpdn
Je n'arrive malheureusement pas a appliquer vos formules :(
Pour évité de devoir descendre jusqu'a la ligne 5005 la prochaine fois que ma responsable de stage ce servira du fichier, j'ai mis les SIREN en colonne U et les montants en V, comme ça elle aura juste a faire un copier/coller des colonnes et a les mettre respectivement en U2 et V2.
donc j'ai sa maintenant: http://cjoint.com/?BFCp2mCGpdn
PHILOU10120
Messages postés
6371
Date d'inscription
lundi 16 avril 2012
Statut
Contributeur
Dernière intervention
5 mai 2024
797
28 juin 2012 à 16:09
28 juin 2012 à 16:09
Etes vous sûre que les lignes correspondront
c'est pour cela qu'il prendre la fonction rechercheV
c'est pour cela qu'il prendre la fonction rechercheV
PHILOU10120
Messages postés
6371
Date d'inscription
lundi 16 avril 2012
Statut
Contributeur
Dernière intervention
5 mai 2024
797
29 juin 2012 à 15:28
29 juin 2012 à 15:28
EN T2 mettre cette formule =CNUM(GAUCHE(O2;9))
et en U2 mettre celle-ci
=SI(ESTERREUR(RECHERCHEV(T2;$O$2:$S$34000;5;FAUX));"";RECHERCHEV(T2;$O$2:$S$34000;5;FAUX))
et en U2 mettre celle-ci
=SI(ESTERREUR(RECHERCHEV(T2;$O$2:$S$34000;5;FAUX));"";RECHERCHEV(T2;$O$2:$S$34000;5;FAUX))
PHILOU10120
Messages postés
6371
Date d'inscription
lundi 16 avril 2012
Statut
Contributeur
Dernière intervention
5 mai 2024
797
Modifié par PHILOU10120 le 28/06/2012 à 15:00
Modifié par PHILOU10120 le 28/06/2012 à 15:00
Bonjour
Mettre cette formule en S2
=SI(ESTERREUR(RECHERCHEV(CNUM(T2);$O$25:$S$34;5;FAUX));"";RECHERCHEV(CNUM(T2);$O$25:$S$34;5;FAUX))
et celle-ci en T2
=gauche($O25;9)
et ajuster le champ O25 : S34 à votre champ
Puis tirer sur toute la hauteur
Mettre cette formule en S2
=SI(ESTERREUR(RECHERCHEV(CNUM(T2);$O$25:$S$34;5;FAUX));"";RECHERCHEV(CNUM(T2);$O$25:$S$34;5;FAUX))
et celle-ci en T2
=gauche($O25;9)
et ajuster le champ O25 : S34 à votre champ
Puis tirer sur toute la hauteur
PHILOU10120
Messages postés
6371
Date d'inscription
lundi 16 avril 2012
Statut
Contributeur
Dernière intervention
5 mai 2024
797
28 juin 2012 à 18:08
28 juin 2012 à 18:08
Vous devez mettre la cellule de la colonne de référence et la première ligne de la zone de recherche Oxxx et la dernière cellule de la derniére colonne Sxxx dans l'exemple
Je viens de me rendre compte que je ne peut pas changer de format. Hors j'avais mis un format personnalisé: [>999999999]########_######;Standard
Le problème viens peut être de là.
En faisant un tri via l'outil "filtrer" je me rend compte que j'ai, dans ma colonne SIRET (donc O), des chaine de caractère variable: la majorité est composé de 14 chiffres (normal pour un siret), mais j'en ai d'autre qui font 9 chiffres. J'en ai beaucoup qui commence par 0, a mon avis ce n'est pas normal et sa doit pas aider.
Je crois que je ne vais pas vous embété plus longtemps, je vais saisir manuellement :-/
Le problème viens peut être de là.
En faisant un tri via l'outil "filtrer" je me rend compte que j'ai, dans ma colonne SIRET (donc O), des chaine de caractère variable: la majorité est composé de 14 chiffres (normal pour un siret), mais j'en ai d'autre qui font 9 chiffres. J'en ai beaucoup qui commence par 0, a mon avis ce n'est pas normal et sa doit pas aider.
Je crois que je ne vais pas vous embété plus longtemps, je vais saisir manuellement :-/
PHILOU10120
Messages postés
6371
Date d'inscription
lundi 16 avril 2012
Statut
Contributeur
Dernière intervention
5 mai 2024
797
29 juin 2012 à 10:24
29 juin 2012 à 10:24
Dans la colonne O on utilise seulement les 9 caractères de gauche avec la formule en colonne T T2 je prends =gauche(O25;9) donc en ligne 25et en colonne O je prends les 9 caractères de gauche pour la recherchev
28 juin 2012 à 09:49
Je comprend rien au macro xD
28 juin 2012 à 09:52
Y a t'il des cellules vides dans cette colonne O?
28 juin 2012 à 09:56
En fait, j'ai une liste de client avec les SIRET et a partir d'un fichier fournis par l'assurance, je dois mettre le montant de l'assurance pour chaque client, seulement le fichier identifie les clients par les SIREN.