Q:

How to compare 2 massive/huge/long lists and output any non matching results in one field

I have 2 massive columns filled with studentids
Column 1(SID)  has 110,000 rows
Column 2(SID2) has 100,000 rows
How do I make a check of column 1 = column 2 or vice versa and then finally place it all in one field!
Current Results
SID SID2 
45  45
142 142
237 218
238 441  
410 410
440 442 
452 237
    452

Final Expected output
SID SID2  Check
45  45
142 142
237 218
238 441   238,441
410 410
440 442   440
452 237
    452
          238,441,440

As you can see, all the ids that dont match should be placed in one field so I can easily spot them rathen than looking though 100,000 rows

A:

Looks like SolarMike answered assuming that the test is A2=B2, but I think you're asking if A2=[Anything in Column B] and if B2=[Anything in Column A]. Here's how I'd test that:

Column C:
=IFERROR(IF(MATCH(A2,B:B,0)>1,"",A2),A2)

Column D:
=IFERROR(IF(MATCH(B2,A:A,0)>1,"",B2),B2)

Column E (very crude, but effective):
=IF(AND(C2="",D2=""),"",CONCATENATE(C2,",",D2,","))

Now, your real problem is getting it all into one single cell. The only way I know how to do this is to use Concatenate, but it requires you to select EACH CELL individually. 
=CONCATENATE(E2,E3,E4,E5,E6,E7,E8,E9,E10)

For 10000 rows, that doesn't seem feasible. Also, that output isn't very flexible, but I digress.
If you want to concatenate everything together in one cell, you have two options.
A)  Use the VBA code here (it's pretty simple, this seems like a viable option): Concatenate Excel Ranges with VBA
B) Hope that you have Office 365 with TEXTJOIN()  See Support article here.

