Formule voor het aanpassen van IP-adressen

[NanceV]

Wie heeft de oplossing voor het volgende:

Ik heb 2 excelsheets die ik samen wil voegen.
Dit zou op basis van IP-adres, welke beide sheets bevatten vrij makkelijk moeten kunnen door middel van verticaal zoeken, echter zijn deze gegevens niet gelijk. Er zit tussen de IP-adressen 1 cijfer verschil.
Fictief voorbeeld: 32.00.100.10 in de ene sheet, 32.00.100.09 in de andere sheet. Er zijn echter ook IP-adressen die beginnen met 3 cijfers en die eindigen met 3 cijfers en tussenliggende cijfercombinaties zijn ook mogelijk.
Doordat het tekst notaties zijn en geen getal notaties is het niet mogelijk om -1 of +1 toe te passen in 1 van beide sheets.

Is er een formule om de adressen toch gelijk aan elkaar te krijgen, zodat het verder samenvoegen van de bestanden via verticaal zoeken plaats kan vinden?

 

[AlexCEL]

Welkom. Plaats s.v.p. een representatief Excel-voorbeeld bestand voor meer assistentie. Nu moeten we gokken.

 

[NanceV]

Voorbeeldbestanden

Hierbij de gevraagde voorbeeld bestanden

Ik wil dus de gegevens uit voorbeeldbestand2 toevoegen aan voorbeeldbestand1, maar doordat de IP-adressen niet gelijk zijn kan ik dit dus niet 1-op-1 doen d.m.v. verticaal zoeken.
Heb nu even een paar regels in de voorbeeldbestanden geplaatst. Het gaat om meer gegevens uiteindelijk.

 

[AlexCEL]

Bij een voorbeeldbestandje is het ook handig dat je even voor een aantal rijen (handmatig) aangeeft wat je als uitkomst verwacht en waar...

Wat ik gedaan heb in het kort:
1. Het tabblad uit voorbeeldbestand2 naar voorbeeldbestand1 gekopieerd. Dat is veel gemakkelijker dan opzoeken in verschillende bestanden.
2. In kolom J staat een formule die de punten uit de IP-adressen haalt en met elkaar vergelijkt. Bij verschil van 1 krijg je het bijbehorende rijnummer terug.
3. De INDEX/VERGELIJKEN combinaties zoeken dan alle bijbehorende gegevens op.

Let op: als je geen Excel 365 gebruikt moet je alle functies in rij 2 eerst afsluiten met Control+Shift+Enter en daarna doorvoeren naar beneden. Het zijn zogenaamde matrixfuncties.

 

[NanceV]

Eenvoudige aanpassing IP-adressen

Beste AlexCEL, ik waardeer de moeite die je neemt.
Het gaat in deze niet zo zeer over welke gegevens uit document2 in document1 dienen te komen.
Het gaat er om dat de IP-adressen uit de beide documenten nu op het laatste cijfer uit het IP-nummer nu afwijkt van elkaar en ik ben op zoek naar een formule om 1 van de 2 IP-adressen aan te kunnen passen.
Indien in document1 dan zou het IP-adres -/- 1 zijn en indien het adres in document2 aangepast zou worden zou het dus IP-adres + 1 zijn.
Maar omdat het IP-adres het format "tekst" heeft kan je hier dus geen som op loslaten. En converteren naar getal zorgt ervoor dat de punten ertussen verdwijnen, terwijl de positie van de punt nogal cruciaal is in een IP-adres.

Mijn vraag is dus of er een formule is waarbij eenvoudig de IP-adressen in 1 van de documenten aangepast kan worden, zodat deze als zoekwaarde gebruikt kan worden om het samenvoegen via verticaal zoeken uit voeren.

 

[AlexCEL]

Het antwoord zou je uit bijgevoegde voorbeeld in #4 kunnen halen. Delete alles behalve kolom J en je hebt je zoeksleutel.

"Waarden aanpassen" kan alleen met VBA (programmeerwerk), niet met formules.

 

[NanceV]

Bedankt AlexCEL

Bedankt AlexCEL, op deze manier gaat het blijkbaar nog makkelijker dan op de manier dat ik het wilde doen. Op deze manier hoef ik niet eerst de IP-adressen in 1 van de bestanden aan te passen en vervolgens de gegevens via verticaal zoeken over te zetten.

 

[popipipo]

Als je alle IP adressen onder elkaar zet en daarna hierop sorteert dan staan ze keurig onder elkaar.

 

[NanceV]

Reactie op popipipo

Beste popipipo, Dat klopt, ware het niet dat beide echte bestanden (dus niet mijn meegestuurde geficteerde bestanden) niet het zelfde aantal IP-adressen hebben en we juist uit proberen te zoeken welke gegevens aan beide kanten ontbreken, om een volledige inventarisatie te krijgen.
Bij alleen op waarde sorteren zal er dus geen volledig kloppend bestand uit komen.