Unique Values and missing values

[dmfweb]

I have 5 different systems I manage and I am trying to figure out which serials are missing from which systems.

I want it show all 5 systems and which are missing from each. Is that possible?

Example

System	Serial
System 1	123456
System 2	12345454
System 3	5445435
System 4	5645654
System 5	2354366734

"System 1" is supposed to be our single source of truth so I am trying to figure out which systems are missing from that, but are in others.

Thank you!

 

[HighAndWilder]

I have 5 different systems I manage and I am trying to figure out which serials are missing from which systems.

I want it show all 5 systems and which are missing from each. Is that possible?

Example

System	Serial
System 1	123456
System 2	12345454
System 3	5445435
System 4	5645654
System 5	2354366734

"System 1" is supposed to be our single source of truth so I am trying to figure out which systems are missing from that, but are in others.

Thank you!

Are you able to post some representative data using XL2BB?

How do you want the results presented?

 

[dmfweb]

Are you able to post some representative data using XL2BB?

How do you want the results presented?

Thank you for the reply @HighAndWilder , to simplify it because I think I made it confusing...

We have 3 systems
SCCM
CMDB
JAMF

I have a list of all serial numbers formatted like below:

System	Serial
JAMF	15117
SCCM	32236
CMDB	68665
JAMF	86671
SCCM	99787
CMDB	79880
JAMF	88313
SCCM	19970
CMDB	85064
JAMF	74584
SCCM	46174
CMDB	58209
JAMF	29523
SCCM	46580
CMDB	29746
JAMF	12018
SCCM	87421
CMDB	80380

What I need is to be able to say is:
X Serials are missing from CMDB
Y Serials are missing from JAMF
Z Serials are missing from SCCM

(if that is too hard really the main one would be CMDB )

 

[HighAndWilder]

Thank you for the reply @HighAndWilder , to simplify it because I think I made it confusing...

We have 3 systems
SCCM
CMDB
JAMF

I have a list of all serial numbers formatted like below:

System	Serial
JAMF	15117
SCCM	32236
CMDB	68665
JAMF	86671
SCCM	99787
CMDB	79880
JAMF	88313
SCCM	19970
CMDB	85064
JAMF	74584
SCCM	46174
CMDB	58209
JAMF	29523
SCCM	46580
CMDB	29746
JAMF	12018
SCCM	87421
CMDB	80380

What I need is to be able to say is:
X Serials are missing from CMDB
Y Serials are missing from JAMF
Z Serials are missing from SCCM

(if that is too hard really the main one would be CMDB )

The list of serial numbers is unique, none are duplicated so they are all missing unless I have misunderstood what you need.

 

[dmfweb]

The list of serial numbers is unique, none are duplicated so they are all missing unless I have misunderstood what you need.

In a perfect world, all three systems would have the exact same serial numbers. That’s what we’re trying to figure out. So if we see the serial numbers in two out of the three systems, we then know this a problem with the data being put into one of them.
So to answer the question They should technically all be in multiple lists, but that is the problem we are solving. We’re trying to figure out which systems are missing which serial numbers.