Spreadsheet help: compare two lists to find exceptions

  • Thread starter DaveC426913
  • Start date
  • Tags
    Compare
In summary: List 1:CarolAliceBobDaveList 2:BillBobDaveIn summary, the MATCH function does not work properly when trying to compare two lists of text that have numbers in them.
  • #36
jack action said:
Maybe you can just write the full column range by default?
Yeah. I just set it to A$1:A$500, which will cover the largest list I have.

But this really gets into the weeds of how I'm going to move forward with this. If this whole process has to survive me (which I verified is the desire, I'm going to have to use the client's preferred software, which is Excel.

Still, no idea where it's going to live or who will be the keeper of the lore. There's no "owner" and no central repository. But that's not an issue that will be solved here...
 
Computer science news on Phys.org
  • #37
DaveC426913 said:
But this really gets into the weeds of how I'm going to move forward with this. If this whole process has to survive me (which I verified is the desire, I'm going to have to use the client's preferred software, which is Excel.
Oh I have misunderstood, I didn't realise you were being paid for this - my bill is in the post.
 
  • Haha
Likes DaveC426913
  • #38
pbuk said:
Oh I have misunderstood, I didn't realise you were being paid for this - my bill is in the post.
It is technically for my (salaried) job, but I don't get expenses. I'm just expected to produce this report.
 
  • #39
OK, I've switched to Excel. I've got a primitive solution but since it uses the MATCH function, it spits out matches. Any matched records display as #N/A.

What I'm after is the opposite. I need to display non matches.

I've looked at VLOOKUP and HLOOKUP but that's not what I'm trying to do - at least they don't seem to work across two sheets.)

So:

Sheet "RCP":
7072Alice
4154Andy
etc.
Sheet "PSS":
7033Bill
7058Dave
7072Alice
7103Charlie
7113Annie
etc.
I hope to get:

RCP NO matches in PSS:
4154 Andy

PSS NO matches in RCP:
7033 Bill
7058 Dave
7103 Charlie
7113 Annie
 
  • #40
With 16K ids on the list, upload each list as a separate table into MS Access and do an outer join
 
  • #41
BWV said:
With 16K ids on the list, upload each list as a separate table into MS Access and do an outer join
I found an even better solution:
conscript a gaggle of interns to do it with pencil and paper.
 
  • Haha
Likes Tom.G
  • #42
DaveC426913 said:
I found an even better solution:
conscript a gaggle of interns to do it with pencil and paper.
Well you could use the query to check the work, than fire the lot of them for wasting time doing it manually and not knowing how to do a simple query
 
  • Haha
Likes DaveC426913

Similar threads

  • Special and General Relativity
Replies
6
Views
1K
  • Mechanical Engineering
Replies
7
Views
1K
  • Sci-Fi Writing and World Building
Replies
1
Views
522
Replies
38
Views
3K
  • Computing and Technology
Replies
12
Views
5K
  • Sticky
  • Aerospace Engineering
2
Replies
48
Views
60K
Replies
2
Views
3K
Replies
5
Views
2K
  • Sticky
  • Engineering and Comp Sci Homework Help
Replies
1
Views
19K
Back
Top