Som als de eerste X aantal tekens in een cel gelijk zijn

[p0ekie]

Beste Excel-experts,

In een Excel bestand (zie voorbeeld) heb ik een lijst met klanten die op verschillende manieren in een cel zijn benoemd, maar dezelfde klant zijn. Echter elke andere benoeming staat een omzetbedrag dat ik graag wil groeperen. Ik heb dit geprobeerd met draaitabellen, som.als, somproduct, maar ik kom er niet uit.

In het voorbeeld ben ik dus opzoek naar een formule om voor klant X, bedrijf y en company z de totale omzet te berekenen. Achter elke klant staan in de meeste cellen echter ook ordernummers. Het originele bestand bestaat uit 30.000 regels dus ondoenlijk om dat met de hand te doen. Nou leek het mij een idee om dit te filteren o.b.v. de eerste X aantal tekens. Als die overeenkomen dan de som van de cellen ernaast waarin dezelfde eerste X aantal tekens staan.

Wellicht hebben jullie suggesties?

Alvast bedankt!

Bekijk bijlage voorbeeld.xlsx

 

[HSV]

Met een asterix.

=SOM.ALS($B$3:$B$16;F3&"*";$D$3:$D$16)

 

[p0ekie]

Bedankt voor de snelle reactie!

Het probleem met mijn lijst, is dat ik dus niet beschik over een lijst met klantnamen. Enkel de lijst zoals in Kolom B. Ik moet dus toe naar een lijstje met Kolom F en G.

 

[sylvester-ponte]

bedoelt u dat u voor de unieke klanten ook een formule wilt?
is vba een optie? ( ik ben niet zo handig in lange onoverzichtelijke excel formules)

ps als het maar voor 1 keer is dan komt u een heel eind met "teks naar kolommen"
en duplicaten verwijderen

 

[p0ekie]

Ik wil inderdaad een lijst me unieke klanten en de totale omzet per unieke klant. Precies zoals die nu (handmatig) in Kolom F (naam klant) en Kolom G (totale omzet) heb gezet. Deze moeten worden gefilterd uit kolom B waarin dus het begin van de cel qua tekst altijd overeenkomt, maar dezelfde klant op meerdere rijden kan voorkomen doordat er ordernummers achter zijn geschreven.

Als dit met (sneller/gemakkelijker) VBA is op te lossen dan is dat zeker een optie.

ps Het punt met Tekst naar kolommen, is dat niet iedere klant de dezelfde lengte in naam is en dat sommige klantnamen ook .,/& tekens bevatten.

 

[HSV]

Laat onderstaande code eens lopen en kijken of het zo gaat.

Sub hsv()
Dim oDic As Object, sn, i As Long, klant
Set oDic = CreateObject("scripting.dictionary")
sn = Columns(2).CurrentRegion.SpecialCells(2).Resize(, 3)
  For i = 1 To UBound(sn)
    klant = Split(sn(i, 1), " /")(0)
       oDic.Item(klant) = oDic.Item(klant) + sn(i, 3)
  Next i
 Range("K1").Resize(oDic.Count, 2) = Application.Transpose(Array(oDic.keys, oDic.items))
End Sub

 

[p0ekie]

Op zich loopt de code in het voorbeeld, maar als ik daar test bedrijven aan toevoeg dan pakt hij deze niet mee. Enig idee hoe ik dit kan oplossen?

Bekijk bijlage voorbeeld.xlsx

 

[HSV]

Er werd een scheiding gemaakt op spatie+slash.
Verander de code in....

klant = Split(sn(i, 1))(0)

....zodat er alleen op een spatie een scheiding wordt gemaakt.

 

[sylvester-ponte]

of zoiets met een function
kan als matrixfunction of als gewone function met teller

 

[Jezet]

Hoi P0ekie,

Je kan ook gewoon een matrix-formule gebruiken (invoeren met Ctrl+Shift ingedrukt houden en dan op Enter).
De formule is dan

=SOMPRODUCT((LINKS($B$3:$B$99;LENGTE(K2))=K2)*($D$3:$D$99))

Of een matrix-formule met SOMMEN.ALS natuurlijk

Groet,
Jeroen

 

[HSV]

Er is géén K2 of J2 met gegevens Jeroen.

 

[Jezet]

Hoi Harry,

In de sheet voorbeeld.xlsx van 17.52 zit wel een naam in K2. K2 is in ieder geval de verwijzing naar een cel waar een (deel van een) bedrijfsnaam staat.

Groet,
Jeroen

 

[HSV]

Dat is een voorbeeldje van hoe het moet worden.
Zie post van 16:13 uur.

 

[p0ekie]

Thanks HSV. Die code werkt inderdaad goed. Voor nu kan ik in ieder geval verder.

Sylvester; als ik een Matrix functie gebruik dan duurt de berekening ervan erg lang.