[EXC/ACCES] Geavanceerd sorteren

[GenetiX]

In Excel heb ik een gigantische lijst met combinaties van bepaalde codes. Hieronder een voorbeeld

ROW 1	X1	X2	X3	X4
ROW 2	X3	X2	X4	X1
ROW 3	D1	D2	--	--
ROW 4	X2	X4	--	--
ROW 5	D2	D1	--	--
ROW 6	X9	X2	X5	--

Omdat deze lijst zo enorm is wil ik een bepaalde sortering toepassen. In principe is iedere zelfde combinatie van codes gelijk. ROW1 = ROW2 en ROW3 = ROW5 dus. Lastige in deze hele situatie is dat het aantal kolommen niet bij iedere row gelijk is. Er kan één code in een row staan, maar ook 20. ROW 4 mag dus niet gelijkgesteld worden aan ROW1.

Output moet een lijst zijn met gelijkstaande codes en de mate waarin deze voorkomt. De eerste 'hit' mag dan leading zijn, als voorbeeld van een code combinatie. Bijvoorbeeld;

ROW 1	X1	X2	X3	X4	433
ROW 2	D2	D1			988

Standaard Excel en/of SQL functies in Access voldoen niet denk ik. Misschien met een stuk VB code?

[Gomez12]

Denk ik nou te simpel of is het gewoon in sql iets als

SQL:

select left(row1,1) as lrow1,left(row2,1) as lrow2, ...., count (1) as aantal
group by lrow1,lrow2,lrow3,lrow4,lrow5,lrow6,lrow7....

gebruiken, want je bedoelt toch dat het alleen maar om het aantal kolommen gaat waar het 1e karakter gelijk is???

[Verwijderd]

Betekent het "433 resultaat" dat deze exacte combinatie (ook qua volgorde) 433x voorkomt?

In dat geval volstaat een simpele formule (in "hokje 433") als (stel hokje met ROW1 in onderste tabel is A1):

{=count(if($B$1:$E$1000=B1:E1,$B$1:$E$1000))}

[GenetiX]

Verwijderd schreef op dinsdag 04 december 2007 @ 23:59:
Betekent het "433 resultaat" dat deze exacte combinatie (ook qua volgorde) 433x voorkomt?

In dat geval volstaat een simpele formule (in "hokje 433") als (stel hokje met ROW1 in onderste tabel is A1):

{=count(if($B$1:$E$1000=B1:E1,$B$1:$E$1000))}

Nee, het kan iedere combinatie van gelijksoortige codes zijn. Dus bij twee variabelen staat X1;X2 gelijk aan de combinatie X2;X1. Voor het overzicht mag de 'eerste' combinatie dan leading zijn en in het overzicht gebruikt worden als indicatie van het soort combinatie

[Verwijderd]

OK, in dat geval gaat het niet werken met een Excel functie en zul je VBA moeten gebruiken. Ik ga ervan uit dat hier wel een aantal VB goeroes meeleest dat je hiermee kan helpen.

Mocht dat overigens niet lukken dan kan ik je hiervoor het volgende forum aanraden:

http://www.mrexcel.com/board2/index.php

[KingRichard]

De methode van grizzlybeer werkt wel (al moet 'ie wel anders) als de codes per rij gesorteerd zijn. Dat kun je doen door de volgende VBA-code uit te voeren:

Visual Basic:

Sub SortPerRow()
Dim rngRow As Range
For Each rngRow In ActiveSheet.UsedRange.Rows
    rngRow.Sort Key1:=rngRow.Rows(1), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
Next rngRow
End Sub

Deze code gaat er vanuit dat ROW 1 uit je voorbeeld niet daadwerkelijk in een cel staat. Mocht dat wel zo zijn, verander dan Header:=xlNo in Header:=xlYes. X1 uit je voorbeeld staat dus in A1.
Als alles gesorteerd is, kun je met deze formule:

code:

{=COUNT(IF(CONCATENATE($A$1:$A$1000,$B$1:$B$1000,$C$1:$C$1000,$D$1:$D$1000)=CONCATENATE(A1,B1,C1,D1),1))}
{=COUNT(IF(CONCATENATE($A$1:$A$1000,$B$1:$B$1000,$C$1:$C$1000,$D$1:$D$1000)=CONCATENATE(A2,B2,C2,D2),1))}

de aantallen tellen. Let erop dat dit een array-formule is.

[GenetiX]

De VBA oplossing van KingRichard werkt prima. Met een pivottable een overzicht gemaakt met het aantal hits per combinatie.

[Verwijderd]

OK, opgelost dus. Perfect!!