Compare two sheets using ipaddress and port as index

D

djzah71

New Member
Joined
Feb 27, 2014
Messages
4
Hello.. having trouble getting this to work and I apologize for my ignorance as I imagine you get a ton of people asking you to help them.

I have the same columns on both sheets
AvailabilityStateReasonDestinationPort

I'm using Destination and Port as the Index (so they "should" be found on both sheets (of course it would be great if I could highlight the cell if missing on the other sheet)

What I'm trying to do is let me know what values under Availability, State and Reason are different.

Scenario...
I gather stats from an old server that hosts a bunch of applications and I move to a new box and I gather the same stats on the new server and I want to make sure they match (so no applications are broken) and it seems as though this should be a way to do it.

I have tried VLOOKUP and MATCH / INDEX with no luck but pretty sure I'm doing it wrong since I'm trying to use Destination and Port as the Key

Any help is appreciated in helping me learn how to attack this.

OLD mini-sheet example:
show-avail-state-reason-dest_20230809_1400.xlsx
ABCDE
1AvailabilityStateReasonDestinationPort
2unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10080
3unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10180
4availableenabledThe virtual server is available10.11.12.101443
5unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10280
6availableenabledThe virtual server is available10.11.12.102443
7unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10480
8availableenabledThe virtual server is available10.11.12.104443
9unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10780
10availableenabledThe virtual server is available10.11.12.107443
11unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10880
12availableenabledThe virtual server is available10.11.12.108443
13unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10980
14availableenabledThe virtual server is available10.11.12.109443
15unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11080
16availableenabledThe virtual server is available10.11.12.110443
17unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11180
18availableenabledThe virtual server is available10.11.12.111443
19unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11280
20availableenabledThe virtual server is available10.11.12.112443
21unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11380
22availableenabledThe virtual server is available10.11.12.113443
23unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11480
24availableenabledThe virtual server is available10.11.12.114443
25unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11580
26availableenabledThe virtual server is available10.11.12.115443
27unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11680
28availableenabledThe virtual server is available10.11.12.116443
29unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11780
30availableenabledThe virtual server is available10.11.12.117443
31unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11880
32availableenabledThe virtual server is available10.11.12.118443
33unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11980
34availableenabledThe virtual server is available10.11.12.119443
35unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12080
36availableenabledThe virtual server is available10.11.12.120443
37offline enabledThe children pool member(s) are down10.11.12.12125
38offline enabledThe children pool member(s) are down10.11.12.12225
39unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12380
40availableenabledThe virtual server is available10.11.12.123443
41unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12480
42availableenabledThe virtual server is available10.11.12.124443
43unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12580
44availableenabledThe virtual server is available10.11.12.125443
45unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12680
46availableenabledThe virtual server is available10.11.12.126443
47unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12780
48availableenabledThe virtual server is available10.11.12.127443
49unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.7780
50offline enabledThe children pool member(s) are down10.11.12.77443
old


NEW mini-sheet example:
show-avail-state-reason-dest_20230809_1400.xlsx
ABCDE
1AvailabilityStateReasonDestinationPort
2unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10080
3availableenabledThe virtual server is available10.11.12.100443
4unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10180
5availableenabledThe virtual server is available10.11.12.101443
6unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10280
7availableenabledThe virtual server is available10.11.12.102443
8unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10780
9availableenabledThe virtual server is available10.11.12.107443
10unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10880
11availableenabledThe virtual server is available10.11.12.108443
12unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10980
13availableenabledThe virtual server is available10.11.12.109443
14unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11080
15availableenabledThe virtual server is available10.11.12.110443
16unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11180
17availableenabledThe virtual server is available10.11.12.111443
18unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11280
19availableenabledThe virtual server is available10.11.12.112443
20unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11380
21availableenabledThe virtual server is available10.11.12.113443
22unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11480
23availableenabledThe virtual server is available10.11.12.114443
24unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11580
25availableenabledThe virtual server is available10.11.12.115443
26unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11680
27availableenabledThe virtual server is available10.11.12.116443
28unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11780
29availableenabledThe virtual server is available10.11.12.117443
30unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11880
31availableenabledThe virtual server is available10.11.12.118443
32unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11980
33availableenabledThe virtual server is available10.11.12.119443
34unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12080
35availableenabledThe virtual server is available10.11.12.120443
36offline enabledThe children pool member(s) are down10.11.12.12125
37offline enabledThe children pool member(s) are down10.11.12.12225
38unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12380
39availableenabledThe virtual server is available10.11.12.123443
40unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12480
41availableenabledThe virtual server is available10.11.12.124443
42unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12580
43availableenabledThe virtual server is available10.11.12.125443
44unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12680
45availableenabledThe virtual server is available10.11.12.126443
46unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12780
47availableenabledThe virtual server is available10.11.12.127443
48unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.7780
49offline enabledThe children pool member(s) are down10.11.12.77443
50unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.7880
51offline enabledThe children pool member(s) are down10.11.12.78443
new
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Does this Conditional Formatting do what you want?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

djzah71.xlsm
ABCDE
1AvailabilityStateReasonDestinationPort
2unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10080
3unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10180
4availableenabledThe virtual server is available10.11.12.101443
5unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10280
6availableenabledThe virtual server is available10.11.12.102443
7unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10480
8availableenabledThe virtual server is available10.11.12.104443
9unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10780
10availableenabledThe virtual server is available10.11.12.107443
11unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10880
12availableenabledThe virtual server is available10.11.12.108443
13unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10980
14availableenabledThe virtual server is available10.11.12.109443
15unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11080
16availableenabledThe virtual server is available10.11.12.110443
17unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11180
18availableenabledThe virtual server is available10.11.12.111443
19unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11280
20availableenabledThe virtual server is available10.11.12.112443
21unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11380
22availableenabledThe virtual server is available10.11.12.113443
23unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11480
24availableenabledThe virtual server is available10.11.12.114443
25unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11580
26availableenabledThe virtual server is available10.11.12.115443
27unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11680
28availableenabledThe virtual server is available10.11.12.116443
29unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11780
30availableenabledThe virtual server is available10.11.12.117443
31unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11880
32availableenabledThe virtual server is available10.11.12.118443
33unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11980
34availableenabledThe virtual server is available10.11.12.119443
35unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12080
36availableenabledThe virtual server is available10.11.12.120443
37offline enabledThe children pool member(s) are down10.11.12.12125
38offline enabledThe children pool member(s) are down10.11.12.12225
39unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12380
40availableenabledThe virtual server is available10.11.12.123443
41unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12480
42availableenabledThe virtual server is available10.11.12.124443
43unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12580
44availableenabledThe virtual server is available10.11.12.125443
45unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12680
46availableenabledThe virtual server is available10.11.12.126443
47unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12780
48availableenabledThe virtual server is available10.11.12.127443
49unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.7780
50offline enabledThe children pool member(s) are down10.11.12.77443
old
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:C50Expression=COUNTIFS(new!$D$2:$D$51,$D2,new!$E$2:$E$51,$E2,new!A$2:A$51,A2)=0textNO


djzah71.xlsm
ABCDE
1AvailabilityStateReasonDestinationPort
2unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10080
3availableenabledThe virtual server is available10.11.12.100443
4unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10180
5availableenabledThe virtual server is available10.11.12.101443
6unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10280
7availableenabledThe virtual server is available10.11.12.102443
8unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10780
9availableenabledThe virtual server is available10.11.12.107443
10unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10880
11availableenabledThe virtual server is available10.11.12.108443
12unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.10980
13availableenabledThe virtual server is available10.11.12.109443
14unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11080
15availableenabledThe virtual server is available10.11.12.110443
16unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11180
17availableenabledThe virtual server is available10.11.12.111443
18unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11280
19availableenabledThe virtual server is available10.11.12.112443
20unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11380
21availableenabledThe virtual server is available10.11.12.113443
22unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11480
23availableenabledThe virtual server is available10.11.12.114443
24unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11580
25availableenabledThe virtual server is available10.11.12.115443
26unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11680
27availableenabledThe virtual server is available10.11.12.116443
28unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11780
29availableenabledThe virtual server is available10.11.12.117443
30unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11880
31availableenabledThe virtual server is available10.11.12.118443
32unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.11980
33availableenabledThe virtual server is available10.11.12.119443
34unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12080
35availableenabledThe virtual server is available10.11.12.120443
36offline enabledThe children pool member(s) are down10.11.12.12125
37offline enabledThe children pool member(s) are down10.11.12.12225
38unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12380
39availableenabledThe virtual server is available10.11.12.123443
40unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12480
41availableenabledThe virtual server is available10.11.12.124443
42unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12580
43availableenabledThe virtual server is available10.11.12.125443
44unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12680
45availableenabledThe virtual server is available10.11.12.126443
46unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.12780
47availableenabledThe virtual server is available10.11.12.127443
48unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.7780
49offline enabledThe children pool member(s) are down10.11.12.77443
50unknown enabledThe children pool member(s) either don't have service checking enabled, or service check results are not available yet10.11.12.7880
51offline enabledThe children pool member(s) are down10.11.12.78443
new
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:C51Expression=COUNTIFS(old!$D$2:$D$50,$D2,old!$E$2:$E$50,$E2,old!A$2:A$50,A2)=0textNO
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
How to look up data from on sheet to another when both sheets have multiple cells with the same ID number.
Replies
5
Views
295
hagia_sofia
hagia_sofia
M
  • Solved
Comparing two columns on different sheets and deleting the entire row if any cell is different
Replies
9
Views
375
Logit
Logit
L
  • Solved
Using CELL function result as part of range in an AVERAGE formula
Replies
3
Views
351
Fluff
Fluff
J
  • Question Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
389
Jagadeshrao
J
G
  • Solved
How to filter a table with two email columns against a list of emails
Replies
7
Views
731
dreid1011
dreid1011

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top