Update prijslijst in excel

[Avenda]

ik heb 2 excel bestanden, voor het gemak even in 1 voorbeeld bestand geplaatst (2 tabbladen).

In bestand A de huidige stand van zaken, in bestand B de nieuwe prijslijst

Bestand A bevat artikelen die inmiddels niet meer bestaan en dus niet in bestand B voorkomen, maar ook bestaande artikelen die ik wil updaten. De inmiddels niet meer bestaande artikelen moeten een code "1" meekrijgen in het veld "discontinued"
Bestand B bevat alle artikelen, ook nieuwe die nog niet in bestand A staan.
De velden komen overeen, veld "product_code" is een sleutelveld.

Ik heb al met van alles en nog wat lopen te stoeien maar ik heb nog geen methode gevonden waarbij ik dit op een makkelijke manier kan verwerken. Is er iemand die mij op weg kan helpen?

Het lukt mij niet om een bestand te uploaden (up te loaden?). Klik hier voor het bestand

 

[sponiza]

Hoi, je hebt gekeken naar de functie VERT.ZOEKEN of Vertical Lookup? Dat zou mijn startpunt zijn.

 

[popipipo]

Je verteld niet wat de code moet zijn als hij wel in bestand B voorkomt.
In dit voorbeeld heb ik aangenomen van niets.

 

[Avenda]

@sponiza dank je, ik zal daar eens mee gaan stoeien. Da's zo ongeveer de enige functie die ik nog nooit gebruikt heb

@popipipo dank voor het opzetje, ik ga eens kijken of ik daar wat mee kan maken. Als het artikel wel in bestand B voorkomt moet deze overigens in bestand A komen te staan. Kun je me daar ook nog een voorzet voor geven?

 

[popipipo]

Wat wil je hiermee bereiken een up to date bestand toevoegen aan een verouderd bestand.
Hier zie ik het nut niet van in.

 

[Avenda]

het nut is als volgt: bestand A komt uit een orderverwerkingsprogramma, bestand B is de actuele prijslijst (beetje aangepast zodat de kolommen overeen komen met bestand A, het exportbestand). Als bestand A een update heeft ondergaan (dus actuele prijzen, actuele artikelomschrijvingen, toevoeging nieuwe artikelen, "discontinued" maken van niet meer leverbare artikelen) wordt bestand A weer ingelezen in het orderverwerkingsprogramma. De oude artikelen die misschien wel ooit verkocht zijn worden daarmee op niet actief gezet, als een klant een herhalingsorder vraagt komt er een melding indien een artikel niet meer leverbaar is.

Ik hoop dat je me hiermee kunt helpen.

 

[popipipo]

Kun je dan niet beter de "discontinued" toevoegen aan het nieuwe bestand en dan dit verder gebruiken?

Het oude bestand kun je dan sorteren op de "discontinued" en dan de "discontinued" kopieren en plakken en opnieuw sorteren in het nieuwe bestand.

 

[Warme bakkertje]

Probeer onderstaande eens uit op een testbestand. Eerste blad uit het systeen, 2de blad de update zoals in je vb-bestand.
Draai dan de code eens.

Sub tst()
With Sheets("Bestand A - uit systeem")
    For i = 2 To .Range("A1048576").End(xlUp).Row
        If .Cells(i, 6) <> "" Then
           If Sheets(2).Columns(6).Find(.Cells(i, 6).Value, , xlValues, xlWhole) Is Nothing Then
                .Cells(i, 2).Value = 1
            End If
        End If
    Next
End With
On Error Resume Next
With Sheets("Bestand B - prijslijst update")
    For i = 2 To .Range("F1048576").End(xlUp).Row
    FRow = Sheets(1).Columns(6).Find(.Cells(i, 6).Value, , xlValues, xlWhole).Row
        If FRow <> 0 Then
            Sheets(1).Cells(FRow, 1).Resize(, 17) = .Cells(i, 1).Resize(, 17).Value
        Else
            Sheets(1).Range("A1048576").End(xlUp).Offset(1).Resize(, 17) = .Cells(i, 1).Resize(, 17).Value
        End If
    FRow = 0
    Next
End With
End Sub

 

[Avenda]

Hi Rudi

ik heb je code ingevoerd (ik hoop op de juiste manier, ViBa is niet iets wat ik vaker heb gebruikt). Het enige resultaat is dat in het testbestand tab A de "product_id" van de nog actuele items is verdwenen. In tab B gebeurd niets.

Gr. Pepijn

 

[Warme bakkertje]

In Blad2 zal nooit iets gebeuren aangezien dit de updatelijst is. Dat er velden verdwijnen in Blad1 is logisch aangezien je updateblad niet volledig ingevuld is. Blijft dit zo of was dit enkel in je voorbeeld ? Nu werkt de macro zo dat waneer er een overeenkomst gevonden wordt bij product_code de volledige regel uit de update geplakt wordt in het systeemblad. Als het echter de bedoeling is dat enkel ingevulde velden vervangen worden moet de macro aangepast worden, maar dit was niet duidelijk op te maken uit je vraagstelling. Als ik de macro getest heb had ik eerst kolom A op het updateblad gevuld met waarden en dan werkt het perfect.

Edit: Heb de code aangepast zodat enkel ingevulde velden in updateblad gekopieërd worden naar systeemblad.

Sub tst()
Const Systeemblad = "Bestand A - uit systeem" 'wijzig hier de naam van systeemblad
Const Updateblad = "Bestand B - prijslijst update" ' wijzig hier de naam van updateblad
With Sheets(Systeemblad)
    For i = 2 To .Range("A1048576").End(xlUp).Row
        If .Cells(i, 6) <> "" Then
           If Sheets(Updateblad).Columns(6).Find(.Cells(i, 6).Value, , xlValues, xlWhole) Is Nothing Then
                .Cells(i, 2).Value = 1
            End If
        End If
    Next
End With
On Error Resume Next
With Sheets(Updateblad)
    For i = 2 To .Range("F1048576").End(xlUp).Row
    FRow = Sheets(Systeemblad).Columns(6).Find(.Cells(i, 6).Value, , xlValues, xlWhole).Row
        If FRow <> 0 Then
            For j = 1 To 17
                If .Cells(i, j) = "" Then
                Else
                    Sheets(Systeemblad).Cells(FRow, j) = .Cells(i, j).Value
                End If
            Next
        Else
            Sheets(Systeemblad).Range("F1048576").End(xlUp).Offset(1, -5).Resize(, 17) = .Cells(i, 1).Resize(, 17).Value
        End If
    FRow = 0
    Next
End With
End Sub

 

[Avenda]

Hi Rudi, ik dacht dat dit het was maar het gaat nog niet goed. Door de opmerking van Popipipo ben ik anders gaan denken: het is wellicht handiger de nieuwe prijslijst te gebruiken als updatebestand voor het systeem. Ik zal daarom de vraagstelling herformuleren:

Tabblad A systeembestand
Tabblad B nieuwe prijslijst

Tabblad B wordt het gewijzigde bestand voor import

Tabblad A bevat een unieke systeemcode (kolom A "_k_product_id") die niet in Tab B voorkomt
De link tussen tabblad A en B is kolom E "bestel_code"
Kolom A "_k_product_id" van Tab B moet worden gevuld met de code van Tab A indien veld E overeenkomt. De overige informatie in de regels moet NIET worden overschreven
Indien een artikel uit Tab A niet meer in Tabblad B voorkomt moet kolom B "discontinued" worden gevuld met waarde "1"
De artikelen die niet voorkomen in Tab B (zijnde de artikelen die de waarde 1 hebben in kolom "discontinued") moeten aan Tab B worden toegevoegd

Samengevat: tabblad B wordt in het systeem ingelezen. De oude artikelen worden op discontinued gezet, de bestaande artikelen krijgen een prijs- en evt. omschrijving update, de nieuwe artikelen worden toegevoegd.

Volgens mij is dit het wel, ik hoop dat je er nog wat tijd in wilt steken.