Unique entries in two lists of user IDs

Tarver

Board Regular
Joined
Nov 15, 2012
Messages
109
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I've got two different list of User IDs that come from two different sources. Some of the User IDs are included in both lists, but many are in one list only. The entries in each list are almost always duplicated. There will eventually be changes to the lists, as employees are hired or leave their roles, so I need a formulaic way of combining the lists, sorting them, and eliminating duplicates.

Here are a sample of my two input tables and the final version I'm trying to create:
Book1
ABCDE
1User ID List 1 - With DuplicatesUser ID List 2 - With DuplicatesDesired Output - Complete User List
2AHOOKSAGONZALEZAGONZALEZ
3AHUDSPETHBAMANTEAHOOKS
4ALEEBANDERSONAHUDSPETH
5BAMANTEBDORSEYALEE
6ALEEBMINOTTBAMANTE
7AHOOKSAGONZALEZBANDERSON
8BAMANTEBDORSEYBDORSEY
9ALEEAGONZALEZBMINOTT
10BAMANTEBMINOTT
Sheet1


I've tried to use the UNIQUE function to identify unique entries and the SORT function to put them in order, but I can't seem to find a way to combine the functions to have them work from both lists.

Is there a way to accomplish what I'm trying to do with formulas?

Thanks!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
How about
Fluff.xlsm
ABCDE
1User ID List 1 - With DuplicatesUser ID List 2 - With DuplicatesDesired Output - Complete User List
2AHOOKSAGONZALEZAGONZALEZ
3AHUDSPETHBAMANTEAHOOKS
4ALEEBANDERSONAHUDSPETH
5BAMANTEBDORSEYALEE
6ALEEBMINOTTBAMANTE
7AHOOKSAGONZALEZBANDERSON
8BAMANTEBDORSEYBDORSEY
9ALEEAGONZALEZBMINOTT
10BAMANTEBMINOTT
Data
Cell Formulas
RangeFormula
E2:E9E2=LET(a,A2:A10,b,C2:C10,ra,ROWS(a),s,SEQUENCE(ra+ROWS(b)),SORT(UNIQUE(IF(s<=ra,a,INDEX(b,s-ra)))))
Dynamic array formulas.
 
Upvote 0
Solution
Nicely done, Fluff. An excellent solution! Works like a charm. Thank you!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
Hi Fluff any chance yo could do a walkthrough explanation on the formula
 
Upvote 0
=LET(a,A2:A10,b,C2:C10,ra,ROWS(a),s,SEQUENCE(ra+ROWS(b)),SORT(UNIQUE(IF(s<=ra,a,INDEX(b,s-ra)))))[/XD][/XR][/RANGE]

Upon review, I find that the last entry in the first column (A2:A10) is dropped from the combined list. I updated the formula to make the ROWS(a) function into ROWS(a)+1. This works.
 
Upvote 0
That will only happen if it is a duplicate value.

That will return a #N/A error.

I already know never to doubt you, Fluff. However in this case, I was missing the final entry in the first list and I'm not getting the #N/A error after the modification. Regardless, it's working for me perfectly, and I'm very grateful for your help.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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