Excel 2000: Conditional Formatting: Match Values

[archwayguy]

I have a very large spreadsheet full of network data. I need to cross-reference source A with source B. Basically, I have a physical location in Column A, then an IP address in Column B and C. I need B & C to match and for those ip addresses to stay within their matching locations with Column A. I'm looking at conditional formatting, but I have no idea how to do this or where to begin, and the formulas I've found either don't apply or I can't figure out. Someone please help.

Spreadsheet looks something like this:

A B C
Site IP1 IP2
New York 192.168.1.1 192.168.1.4
New York 192.168.1.3 192.168.1.1

I need the values in B & C to pair up on their duplicates, and for them to stay in their respective sites and not roam around in the sheet and get out of order. That's very important.

 

[archwayguy]

I tried Data>Advanced Filter with a test sheet, and I selected two columns of IP addresses and set it to Filter the list in place, and set the list range from $B$2:$C$47 with unique records only. Nothing happened. What am I doing wrong here?

 

[Gavona]

That would only hide rows where the contents of both colB and ColC were identical to the contents of another row. eg the red row below:

192.168.1.1 192.168.1.4
[red]192.168.1.1 192.168.1.4[/red]
192.168.1.4 192.168.1.22

Gavin

 

[archwayguy]

I found this:

http://nerds-central.blogspot.com/2008/05/column-uniquing-in-excel-via-vbscript.html

I ran it on a test with just one site and it worked like a champion. Problem is, I'll have to do it site by site as it won't discriminate between the locations column and I don't want to get the ip ranges all jumbled. Any idears on how to get it to work even better?

 

[Gavona]

You are in the wrong forum for a code solution. Forum707.

Easiest perhaps to join (concatenate) Site and IP Address.
D2==A2&" | "&B2
E2=A2&" | "&C2

Then use either my suggestion above or the code you mentioned.



Gavin

 

[archwayguy]

Well, thanks for the prodding at least.

 

[SkipVought]

My Resultset
[tt]
site IP_1 IP_2
New York 192.168.1.1
New York 192.168.1.2
New York 192.168.1.2 192.168.1.2
New York 192.168.1.22
New York 192.168.1.3
New York 192.168.1.4
New York 192.168.1.4 192.168.1.4
New York 192.168.1.7
[/tt]
My solution: MS Query faq68-5829.
[tt]
SELECT A.site, A.IP_1, B.IP_2
FROM
`IPs$` A
, `IPs$` B
Where A.site=B.site
AND A.IP_1=B.IP_2

UNION

SELECT A.site, A.IP_1, ''
FROM
`IPs$` A

Where A.IP_1 NOT IN (Select B.IP_2 From `IPs$` B
Where A.site=B.site AND B.IP_2 IS NULL)

UNION

SELECT B.site, B.IP_2,''
FROM
`IPs$` B

Where B.IP_1 NOT IN (Select A.IP_1 From `IPs$` A
Where A.site=B.site AND A.IP_1 IS NULL)

[/tt]
This accounts for different sites.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]