Vaske lister i excel, definere fellesnevner i begge lister

[valentino]

Hei.

 

I forbindelse med jobben har vi en avtale med et annet firma som våre ansatte nyter godt av. Det hender at folk slutter i vår organisasjon og finner nye spennende utfordringer og i den forbindelse må vi fjerne de fra "vår" konto hos leverandøren av tjenesten. Det er en stor organisasjon og automatisk utmelding ved oppsigelse er ikke mulig. Det alternativet jeg har er å vaske listen manuelt for å finne frem til hvem som har forlatt vår organisasjon. Jeg leter etter personer som er i leverandørens liste, men ikke i vår. Jeg tenker at det letteste er å fjerne alle som enda er aktive hos oss. De som da står igjen på leverandørens liste har sannsynligvis sluttet.

 

Fakta:

 

Ansatte i vår organisasjon: over 2000

Antall personer som er registrert hos leverandøren: ca 200

Office 2003, Norsk, Windows XP.

 

For å kunne vaske disse listene må jeg ha noe som er likt i begge listene. Jeg prøvde i første omgang å bruke fornavn + etternavn som utvelgelseskriterium, men jeg fant fort ut at det ikke var helt gunstig. (benyttet finn.rad) Dette på grunn av at vi har flere med samme navn i vår organisasjon, og at navnene er stavet ulikt i de to registrene. I denne omgangen har jeg fått fødselsnummer i en liste og fødselsdato i den andre listen.

 

Eksempel:

Liste fra leverandør:

02027012345 Per Persen (personnr. ren tekst)

 

Liste fra egen organisasjon:

22.02.1970 Per O. Persen (dato formatert som dato)

 

Mål:

Få gjort personnr. om til kun fødselsdato i listen fra leverandør.

 

Dette har jeg gjort:

Tekst til kolonner:

22 | 2 | 70 | 12345

 

Fjerner de 5 siste da jeg ikke har bruk for de, og legger til 19 foran årstallet slik at det blir som følger:

22 | 2 | 1970

 

(jeg må splitte det opp slik og definere 19 selv fordi det kommer årstall 2070, 2270, 2470 eller lignende dersom jeg bare tar bort de 5 siste og formaterer som dato)

 

 

Brukt &"."& formelen og kopiert resultatet, limt inn verdier for å få det som ren tekstdata. Har også formatert cellen som dato. Da blir det som følger:

22.2.1970

 

 

Dette ser tilsynelatende likt ut som det står i vår liste, men Excel registrerer de ikke som like. Dette på grunn av at den datoen jeg har satt sammen fra fødselsnummeret ikke blir registrert som dato selv om jeg har gjort den nødvendige formateringen. Dette vises ved at datoen fra vår egen liste er høyrestillt i cellen, mens den sammensatte datoen fra leverandøren blir venstrestillt. Jeg har funnet ut at dersom jeg velger innholdet i cellen (med dato fra leverandør) fra funksjonslinjen og trykker enter så blir innholdet høyrestillt og registrert som likt som datoen fra vår liste. Jeg har da ikke gjort noen endringer i innholdet, bare velger meg inn i cellen og trykker enter.

 

Hva kommer dette av?

 

Jeg ser for meg to mulige løsninger, men har ikke kunnskaper nok til å gjennomføre de selv.

1. Lage en makro som kan trykke enter i alle 200 cellene med feil-registrert dato for at de skal registreres rett. Hvordan skal jeg gå frem for at dette går automatisk? Jeg har ikke lyst å jobbe meg manuelt gjennom 200 oppføringer.

 

2. Endre fremgangsmåten for konvertering fra fødselsnummer til fødselsdato slik at formateringen blir rett. Jeg vet ikke hvordan, men kom med forslag så kan jeg prøve.

 

Alle forslag mottas med takk.

Endret 26. april 2010 av valentino

[Harald Staff]

Hei. Tekst blir dessverre ikke tall uansett hvordan du formatterer. En måte, etter din tekst til kolonner, prøv en formel som

=DATO(1900+C2;B2;A2)

så gjerne kopier resultatene, Rediger > Lim inn utvalg > Verdier.

 

En annen løsning for konvertering er å først fjerne de overskytende 5 tegnene fra personnummer, via =VENSTRE(A1;6)

Kopier resultatene, Rediger > Lim inn utvalg > Verdier.

 

Edit: Glem dette, du behersker jo tekst til kolonner. Gjør dette istedet og fjern personnr:

220270 | 12345

 

Så merk de sekssifrede datoene, Data > Tekst til kolonner > Neste > Neste > velg Dato og DMÅ, Fullfør.

 

HTH. Beste hilsen Harald

Endret 27. april 2010 av Harald Staff

[valentino]

Hei. Tekst blir dessverre ikke tall uansett hvordan du formatterer. En måte, etter din tekst til kolonner, prøv en formel som

=DATO(1900+C2;B2;A2)

så gjerne kopier resultatene, Rediger > Lim inn utvalg > Verdier.

 

HTH. Beste hilsen Harald

 

Hei.

 

Først, takk for svært raskt svar. Denne løsningen som du skisserer fungerte utmerket. Nå registrerer Excel at like datoer faktisk er lik. Nå går det som en drøm å vaske listen.

 

Den lille jobben som gjenstår er mulig å gjøre manuelt. Da tenker jeg på at det er født flere personer på samme dato samme år i vår organisasjon. Det er dog ikke så mange at det vil være noe problem pr. nå tror jeg.

[Harald Staff]

Den er god.

Du kan bli oppmerksom på dubletter med en hjelpekolonne, eksempel med datoer i C:

 

=HVIS(ANTALL.HVIS($C$1:$C$5000;C1)>1;ANTALL.HVIS($C$1:$C$5000;C1);"")

fyll ned.

 

Beste hilsen Harald

[valentino]

Den er god.

Du kan bli oppmerksom på dubletter med en hjelpekolonne, eksempel med datoer i C:

 

=HVIS(ANTALL.HVIS($C$1:$C$5000;C1)>1;ANTALL.HVIS($C$1:$C$5000;C1);"")

fyll ned.

 

Beste hilsen Harald

 

Hei.

Jeg har sortert ut alle duplikater, men jeg brukte enkelt og greit bare =Antall.hvis(C:C;C2)

Sorterer deretter etter antall oppføringer for å få dublikatene for seg selv. De har jeg gått gjennom manuelt. Synes dette var mindre avansert til dette formålet, men jeg har brukt din fremgangsmåte i andre sammenhenger.

[valentino]

Jeg kan slenge på ett oppfølgingsspørsmål til den listevaskingen min.

 

Kunne jeg løst problemstillingen med de doble oppføringene lettere ved å søke på kombinasjonen av dato og etternavn. Det at flere personer er født på samme dato og etternavn er vel ganske liten sannsynlighet, med unntak av tvillinger da selvsagt. Fornavn kan jeg ikke bruke som et automatisert parameter da det ofte er skrevet ulikt i de to aktuelle databasene.

 

Jeg tenker noe sånn som =OG(A1=D1;B1=E1), bare mer dynamisk slik at den søker i tillegg. Problemet jeg ser for meg er at Finn.rad bare tar den første og beste som er lik. Dersom det er dobbel oppføring av et parameter vil ikke denne funksjonen gjøre deg oppmerksom på det.

[Harald Staff]

Det enkleste da, og vanligste, er å lage en hjelpekolonne som består av etternavn førdselsdato, a la:

 

=A2&" "&TEKST(B2;"dd.mm.åååå")

 

Så kan du enten gjøre alle FINN.RADene der (du får sikkert mye bøll med ikke matchende etternavn da) eller bedre; la antall datoer (her A2) avgjøre hvor det skal slås opp. (Dato i B2, Navn-Dato i C2):

 

=HVIS(A2=1;FINN.RAD(B2;Datotabell;2;USANN);FINN.RAD(C2;NavnDatoTabell;2;USANN))

 

så skal du slippe å sortere. I teorien da.

 

Beste hilsen Harald

Endret 27. april 2010 av Harald Staff

[valentino]

Hei igjen.

 

Takk for innspill. Det enkle er ofte det beste og det er selvsagt helt supert å kombinere teksten fra to celle for å søke på kombinasjonen. Nå lærte jeg hvordan =tekst() kommandoen funger også.

 

Den litt mer avanserte formelen din ser også veldig grei ut. Jeg kan bruke en =hvis(finn.rad()>1;B1&" "&Tekst(A1;dd.mm.åååå);"") får å få alle duplikater som sammensatte verdier i egen kolonne og bruke det som NavnDatoTabell.

[Harald Staff]

Bra. Siste innspill på en stund da er å bruke TRIMME rundt etternavnene for å fjerne mellomrom som veldig ofte dukker opp i slutten av tekstfelter:

 

TRIMME(B1)&" "&TEKST(A1;"dd.mm.åååå")

 

Beste hilsen Harald