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.

 

[SkipVought]

Hi,

"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."

How could they "roam around in the sheet and get out of order" unless you explicitly did something to make that happen?

Are the values in column B and column C identical? WHY have two identical columns?

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

 

[archwayguy]

I don't know if they will move or not. I haven't done anything yet that can't be undone. B & C have some identical values, but not all. I have two different data pools and I need to know which is accurate. If they match, then they are up to date, if they don't match, then Column C takes precedence. Make sense?

 

[SkipVought]

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."

and

"B & C have some identical values, but not all. I have two different data pools and I need to know which is accurate. If they match, then they are up to date, if they don't match, then Column C takes precedence."

These statements are totally different!!!

I am confused!!!

Exactly what do you want to happen??? EXACTLY.

Please be CLEAR, CONCISE and COMPLETE.


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

 

[archwayguy]

I need to pair the old data in Column B with the new data in Column C.

If something is in C and not in B, it should just leave that value and put a blank cell in Column B beside it.

If it has the same value in B & C, it should put them both in the same row, side by side.

C is larger (has more cells) than B, as some changes and additions have been made. Will this affect the outcome or possibility of doing this?

 

[mintjulep]

I have two different data pools and I need to know which is accurate. If they match, then they are up to date, if they don't match, then Column C takes precedence. Make sense?

No, not really.

But if if you know that Column C takes precedence, then you must already know that Column C is up to date. Right?

So why do you need to compare anything?

 

[archwayguy]

Because a vast majority of the information has changed. If I can match the data from B & C, then I will know what is an addition and what is a change. I have a second database that is going to be updated with the change information.

 

[SkipVought]

I will know what is an addition and what is a change"

How is that?

Please define how you will identify an ADDITION and how you will identify a CHANGE?

It seems that waht you want is another column to reflect these conditions. Conditional Formatting will not be adequate to, identify a "second database that is going to be updated with the change information"

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

 

[mintjulep]

So in your example in your first post the IP 192.168.1.1 is associated with "New York" in both column B and column C.

Is this a change?

Does the location data in column A come from the same data source as the IP addresses in column B, or in column C?

 

[archwayguy]

No, the site column remains the same, it's just for reference and cataloging. Only the ip address data has changed. Column B is old data, and Column C is the most up to data from the latest live network response. I need to match B & C so I can find old devices that no longer exist and prune them out, as well as update the list with new devices that didn't previously exist.

 

[mintjulep]

No, the site column remains the same". The same as what?

You previously wrote "I need B & C to match and for those ip addresses to stay within their matching locations with Column A"

Is A important or not?

Where is the link between a location and an IP address defined?

 

[SkipVought]

So you STILL have not explained exactly WHAT you want as a SPECIFIC result?

When B=C WHAT?
When B is empty WHAT?
When B<>C WHAT?

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

 

[Gavona]

If you have a second database to update with the change info then I suggest that you don't really want highlighting (conditional formatting) you want to create a flag in a new column (or columns) to indicate if there is a change.

Conditions to test for could be:
1) IP address in IP1 against more than one site ("Multiple Sites IP1")
2) IP address in IP2 against more than one site ("Multiple Sites IP2")
3) Both conditions 1 & 2 apply
4) IP address in IP1 not in IP2 ("Old Device")
5) IP address in IP2 not in IP1 ("New Device")
6) IP address in both lists against same site ("no change"
7) IP address in both lists against different site ("Site change")

Which of the above do you need to test for? What else? What do you want to appear in the Flag column(s) for each condition?

Gavin

 

[archwayguy]

Le sigh. I have three columns. One is site/location, one is an old list of ip addresses, and the last is a new list of ip addresses.

I want to take both columns of ip address and sort them so that the duplicates (i.e. those address that have not changed or are not new) are next to each other, and those that don't have duplicates (or are new) have a blank cell next to it. I don't know how to do this.

 

[SkipVought]

[tt]
=if(b2=c2,1,0)
[/tt]
and copy down.

all the ONEs are identical.

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

 

[archwayguy]

So, original would look something like this:

New York 192.168.1.1 192.168.1.4
New York 192.168.1.2 192.168.1.7
New York 192.168.1.3 192.168.1.2
New York 192.168.1.4 192.168.1.22

And after sorting it would look something like this:

New York 192.168.1.1
New York 192.168.1.2 192.168.1.2
New York 192.168.1.3
New York 192.168.1.4 192.168.1.4
New York 192.168.1.7
New York 192.168.1.22

Are we on the same page now? Can Excel do this?

 

[SkipVought]

HUH???

My formula returned ZEROs for each of the 4 rows.

Sorting on that column does not change the order of the data!!!

How did your table change?

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

 

[SkipVought]

Sir, if you would use understandable terminology and describe what you are looking for clearly, we would not have wasted your time and mine.

Please explain the business case for this exersize.

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

 

[archwayguy]

I didn't mean I sorted the list and that's what I got, I mean that's what I want it to look like when it's done. If I could even hide the ones that match/duplicate that would be fantastic and make it much easier to finish this. I need the list for identification and inventory. It's outdated and it needs to be updated. I have a full list of updated addresses and the sites they are from, but I need to be able to match those to the old list so I can see what has changed/needs to be changed, and what needs to be added or removed. How am I only making sense to myself?

 

[Gavona]

Your example helps! How about this:

[tt]Site         IP1           IP2    FlagOld FlagNew
New York     192.168.1.1 192.168.1.4 old
London 192.168.1.2 192.168.1.7 New
New York     192.168.1.3 192.168.1.2 old
New York     192.168.1.4 192.168.1.22 New[/tt]

Formulae (copied down):
D2=IF(ISNA(VLOOKUP(B2,$C$1:$C$16,1,0)),"old","")
E2==IF(ISNA(VLOOKUP(C2,$B$1:$B$16,1,0)),"New","")

Sort on the 2 new columns.

Gavin