This particular guided is for the deliverable question you will get it some point in your career, That is the ultimate question of comparing two columns, a data and being asked for differences between them.
Compare File Question
The question you will be asked is something like can you tell me what names don’t appear in this HR data that do appear in this other list?
Human Logic Failure
The first problem is the logic flow, many times people will ask you the question the wrong way round and/or get their logic back to front, then when you give them the results you will get the "that is not what I was expecting"
The seconds is when human expectation do not match you output it will be questioned immediately and people will manually check it "to be sure" - however the failing here is computers do not lie and this script works, so here the human is wrong!
The third issue is one you have done that, the human will not like the data and then ask "can I get a list of people in both files to be sure"
Therefore I predict this with this script and give the end user both outputs in one, like one stone three birds.
Excel : Not my choice of weapon here
I also find it quite irritating that the conversation always starts with "I have this excel spreadsheet with two sheets" or "I have these two excel spreadsheets"
The thing that always confuses me here is Excel is not the most effective way to do this unless you know the string of formulas and expressions you need, if you do then it’s probably very simple but this is not 1984, we have better tools for this now.
Powershell is this correct approach
Powershell is the solution to this problem, it also doesn’t require an office license and it’s included in most modern versions of windows unless you do have a time machine and you travelled back to 1984.
Right so this is the script and once you have it you can use it again and again.
You need to define you A and B file - its called A and B for ease of scripting when you use it again, so in this example we have A file is bears.txt and B file which is honey-register.txt, and there is differences between these files and the humans will want to know what is different!
Script - CompareTextFiles-A2B
# Define the paths to the text files
$afile = "bears.txt"
$bfile = "honey.register.txt"
# Define the paths for the output comparison files
$aMinusBFile = "a-bcompare.txt"
$bMinusAFile = "b-acompare.txt"
$commonFile = "common.txt"
# Read the contents of the files into arrays
$aNames = Get-Content -Path $afile
$bNames = Get-Content -Path $bfile
# Find names that are in A but not in B
$namesInAOnly = $aNames | Where-Object { $_ -notin $bNames }
# Find names that are in B but not in A
$namesInBOnly = $bNames | Where-Object { $_ -notin $aNames }
# Find names that are in both A and B
$commonNames = $aNames | Where-Object { $_ -in $bNames }
# Save the results to the respective files
$namesInAOnly | Out-File -FilePath $aMinusBFile
$namesInBOnly | Out-File -FilePath $bMinusAFile
$commonNames | Out-File -FilePath $commonFile
# Optionally, output the counts to the console
Write-Output "Names in A but not in B (saved to $aMinusBFile): $($namesInAOnly.Count)"
Write-Output "Names in B but not in A (saved to $bMinusAFile): $($namesInBOnly.Count)"
Write-Output "Names in both A and B (saved to $commonFile): $($commonNames.Count)"
Note : If the job is that these two files should match then you have to give someone bad news on that topic, so prepare yourself for people not to trust the data when they do not like the results -aka human logic failure again,
