Comparing lists

O

Ollie67

New Member
Joined
Feb 23, 2005
Messages
2
Hi,

Hopefully someone can help me. I'll try to explain as clearly as possible:

I basically want to compare two columns of alphanumeric data, list A and list B. List A contains mostly the same items as list B, but contains some items that do not appear in list B, and may multiple instances of an item (list B only contains single instances). I want to return a list in a third column which contains items that appear in List A but NOT list B, with no duplicates (i.e. if an item had multiple instances in list A, I only want it to occur once in list C).

Any help would be greatly appreciated.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
If you start in C2 with the following formula:

=IF(COUNTIF(B:B,A2)=0,IF(COUNTIF(C$1:C1,A2)=0,A2,""),"")

and copy down as far as you need then for each row in column C you will either have the corresponding entry from column A if not also in column B and if not already shown higher up (i.e. no duplicates)

[If you don't want a third column then you could do th same sort of thing with conditional formatting, simply highlighting in column A the first instance of any entry in A which doesn't also appear in B]
 
Upvote 0
Book4
ABCDE
102
2ListAListBIdxListC
3FRADRA GDA
4GDAFRA1WDA
5XSAXSA  
6XSAZXA  
7ZXA  
8XSA 
9FRA 
10WDA2
11
Sheet1


Formulas...

C1 must house a 0.

C3, copied down:

=IF((A3<>"")*ISNA(MATCH(A3,$A$2:A2,0))*ISNA(MATCH(A3,$B$3:$B$6,0)),LOOKUP(9.99999999999999E+307,$C$1:C2)+1,"")

E1:

=LOOKUP(9.99999999999999E+307,C:C)

E3, copied down:

=IF(ROW()-ROW(E$3)+1<=$E$1,LOOKUP(ROW()-ROW(E$3)+1,C:C,A:A),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
build user list from spreadsheet
Replies
6
Views
156
lordriel
L
F
  • Solved
VBA to create list of unique values matching a condition, drawn from a large dataset
Replies
6
Views
301
dermie_72
D
N
  • Question
Worksheet Event adding and removing Data Validation based on multiple Conditions
Replies
0
Views
284
Nathan Asius
N
J
  • Question
Sum data in a range based on a column and row and an equation
Replies
1
Views
232
bobaol
B
B
  • Solved
Create distinct list with criteria
Replies
3
Views
173
Fluff
Fluff

Forum statistics

Threads
1,216,157
Messages
6,129,195
Members
449,493
Latest member
JablesFTW

Share this page

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