LinkBack URL
About LinkBacks
Hi,
I need to compare 2 lists.
Then I need to match off identical items.
Then I need to leave any which don't have a 1 to 1 match.
Can anyone help?
Hi,
I need to compare 2 lists.
Then I need to match off identical items.
Then I need to leave any which don't have a 1 to 1 match.
Can anyone help?
Unmatched items in column A
In B4 copy down
=IF(COUNTIF($A$4:A4,A4)=1,IF(COUNTIF($C$4:$C$6,A4)=0,A4,""),"")[/FORMULA]Formula:
Please Login or Register to view this content.
Unmatched items in column C
In D4 copy down
Formula:Please Login or Register to view this content.
Unique values
- copy your 2 lists one below the other in the same column
- Data tab \ Remove duplicates
RemoveDuplicates.jpg
Last edited by kev_; 12-09-2017 at 12:19 PM.
Click *Add Reputation to thank those who helped you. Ask if anything is not clear
Hi,Originally Posted by kev_
Unmatched items in column A
In B4 copy down
=IF(COUNTIF($A$4:A4,A4)=1,IF(COUNTIF($C$4:$C$6,A4)=0,A4,""),"")[/FORMULA]Formula:Please Login or Register to view this content.
Unmatched items in column C
In D4 copy down
Formula:Please Login or Register to view this content.
Unique values
- copy your 2 lists one below the other in the same column
- Data tab \ Remove duplicates
Attachment 551304
The answer that you have provided is not the one that I was looking for.
If you look at what IU want my end result to be, it is:
List 1 List 2
Apples Pears
Grapes
The answer you have provided is:
List 1 List 2
Grapes Pears
We are missing 1 of the apples in List 1.
Is there any way to fix this?
Try this
For list one use in F4:F7 (F3 must be empty)
Formula:Please Login or Register to view this content.
For list two use in G4:G7 (G3 must be empty)
Formula:Please Login or Register to view this content.
See the file for clarification
@eazy899The answer that you have provided is not the one that I was looking for.
You saw 2 lists and you made an assumption - which is dangerous with Excel
You did not read what I wrote.
The workbook gives you only the unmatched items
- there are formulas in column B & D as specified my post
- so the 2 columns are both part of the same list
For the unique items follow the instructions
- this is a DIY job
Last edited by kev_; 12-09-2017 at 02:10 PM.
I would use Conditional formatting to highlight the matched items.
Then Sort the entire table on color using AutoFilter.
Then select the highlighted rows and deleten the rows.
Then sort the table again to loose the empty rows.
Last edited by PietBom; 12-10-2017 at 05:13 PM.
Kind regards,
Piet Bom
Another try.
Array enter this in A12 and fill down until you get blanks.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Then array enter this in C12 and fill down until you get blanks.Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
Dave
Re: Compare 2 lists and leave unmatched items
You can use PowerQuery (Get&Transform) (no formula, no vba)
eazy899 you've marked this as Solved.
To which solution do you refer?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks