Excel: Sammenligne store lister

[deaktivert443556]

Jeg har rapport med ca 350.000 linjer, og en veldig lik rapport med ca 330.000 linjer. Begge rapportene skulle ha hatt 350.000 linjer, så jeg må finne de 20.000 som mangler og gjøre de nødvendige grepene for at også disse skal komme med i fremtidige rapporter.

 

Tilsvarende oppgave har jeg løst tidligere ved å bruke ANTALL.HVIS i den største filen, men problemet er at beregningen av disse 350.000 formlene tar naturlig nok veldig lang tid (litt i overkant av en time sist gang).

 

Finnes det en mer effektiv måte å sammenligne slike lister? Kanskje er ikke Excel det beste verktøyet...?

 

 

Jeg bruker Excel 2010 - Norsk

[Harald Staff]

Man kan skrive en makro som utfører dette på noen minutter. Men makroskrivingen tar fort minst en time, så det spørs jo hva du regner som kosteffektivt. Mao, Hvor ofte skal du gjøre dette, og er oppsettet det samme hver gang (som i unike verdier i samme kolonner) ?

 

En time er ekstremt med en formelløsning uansett. Lukk og omstart Excel, og ha bare de to filene åpne med ANTALL.HVIS bare i den ene. Hvis du har andre greier kjørende samtidig, en pivottabell eller en haug betingede formateringer, senkes regnekraften ekstremt.

 

Hjelper deg gjerne med makroskrivingen, men prøv formelløsningen i et nøkternt miljø først.

 

beste hilsen Harald

[deaktivert443556]

Den ble kjørt under tilnærmet optimale forhold så jeg tror ikke det er så mye å hente på ressursbesparelser.

 

En time+ med kodeskriving for denne funksjonen høres veldig omfattende ut. Med mindre prinsippet med makroen kan forklares i enkle trekk og at jeg klarer å sette det sammen selv, så tror jeg ikke vi skal bruke noe tid på det.

 

Nå som jeg tenker litt, så lurer jeg på om det kanskje er litt tid å spare på hvordan formelen er utformet. Jeg brukte en formel som lette gjennom alle 330.000 verdiene hver eneste gang, men det er kanskje ikke nødvendig.

 

Sett at det er to kolonner med verdier hvor den ene (kolonne A) har 350.000 verdier og den andre (kolonne C) har 330.000.

 

Begge sorteres i stigende rekkefølge og jeg flytter dataene i C slik at disse starter på rad 20.000. Begge stopper altså på rad 350.000, men kolonne A starter på rad 1.

 

Jeg tenker da følgende formel

=Antall.hvis(c1:c$350000;a1) - Leter gjennom 350.000 celler, inkl 20.000 tomme

I rad 2 blir det da

=Antall.hvis(c2:c$350000;a2) - Leter gjennom 349.999 celler, inkl 19.999 tomme

I rad 300.000 blir det

=Antall.hvis(c300000:c$350000;a300000) - Leter gjennom 50.000 celler, ingen tomme.

 

For alt jeg vet går dette enda tregere, men det blir interessant å teste.

 

[Harald Staff]

Jeg får ikke makroen til å jobbe fort nok med så store datamengder heller. Vi er over en eller annen usynlig grense tror jeg.

 

Harald

[Gjest]

Ville det ikke vært bedre å lagre dataene til en CSV fil og brukt et annet språk til å gå igjennom dette en Excel om den kræsjer eller ikke klarer å håndtere dette?

[Harald Staff]

Vi kan ikke gi op så lett.

 

Forarbeid: I VB-editoren, Insert > Class module. Gi den navn cTing og følgende innhold:

Public Tekst As String

Så, i en standard modul:

Sub Test2()
Dim Rng1 As Range, Rng2 As Range, RngX As Range
Dim Cel As Range, Hit As Range
Dim List1 As Worksheet, List2 As Worksheet
Dim C As Long
Dim Coll As New Collection
Dim Ting As cTing
Dim Miss As Long

On Error Resume Next
Set Rng1 = Application.InputBox("Klikk en celle i id-kolonnen i den lange lista:", Type:=8)
If Rng1 Is Nothing Then Exit Sub
Set Rng2 = Application.InputBox("Klikk en celle i id-kolonnen i den korte lista:", Type:=8)
If Rng2 Is Nothing Then Exit Sub
Set List1 = Rng1.Parent
Set Rng1 = Intersect(List1.Columns(Rng1(1).Column), List1.UsedRange)
Set List2 = Rng2.Parent
Set Rng2 = Intersect(List2.Columns(Rng2(1).Column), List2.UsedRange)

Set RngX = Application.InputBox("Klikk en celle i kolonnen i den lange lista hvod vi skriver mangler:", Type:=8)
If RngX Is Nothing Then Exit Sub
C = RngX(1).Column

For Each Cel In Rng2
    Set Ting = New cTing
    Ting.Tekst = Cel.Value
    
    On Error Resume Next
    Coll.Add Ting, Ting.Tekst
    On Error GoTo 0
    Set Ting = Nothing
Next
'MsgBox Coll.Count

For Each Cel In Rng1
    If Cel.Row Mod 1000 = 0 Then
        Application.StatusBar = Cel.Row
        DoEvents
    End If
    On Error Resume Next
    Set Ting = Coll(Cel.Value)
    On Error GoTo 0
    If Ting Is Nothing Then
        List1.Cells(Cel.Row, C).Value = "Mangler"
        Miss = Miss + 1
    Else
'        List1.Cells(Cel.Row, C).Value = Ting.Tekst
    End If
    Set Ting = Nothing
Next
MsgBox Miss & " mangler av " & Rng1.Count
Set Coll = Nothing
DoEvents
Application.StatusBar = False
End Sub

Her kjører den på 16 sekunder, men min Excel 2007 trenger en pust i bakken i etterkant, henger litt.

 

Beste hilsen Harald

[deaktivert443556]

Flotte greier! Jeg fikk riktignok "Mangler" på alt som bare består av tall. Det hjalp ikke å formatere alt som tekst, men det fungerte bra da jeg lagde to hjelpekolonner hvor jeg bare la til en tilfeldig tekst til de eksisterende verdiene. Så i stedet for "123" ble det "ASDF123".

 

Takker!

[Harald Staff]

Pussig. Tallkonverteringen til tekst skulle skjedd via Public Tekst As String. Men det er bare teori. Skal du kjøre dette hyppig kan du eksperimentere med sånt som Ting.Tekst = Cstr(Cel.Value), eller kanskje heller la koden legge en bokstav foran selv, Ting.Tekst = "A" & Cel.Value ...husk det også her i så fall: Set Ting = Coll("A" & Cel.Value)

 

Fint det funket i hvert fall. Takk for tilbakemelding!

 

Beste hilsen Harald

[deaktivert443556]

Når disse dataene hentes ut fra en spørring, så er alle tallene i utgangspunktet tekst, jeg hadde bare tilfeldigvis konvertert dem til tall denne gangen. Makroen vil trolig fungere om jeg bare lar dem forbli tekst.

 

Nå som det er såpass lite tidskrevende å gjøre denne jobben, så skal jeg definitivt gjøre det oftere.