Unieke waarden consolideren uit 3 verschillende bronbestanden

[Nelis1]

Beste allemaal,

hoe kan je vanuit drie lijsten met data, één geconsolideerde lijst maken met unieke waarden.
In bijgevoegd voorbeeld staan er per Sheet "Lijst 1,2,3" producten met de desbetreffende volumes.


In de consolidated lijst wil ik alleen de unieke producten terugzien, volumes alloceren is geen probleem.
In de praktijk zal dit om een 900 tal producten gaan, welke steeds zullen verschillen gezien het hier om een post mortum analysis gaat.

alvast bedanktBekijk bijlage Unieke waarden uit verschillende bronbestanden.xlsx

 

[HSV]

Je schrijft volumes toewijzen is geen probleem; in onderstaande code worden ze opgeteld.

Sub hsv()
Dim sv, j As Long, i As Long
With CreateObject("scripting.dictionary")
       For j = 1 To 3
           sv = Sheets(j).Cells(1).CurrentRegion
                  For i = 2 To UBound(sv)
                      .Item(sv(i, 1)) = .Item(sv(i, 1)) + sv(i, 2)
                   Next i
       Next j
    Sheets("consolidated file").Cells(1).CurrentRegion.Offset(1).ClearContents
    Sheets("consolidated file").Cells(2, 1).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
End With
End Sub

 

[Nelis1]

Thanks, werkt als een trein. Snap er werkelijk geen snars van maar het werkt.
Ik ga gewoon geduldig door met het leren in mijn VBA bijbel van Wim de Groot maar ik weet niet of ik dit daarin ga terugvinden

als je het stukje VBA kan uileggen dan graag en anders thanks & solved


solved in ieder geval:thumb:

 

[HSV]

For j = 1 to 3 ' loop de eerste drie bladen een voor een door.

For i = 1 to de laatste cel van elk tabblad (in dit geval de array 'sv').

De 'dictionary' methode onthoud alleen de unieke sleutel (keys).
Van de item van elke key wordt een telling gemaakt.

 

[Nelis1]

dus als ik mijn snappert probeer aan te gooien:
For i = 2 To UBound(sv)
.Item(sv(i, 1)) = .Item(sv(i, 1)) + sv(i, 2)


als ik een extra kolom wil toevoegen, wordt het dan:
For i = 3 To UBound(sv)
.Item(sv(i, 1)) = .Item(sv(i, 1)) + sv(i, 2)+ sv(i, 3)

excuses ik was te snel, dit wordt dan:
For j = 1 To 3
sv = Sheets(j).Cells(1).CurrentRegion
For i = 2 To UBound(sv)
.Item(sv(i, 1)) = .Item(sv(i, 1)) + sv(i, 2) + sv(i, 3)
Next i
Next j
Sheets("consolidated file").Cells(1).CurrentRegion.Offset(1).ClearContents
Sheets("consolidated file").Cells(2, 1).Resize(.Count, 3) = Application.Transpose(Array(.keys, .items))

 

[HSV]

De telling wordt inderdaad dan uit de tweede en derde kolom gemaakt.
Het resultaat wordt dan gewoon over twee kolommen uitgesplitst.

 

[Nelis1]

toch niet helemaal goed van mij ...
wat is de benodigde aanpassing als ik kolom c bijvoorbeeld de kg bijvoeg?

Bekijk bijlage Unieke waarden uit verschillende bronbestanden.xlsm

 

[HSV]

Een volledige nieuwe aanpak.

Sub hsv()
Dim sv, j As Long, i As Long, a, b(2)
With CreateObject("scripting.dictionary")
  For j = 1 To 3
   sv = Sheets(j).Cells(1).CurrentRegion
    For i = 2 To UBound(sv)
     a = .Item(sv(i, 1))
     If IsEmpty(a) Then a = b
        a(0) = sv(i, 1)
        a(1) = a(1) + sv(i, 2)
        a(2) = a(2) + sv(i, 3)
      .Item(sv(i, 1)) = a
    Next i
  Next j
    Sheets("consolidated file").Cells(1).CurrentRegion.Offset(1).ClearContents
    Sheets("consolidated file").Cells(2, 1).Resize(.Count, 3) = Application.Index(.items, 0, 0)
End With
End Sub

 

[Nelis1]

:shocked::thumb:

 

[Nelis1]

dat is als je er niet veel van snapt - er "moet" nog een extra kolom in. dus ik dacht dan passen we tog gewoon even snel de VBA code aan... helaas krijg het gewoon niet beredeneert.
wat dient er in de VBA code aangepast te worden om ook de additionele kolom mee te nemen in de consolidated file.Bekijk bijlage Unieke waarden uit verschillende bronbestanden v3.xlsm

Sub hsv()
Dim sv, j As Long, i As Long, a, b(2)
With CreateObject("scripting.dictionary")
For j = 1 To 3
sv = Sheets(j).Cells(1).CurrentRegion
For i = 3 To UBound(sv)
a = .Item(sv(i, 1))
If IsEmpty(a) Then a = b
a(0) = sv(i, 1)
a(1) = a(1) + sv(i, 2)
a(2) = a(2) + sv(i, 3)
a(3) = a(3) + sv(i, 3)
.Item(sv(i, 1)) = a
Next i
Next j
Sheets("consolidated file").Cells(1).CurrentRegion.Offset(1).ClearContents
Sheets("consolidated file").Cells(2, 1).Resize(.Count, 4) = Application.Index(.items, 0, 0)
End With
End Sub

 

[HSV]

Hoe moet het resultaat eruit zien?

 

[Nelis1]

Goedemorgen Harry,

resultaat zoals in sheet "consolidated file" dient het eindresultaat te zijn.
eerste opzet deed alles netjes op product sommen en nu moet de product groep erbij genoemd de Technology.
product 310A zal altijd onder technology a vallen.

Product kan ook voor technology staan dat maakt elkaar helemaal niets.
hoofdzaak is dat de producten sommen op volume in ea en op kg's

Bekijk bijlage Unieke waarden uit verschillende bronbestanden v3.xlsm

hoop dat deze uitleg niet te omslachtig is

 

[HSV]

Verwijder de code uit Thisworkbook en voeg een module in en plaats daar onderstaande code.

Sub hsv()
Dim sv, j As Long, i As Long, a, b(3)
With CreateObject("scripting.dictionary")
  For j = 1 To 3
   sv = Sheets(j).Cells(1).CurrentRegion
    For i = 2 To UBound(sv)
     a = .Item(sv(i, 2))
     If IsEmpty(a) Then a = b
        a(0) = sv(i, 1)
        a(1) = sv(i, 2)
        a(2) = a(2) + sv(i, 3)
        a(3) = a(3) + sv(i, 4)
      .Item(sv(i, 2)) = a
    Next i
  Next j
    Sheets("consolidated file").Cells(1).CurrentRegion.Offset(1).ClearContents
    Sheets("consolidated file").Cells(2, 1).Resize(.Count, 4) = Application.Index(.items, 0, 0)
End With
End Sub

 

[Nelis1]

werkt als een zwitsers klokje - dank je wel voor de genomen moeite.
nu zie ik ook hoe de range dient uit te breiden :thumb:

 

[jkpieterse]

Dit kan ook prima met een draaitabel (in de wizard de optie meerdere consolidatie bereiken kiezen), of mooier nog met PowerQuery.