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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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

G
  • Solved
How to filter a table with two email columns against a list of emails
Replies
7
Views
354
dreid1011
dreid1011
A
  • Solved
Comparing two excel files for matching / mismatching rows (long text strings - match() wont work
Replies
4
Views
562
ahaye
A
J
  • Question
Scraping data from WIFI networks that are available
9 10 11
Replies
109
Views
5K
Prome3
P
JeffGrant
  • Solved
When is a workbook too big?
2
Replies
11
Views
1K
Michael M
Michael M
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
405
AndyEd
A

Forum statistics

Threads
1,215,084
Messages
6,123,029
Members
449,092
Latest member
ikke

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