[EXCEL] Creare un associazione tra tabelle

[webpleasure]

Ciao a tutti, non so se il titolo del topic è appropriato, e mi è risultato complicato cercare sul forum qualcosa di attinente, vi spiego cosa vorrei fare...

Ho i listini dei miei fornitori in formato xls con: COD.Prodotto - Quantità - Nome prodotto.
Ho il mio listino in formato xls con: Il mio codice prodotto - Quantità - Il Mio nome prodotto.

Vorrei sapere se è fattibile e come creare una specie di tabella di associazione tra il codice prodotto del fornitore (es. PROD0001) ed il mio codice prodotto (es. ID0005) in modo da ricavarne la quantità disponibile dal fornitore con il mio codice prodotto, il risultato dovrebbe essere poi salvato in un file CSV in modo da poter essere importato facilmente.

Non ho nessuno problema a crearmi questa associazione a mano la prima volta, ma m'interessa che poi il tutto diventi automatizzato per l'estrazione della quantità.

Solitamente leggendo i vostri aiuti sono sempre riuscito a ricavarne una soluzione appropriata alle mie esigenze, ma questa volta credo sia tosta come situazione...
Grazie!

[Flash30005]

Ciao Webpleasure e benvenuto nel Forum

Leggendo la prima parte del tuo post dove dicevi:
"Ho i listini dei miei fornitori in formato xls con: COD.Prodotto - Quantità - Nome prodotto.
Ho il mio listino in formato xls con: Il mio codice prodotto - Quantità - Il Mio nome prodotto
e volevi creare un'associazione"

mi sono spaventato perché l'unica voce in comune era solo la quantità che è sicuramente diversa, come valore


Si, se fai un'associazione tra i due codici poi sarà possibile ricavarsi il resto

ciao

[webpleasure]

Ciao Flash! Grazie della risposta!
Volevo quindi chiederti come creo questa associazione fra i 2 tipi di codice (che sono diversi) e dire ad excel che per il mio prodotto "A" devo usare la quantità del prodotto "AB1" del fornitore?

[Flash30005]

é sufficiente creare una tabella dove in una colonna hai il tuo codice e nella colonna adiacente il codice fornitore
poi si deve creare una macro che scansioni il tuo Listino
trova il primo tuo codice
lo confronta con quello del fornitore (in tabella)
va alla ricerca del codice fornitore nel listino fornitore
una volta trovato copia la quantità e la incolla in corrispondenza del tuo codice nel tuo listino

Con un ciclo for next
otterrai il tuo listino aggiornato

ciao

[webpleasure]

Bene vedo che non è così insormontabile come credevo, comincio a prepararmi a mano la tabella di associazione dei codici, basta mettere in colonna A il mio codice e in quella B quello del fornitore, nella C andrà la quantità ricavata giusto?
Ora volevo poi chiederti, riesci a illuminarmi sul codice da utilizzare? Non sono proprio praticissimo riesco solo ad adattarmi un pochetto!
Grazie del tuo aiuto sei stato molto gentile!

[Flash30005]

Quantità?

No, no
solo i codici tuoi non ripetuti associati ai codici fornitore

Per la quantità si dovrà creare una macro ad hoc
dopo che avrai postato il file oppure che avrai dato le coordinate delle colonne con i dati e i nomi dei fogli

ciao

[webpleasure]

Sto creando la lista ma ho un piccolo problemino, estraendo il codice fornitore in automatico posso incappare in 2 situazioni diverse:

Stinga SKIR008Marca mi serve solo il codice: SKIR008 quindi risolvo con:

Codice: Seleziona tutto

=SINISTRA(A1;RICERCA("Marca";A1)-1)

Stringa ADG5312P.Listino mi serve solo il codice: ADG5312 quindi risolvo con:

Codice: Seleziona tutto

=SINISTRA(A5;RICERCA("P.Listino";A5)-1)

La domanda è, come creo un comando che mi unisca le 2 situazioni in modo da usare lo stesso comando per l'intera colonna?
Ho provato il comando "O" ma non sono riuscito a venirne fuori...

[Flash30005]

Puoi usare un "doppio" sostituisci
(supponendo A1 il primo codice)

Codice: Seleziona tutto

=SOSTITUISCI(SOSTITUISCI(A1;"Marca";1);"P";1)

Ma attenzione perché se un codice ha all'interno la lettera P con questo codice (come nel tuo esempio) verrà eliminata la lettera propria del codice pertanto opterei più per una formula così

Codice: Seleziona tutto

=SE(DESTRA(A1)="P";SINISTRA(A1;LUNGHEZZA(A1)-1);SOSTITUISCI(A1;"Marca";1))

ciao

[Anthony47]

L' istruzione Sostituisci cerca una stringa e la rimpiazza con un' altra:
Sostituisci(TestoDiPartenza;StringaDaCercare;StringaDiRimpiazzo)
Quindi la formula

Codice: Seleziona tutto

=SOSTITUISCI(SOSTITUISCI(A2;"Marca";"");"P.Listino";"")

eliminera' le stringhe "Marca" e "P.Listino" dalla stringa di origine
Questa formula e' leggermente diversa da quanto ha scritto Flash.

Ciao

[webpleasure]

Eccomi qua, grazie 1000 per i suggerimenti "notturni", ero quasi arrivato ad una soluzione da solo.....poi ho optato per la via del copia/incolla della formula
Ho creato uno zip con il necessario per la fase successiva.
https://rapidshare.com/files/956176951/estrazione.zip
All'interno dell'archivio ci sono 2 file (dat.csv e dati.xlsm) il csv è quello relativo ai dati grezzi importati successivamente nel file xlsm, non so se fosse necessario ma l'ho incluso lo stesso .

Per testare il tutto ho inserito solo i primi 11 articoli, il listino completo è di quasi 2000 e non mi sembrava il caso.

Il file dati.xlsm invece è diviso in 2 pagine, nella prima che ho chiamato "dati grezzi fornitore", c'è la "pulitura" dei dati di origine (dati.csv), estraendo in modo chiaro il nome prodotto, il codice articolo e la quantità. Qui c'è anche una macro che si chiama "pulizia_iniziale" che mi elimina le righe "superflue".

Infine il tutto è concatenato per un eventuale utilizzo.

Nella seconda pagina c'è l'associazione del codice produttore con l'id del mio prodotto (ho dovuto usare l'id e non il codice per facilitarmi la successiva importazione).
La seconda pagina sarà sempre controllata a mano mentre la prima estrarrà automaticamente i dati, questo perchè magari domani fornitore potrebbe inserire un nuovo prodotto ad esempio nella quinta riga che mi stravolgerebbe tutto, in questo modo dovrei salvare le mie associazioni e aggiungere in fondo la nuova che si è creata. (io l'ho pensata così se avete un'idea migliore è ben accetta )

Credo di aver dato tutte le spiegazioni necessarie, ora il problema da risolvere quello scritto nei post precedenti e sarebbe:
- estrarre la quantità legata al codice prodotto del fornitore
- controllare l'associazione con il mio id
- associare la quantità al mio id.

Alcune funzioni supplementari che mi farebbero comodo ma non so se fattibili sono:
- la creazione automatica di un file csv con i campi id e quantità ricavati in precedenza.
- l'evidenziamento di un codice del fornitore che non ha ancora un'associazione con un mio ID.

Grazie ancora a tutti per il vostro aiuto, siete davvero molto gentili!

[webpleasure]

il link corretto è questo:
https://rapidshare.com/files/3938979393/estrazione.zip

[webpleasure]

Mentre attendevo la risposta ci ho provato sa solo, credo che la funzione utilizzata sia corretta per la mia situazione ma non riesco a farla andare...

Codice: Seleziona tutto

=CERCA.ORIZZ(B2;'dati grezzi fornitore'!D2:E2;'dati grezzi fornitore'!D2)

...l'ho inserita nel foglio "comparazione codici" in C2....perfavore aiutatemi

[Anthony47]

Non ho seguito la discussione, quindi forse mi sono perso informazioni importanti e faccio fatica a inquadrare, quindi faccio commenti sperando che siano attinenti.

Primo: il codice fornitore "forse" deve essere preceduta da una sigla che indica di quale fornitore parli; voglio dire che oggi hai un fornitore che usa il codice SCR008 per uno strano (per me) bene che si chiama 011-Edizione Italiana: sei sicuro che non avrai mai a che fare in futuro con un altro fornitore che allo stesso codice SCR008 non ha associato il vol 31 della Storia di Forli'? Quindi il mio suggerimento e' che crei uno standard di Codice che comprende Cod.Fornitore+Codice prodotto del fornitore. Possibilmente una "sigla parlante", cioe' interpretabile a colpo d' occhio e di lunghezza fissa con un separatore noto; es CF001-CP:SCR008
Ovviamente CF sta' per "codice fornitore", 001 e' il numero su una tua lista Fornitori (e se pensi di aver bisogno di piu' di 999 fornitori e' bene che usi subito CF0001), CP: e' il preambolo di Codice prodotto, e SCR008 e' il famoso bene "011-Edizione Italiana"

Secondo: ti sei fatto una associazione "Tuo codice 1" col prodotto SCR008; ma sei sicuro che quel bene non lo acquisterai anche da un fornitore2 il cui codice sara' PIPP321? Come pensi di gestire questa situazione? Non avendo idea di quale e' lo scopo finale del lavoro non ho un suggerimento "su misura" da dare; banalmente io aggiungerei una seconda riga con lo stesso "Tuo codice 1" che fa riferimento al codice di un secondo fornitore. Inoltre eviterei che il tuo codice sia un valore numerico, quindi userei non 1 ma (a mo' di esempio) WP0001 (se pensi a max 9999 codici).

Fine del preambolo, e veniamo al quesito:

- estrarre la quantità legata al codice prodotto del fornitore
- controllare l'associazione con il mio id
- associare la quantità al mio id

Qui si impone purtoppo un' altra osservazione, relativamente alla struttura dati su "dati grezzi fornitore": il campo Codice e' gerarchicamente superiore al campo Quantita', quindi andrebbe messo in posizione a sx della colonna Qt; ad esempio se Codice e Qt fossero invertiti avresti potuto usare Cerca.Vert, nella formula

Codice: Seleziona tutto

=CERCA.Vert(B2;'dati grezzi fornitore'!D:E;2;0)

Trattandosi di campi ottenuti con formule non dovresti aver difficolta' a invertire le colonne e usare cosi' la formula che ti ho dato; cosi' te ne risparmio una meno immediata con Indice e Confronta.
Forse ti chiederai perche' uso Cerca.Vert e non Cerca.Orizz (come nella tua formula sintatticamente "avventurosa"); la risposta e' che noi non sappiamo (cioe' tu non sai) su quale riga e' posizionato quel codice, ed e' quindi una "ricerca in verticale" sulla colonna del codice (D) che ci consente di arrivare alla riga giusta su cui prendere il "2°" (vedi "2" nella formula) elemento nell' intervallo cercato (colonne D:E, quindi colonna E) dove e' posizionato il campo Quantita', mentre l' ultimo "0" indica che si fara' una "ricerca esatta" del codice e non una approssimata. Per i dettagli della sintassi ti potra' aiutare la consultazione dell' Help on line di Excel.

Ciao

[webpleasure]

Ciao Anthony47, innanzitutto grazie per la risposta e per le tue precisazioni, cerco di risponderti in modo preciso...

Primo: il codice fornitore "forse" deve essere preceduta da una sigla che indica di quale fornitore parli; voglio dire che oggi hai un fornitore che usa il codice SCR008 per uno strano (per me) bene che si chiama 011-Edizione Italiana: sei sicuro che non avrai mai a che fare in futuro con un altro fornitore che allo stesso codice SCR008 non ha associato il vol 31 della Storia di Forli'? Quindi il mio suggerimento e' che crei uno standard di Codice che comprende Cod.Fornitore+Codice prodotto del fornitore. Possibilmente una "sigla parlante", cioe' interpretabile a colpo d' occhio e di lunghezza fissa con un separatore noto; es CF001-CP:SCR008
Ovviamente CF sta' per "codice fornitore", 001 e' il numero su una tua lista Fornitori (e se pensi di aver bisogno di piu' di 999 fornitori e' bene che usi subito CF0001), CP: e' il preambolo di Codice prodotto, e SCR008 e' il famoso bene "011-Edizione Italiana"

Parto con il dirti che tratto come merce i boardgames (giochi da tavolo o in scatola) il fornitore a cui faccio riferimento è il più grande d'Italia (per non dire l'unico) e siccome al momento non possiamo permetterci di fare del grande magazzino in base a degli accordi ci agganciamo a loro. Quando in pratica avrò altri fornitori significa che avrò possibilità di farmi magazzino e acquisti dall'estero e il foglio excel non servirà più (ecco cos'è 011 http://boardgamegeek.com/boardgame/93194/011).

Secondo: ti sei fatto una associazione "Tuo codice 1" col prodotto SCR008; ma sei sicuro che quel bene non lo acquisterai anche da un fornitore2 il cui codice sara' PIPP321? Come pensi di gestire questa situazione? Non avendo idea di quale e' lo scopo finale del lavoro non ho un suggerimento "su misura" da dare; banalmente io aggiungerei una seconda riga con lo stesso "Tuo codice 1" che fa riferimento al codice di un secondo fornitore. Inoltre eviterei che il tuo codice sia un valore numerico, quindi userei non 1 ma (a mo' di esempio) WP0001 (se pensi a max 9999 codici).

Il mio codice in realtà per quel prodotto sarà PAG00001 ho usato solo "1" perchè è l'id del prodotto, dato che così mi sarà più comodo importare un file CSV contenente l'ID prodotto con la relativa quantità. Effettivamente ho chiamato la colonna erroneamente "mio codice".
Il mio scopo finale è semplicemente mantenere le quantità disponibili di questo fornitore aggiornate senza dovermi girare il loro sito giornalmente dato che non è il massimo della navigabilità.

Ora passiamo alla formula che naturalmente funziona alla grande! Ho riposizionato le colonne come da te suggerito e grazie 1000 anche per la spiegazione della formula, effettivamente non avendo mai usato molto excel mi rendo conto di non averci capito niente

Ora avrei altre 2 implementazioni se possibile:
- Se nella pagina "dati grezzi fornitore" appare un nuovo codice prodotto e questo non è associato ad un mio ID che mi venga segnalato in qualche modo tipo il relativo campo evidenziata in giallo.
- Excel è capace di esportare un file CSV in automatico con le colonne da me richieste? (Mio ID e Quantità del foglio "comparazione codici)

Grazie ancora!

[Anthony47]

Per la verifica dei codici fornitore vs il tuo: in una colonna libera di "dati grezzi fornitore" inserisci, in riga2, la formula

Codice: Seleziona tutto

=SE(VAL.ERRORE(CERCA.VERT(D2;'Comparazione Codici'!B:B;1;0));"###";"Ok")

(In col D ora hai il codice, vero?) Copia poi verso il basso anche oltre fine dell' attuale elenco.
Se il codice e' presente nel tuo elenco avrai Ok, altrimenti ###

Per l' esportazione delle colonne, la cosa piu' semplice e' che ti registri una macro su un file nuovo, avendo aperto anche aperto il file Dati.xlsm, mentre esegui diligentemente, partendo dal file nuovo:
-seleziona il file Dati.xlsm
-seleziona foglio "dati grezzi fornitore"
-salvi il file col suo nome e formato excel
-seleziona il foglio "Comparazione Codici"
-copialo in un nuovo foglio: tab Home /Celle /Formato /Sposta o copia foglio; spunta Crea copia e (sposta alla fine); Ok
-seleziona tutte le colonne che ti interessano, Copia, IncollaSpeciale-Valori (nelle stesse posizioni)
-elimina le colonne che non ti interessano
-File /Salva con nome; come formato scegli CSV, dai il nome che ti piace; Salva.
-Rispondi Ok ai messaggi che ti da'
-chiudi il file XYZ.csv anche senza salvarlo (ti dara' dei warning che puoi ignorare)
-apri il file Dati.xlsm
Interrompi la registrazione.
Copia il codice prodotto e pubblicalo in un prossimo messaggio, lo varieremo per migliorare l' automatismo.

Nel messaggio precedente ho dimenticato di dirti di variare nella Function estrai_cifre la riga Application.Volatile True in Application.Volatile False
Eviterai di ricalcolare in continuazione le Disponibilita' se non cambia la cella richiamata (colonna Disponibilita')

INFINE una domanda: ma i dati di prodotti, codice, disponibilita' sono accessibili in un sito del fornitore?

Ciao

[webpleasure]

Per la verifica dei codici fornitore vs il tuo: in una colonna libera di "dati grezzi fornitore" inserisci, in riga2, la formula

Codice: Seleziona tutto

=SE(VAL.ERRORE(CERCA.VERT(D2;'Comparazione Codici'!B:B;1;0));"###";"Ok")

(In col D ora hai il codice, vero?) Copia poi verso il basso anche oltre fine dell' attuale elenco.
Se il codice e' presente nel tuo elenco avrai Ok, altrimenti ###

Fatto! Mi sono solo permesso di aggiungere una formattazione condizionale (verde e rosso per i due casi) e sostituire "###" con "nuovo".

Per l' esportazione delle colonne, la cosa piu' semplice e' che ti registri una macro su un file nuovo, avendo aperto anche aperto il file Dati.xlsm, mentre esegui diligentemente, partendo dal file nuovo:
-seleziona il file Dati.xlsm
-seleziona foglio "dati grezzi fornitore"
-salvi il file col suo nome e formato excel
-seleziona il foglio "Comparazione Codici"
-copialo in un nuovo foglio: tab Home /Celle /Formato /Sposta o copia foglio; spunta Crea copia e (sposta alla fine); Ok
-seleziona tutte le colonne che ti interessano, Copia, IncollaSpeciale-Valori (nelle stesse posizioni)
-elimina le colonne che non ti interessano
-File /Salva con nome; come formato scegli CSV, dai il nome che ti piace; Salva.
-Rispondi Ok ai messaggi che ti da'
-chiudi il file XYZ.csv anche senza salvarlo (ti dara' dei warning che puoi ignorare)
-apri il file Dati.xlsm
Interrompi la registrazione.
Copia il codice prodotto e pubblicalo in un prossimo messaggio, lo varieremo per migliorare l' automatismo.

Fatto!
Premessa, nel foglio "comparazione codici" nella colonna D2 ho inserito:

Codice: Seleziona tutto

=CONCATENA(A2; "|"; C2;)

in modo da avere "ID|quantità" che è il formato che mi serve per l'importazione.
Qui la macro ottenuta con i tuoi passi (ho eliminato i commenti automatici):

Codice: Seleziona tutto

Sub esportadati()

    Workbooks.Add
    Windows("Dati.xlsm").Activate
    Sheets("dati grezzi fornitore").Select
    ActiveWorkbook.Save
    Sheets("Comparazione Codici").Select
    Sheets("Comparazione Codici").Copy After:=Workbooks("Cartel1").Sheets(1)
    Columns("D:D").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:C").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    ActiveWorkbook.SaveAs Filename:="C:\estrazione\update.csv", FileFormat:= _
        xlCSV, CreateBackup:=False
    ActiveWindow.Close
End Sub

Sembra creare il file correttamente, anche se mi chiede la prima volta di sovrascrivere il file già esistente e alla chiusura se voglio salvare nuovamente il file.

Nel messaggio precedente ho dimenticato di dirti di variare nella Function estrai_cifre la riga Application.Volatile True in Application.Volatile False
Eviterai di ricalcolare in continuazione le Disponibilita' se non cambia la cella richiamata (colonna Disponibilita')

Fatto! Il codice l'avevo trovato in giro e leggermente riadattato nel limite delle mie conoscenze grazie della dritta!

INFINE una domanda: ma i dati di prodotti, codice, disponibilita' sono accessibili in un sito del fornitore?

Ciao

Si, i dati "grezzi" sono ricavati tramite il sito del fornitore, loro forniscono un file pdf il fine settimana, ma io sto cercando di aggiornarmi quotidianamente
Io direi che ci siamo (sei) riuscito alla grande, tutto sembra andare, ora devo terminare la tabella delle associazioni mio ID/Codice e fare una prova generale d'importazione.

Un'altra cosa che ti volevo chiedere è perchè se dalla query scelgo d'importare i dati ad ogni apertura del file, eseguo la macro di "pulizia iniziale", chiudo e salvo il file, riapro il file...i comandi in D12, E12, F12 vogliono in automatico prendersi i dati da A21, B21, C21 invece che da A12,B12, C12 quando poi all'atto di salvare il file le stringhe sono corrette!

Ciao e grazie ancora!!!

[Anthony47]

Per evitare i messaggi durante salvataggio e chiusura file puoi inserire le due righe marcate <<<

Codice: Seleziona tutto

'istruzioni precedenti
    Selection.Delete Shift:=xlUp
    Application.DisplayAlerts = False   '<<<
    ActiveWorkbook.SaveAs Filename:="C:\estrazione\update.csv", FileFormat:= _
        xlCSV, CreateBackup:=False
    ActiveWindow.Close
    Application.DisplayAlerts = True   '<<<
End Sub


Per la questione delle formule che sembra cambino riferimenti, non credo di avere elementi per replicare il comportamento. Mi pare comunque che nel tuo file Dati.xlsm ci sia una query per importare il file csv; se e' cosi' allora tieni presente che all' aggiornamento dei dati le righe dell' area dei risultati possono accorciarsi o allungarsi, creando problemi a formule che indirizzino in modo esplicito le celle; un problema analogo fu discusso qui: viewtopic.php?f=26&t=95468#p547057
La proposta fu di trasformare gli indirizzamenti in "fissi"
Se anche il tuo problema e' questo allora il mio suggerimento e' che importi in un foglio, poi con formule a indirizzamento fisso riporti i valori su un secondo foglio ("dati grezzi fornitore"?) su cui sono presenti anche le formule (che fanno riferimento ai dati sullo stesso foglio)

Ciao