Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel 2000: Conditional Formatting: Match Values

Status
Not open for further replies.

archwayguy

Technical User
Oct 30, 2008
58
US
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.
 
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?
 
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
 
I found this:
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?
 
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
 
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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top