Find unique values

A

abdulla88

New Member
Joined
Jan 23, 2021
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
Hello everybody


I am facing a problem and I hope you can help،
I have two lists in two different columns and I want to have the unique values in a separate column. Is there any way?



meetingAttendanceList (2).csv
ABC
1List 1List 2Unique
2Andrew CenciniMichael Neipper
3Jan KotasNancy Freehafer
4Mariya SergienkoMariya Sergienko
5Michael NeipperMariya Sergienko
6Anne LarsenMichael Neipper
7Nancy FreehaferAnne Larsen
8Laura GiussaniNancy Freehafer
9Nancy FreehaferLaura Giussani
10Laura GiussaniLaura Giussani
11Laura GiussaniAnne Larsen
12Laura GiussaniNancy Freehafer
13Anne LarsenAnne Larsen
14Anne LarsenRobert Zare
15Nancy FreehaferNancy Freehafer
16Nancy FreehaferAnne Larsen
17Nancy FreehaferRobert Zare
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:B17Cell Valueunique valuestextNO
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
vba option

VBA Code: 
Sub do_it()
r = 2
For Each cel In Range("A1:B17")
If WorksheetFunction.CountIf(Range("A1:B17"), cel.Value) = 1 Then Cells(r, "C") = cel.Value: r = r + 1
Next cel
End Sub
 
Upvote 0
How about this

Excel Formula: 
=IFERROR(INDEX($A$2:$B$17,CEILING(AGGREGATE(15,6,((COLUMN($A$2:$B$17)-COLUMN($A$2))+((ROW($A$2:$B$17)-ROW($A$2))*COLUMNS($A$2:$B$17)+1))/((COUNTIF($A$2:$B$17,"<"&$A$2:$B$17)+($A$2:$B$17<>""))+(($A$2:$B$17<>"")*(--($A$2:$B$17<>"N/A"))*--(ISNUMBER($A$2:$B$17)=FALSE))*(SUMPRODUCT(--(ISNUMBER($A$2:$B$17)=TRUE)))-SUMPRODUCT(COUNTIF($A$2:$B$17,C$1:C1))=1),1),COLUMNS($A$2:$B$17))/COLUMNS($A$2:$B$17),MOD(AGGREGATE(15,6,((COLUMN($A$2:$B$17)-COLUMN($A$2))+((ROW($A$2:$B$17)-ROW($A$2))*COLUMNS($A$2:$B$17)+1))/((COUNTIF($A$2:$B$17,"<"&$A$2:$B$17)+($A$2:$B$17<>""))+(($A$2:$B$17<>"")*(--($A$2:$B$17<>"N/A"))*--(ISNUMBER($A$2:$B$17)=FALSE))*(SUMPRODUCT(--(ISNUMBER($A$2:$B$17)=TRUE)))-SUMPRODUCT(COUNTIF($A$2:$B$17,C$1:C1))=1),1)-1,COLUMNS($A$2:$B$17))+1),"")
Book1
ABC
1List 1List 2Unique
2Andrew CenciniMichael NeipperAndrew Cencini
3Jan KotasNancy FreehaferAnne Larsen
4Mariya SergienkoMariya SergienkoJan Kotas
5Michael NeipperMariya SergienkoLaura Giussani
6Anne LarsenMichael NeipperMariya Sergienko
7Nancy FreehaferAnne LarsenMichael Neipper
8Laura GiussaniNancy FreehaferNancy Freehafer
9Nancy FreehaferLaura GiussaniRobert Zare
10Laura GiussaniLaura Giussani 
11Laura GiussaniAnne Larsen 
12Laura GiussaniNancy Freehafer 
13Anne LarsenAnne Larsen 
14Anne LarsenRobert Zare 
15Nancy FreehaferNancy Freehafer 
16Nancy FreehaferAnne Larsen 
17Nancy FreehaferRobert Zare 
Sheet1
Cell Formulas
RangeFormula
C2:C17C2=IFERROR(INDEX($A$2:$B$200,CEILING(AGGREGATE(15,6,((COLUMN($A$2:$B$200)-COLUMN($A$2))+((ROW($A$2:$B$200)-ROW($A$2))*COLUMNS($A$2:$B$200)+1))/((COUNTIF($A$2:$B$200,"<"&$A$2:$B$200)+($A$2:$B$200<>""))+(($A$2:$B$200<>"")*(--($A$2:$B$200<>"N/A"))*--(ISNUMBER($A$2:$B$200)=FALSE))*(SUMPRODUCT(--(ISNUMBER($A$2:$B$200)=TRUE)))-SUMPRODUCT(COUNTIF($A$2:$B$200,C$1:C1))=1),1),COLUMNS($A$2:$B$200))/COLUMNS($A$2:$B$200),MOD(AGGREGATE(15,6,((COLUMN($A$2:$B$200)-COLUMN($A$2))+((ROW($A$2:$B$200)-ROW($A$2))*COLUMNS($A$2:$B$200)+1))/((COUNTIF($A$2:$B$200,"<"&$A$2:$B$200)+($A$2:$B$200<>""))+(($A$2:$B$200<>"")*(--($A$2:$B$200<>"N/A"))*--(ISNUMBER($A$2:$B$200)=FALSE))*(SUMPRODUCT(--(ISNUMBER($A$2:$B$200)=TRUE)))-SUMPRODUCT(COUNTIF($A$2:$B$200,C$1:C1))=1),1)-1,COLUMNS($A$2:$B$200))+1),"")
 
Upvote 0
Dossfm0q said:
How about this

Excel Formula: 
=IFERROR(INDEX($A$2:$B$17,CEILING(AGGREGATE(15,6,((COLUMN($A$2:$B$17)-COLUMN($A$2))+((ROW($A$2:$B$17)-ROW($A$2))*COLUMNS($A$2:$B$17)+1))/((COUNTIF($A$2:$B$17,"<"&$A$2:$B$17)+($A$2:$B$17<>""))+(($A$2:$B$17<>"")*(--($A$2:$B$17<>"N/A"))*--(ISNUMBER($A$2:$B$17)=FALSE))*(SUMPRODUCT(--(ISNUMBER($A$2:$B$17)=TRUE)))-SUMPRODUCT(COUNTIF($A$2:$B$17,C$1:C1))=1),1),COLUMNS($A$2:$B$17))/COLUMNS($A$2:$B$17),MOD(AGGREGATE(15,6,((COLUMN($A$2:$B$17)-COLUMN($A$2))+((ROW($A$2:$B$17)-ROW($A$2))*COLUMNS($A$2:$B$17)+1))/((COUNTIF($A$2:$B$17,"<"&$A$2:$B$17)+($A$2:$B$17<>""))+(($A$2:$B$17<>"")*(--($A$2:$B$17<>"N/A"))*--(ISNUMBER($A$2:$B$17)=FALSE))*(SUMPRODUCT(--(ISNUMBER($A$2:$B$17)=TRUE)))-SUMPRODUCT(COUNTIF($A$2:$B$17,C$1:C1))=1),1)-1,COLUMNS($A$2:$B$17))+1),"")
Book1
ABC
1List 1List 2Unique
2Andrew CenciniMichael NeipperAndrew Cencini
3Jan KotasNancy FreehaferAnne Larsen
4Mariya SergienkoMariya SergienkoJan Kotas
5Michael NeipperMariya SergienkoLaura Giussani
6Anne LarsenMichael NeipperMariya Sergienko
7Nancy FreehaferAnne LarsenMichael Neipper
8Laura GiussaniNancy FreehaferNancy Freehafer
9Nancy FreehaferLaura GiussaniRobert Zare
10Laura GiussaniLaura Giussani 
11Laura GiussaniAnne Larsen 
12Laura GiussaniNancy Freehafer 
13Anne LarsenAnne Larsen 
14Anne LarsenRobert Zare 
15Nancy FreehaferNancy Freehafer 
16Nancy FreehaferAnne Larsen 
17Nancy FreehaferRobert Zare 
Sheet1
Cell Formulas
RangeFormula
C2:C17C2=IFERROR(INDEX($A$2:$B$200,CEILING(AGGREGATE(15,6,((COLUMN($A$2:$B$200)-COLUMN($A$2))+((ROW($A$2:$B$200)-ROW($A$2))*COLUMNS($A$2:$B$200)+1))/((COUNTIF($A$2:$B$200,"<"&$A$2:$B$200)+($A$2:$B$200<>""))+(($A$2:$B$200<>"")*(--($A$2:$B$200<>"N/A"))*--(ISNUMBER($A$2:$B$200)=FALSE))*(SUMPRODUCT(--(ISNUMBER($A$2:$B$200)=TRUE)))-SUMPRODUCT(COUNTIF($A$2:$B$200,C$1:C1))=1),1),COLUMNS($A$2:$B$200))/COLUMNS($A$2:$B$200),MOD(AGGREGATE(15,6,((COLUMN($A$2:$B$200)-COLUMN($A$2))+((ROW($A$2:$B$200)-ROW($A$2))*COLUMNS($A$2:$B$200)+1))/((COUNTIF($A$2:$B$200,"<"&$A$2:$B$200)+($A$2:$B$200<>""))+(($A$2:$B$200<>"")*(--($A$2:$B$200<>"N/A"))*--(ISNUMBER($A$2:$B$200)=FALSE))*(SUMPRODUCT(--(ISNUMBER($A$2:$B$200)=TRUE)))-SUMPRODUCT(COUNTIF($A$2:$B$200,C$1:C1))=1),1)-1,COLUMNS($A$2:$B$200))+1),"")
Click to expand...

Thank you for your quick response, but my request was specifically to get the unique values from the two lists.
Sorry, if I was not clear at the beginning, you can see the attachment, for more clarification.

New Microsoft Excel Worksheet.xlsx
ABC
1TotalAttendAbsent
2Andrew CenciniAndrew Cencini
3Jan KotasJan Kotas
4Mariya SergienkoMariya Sergienko
5Michael NeipperMichael Neipper
6Anne LarsenAnne Larsen
7Nancy Freehafer
8Laura Giussani
ورقة1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:B8Cell Valueunique valuestextNO
 
Upvote 0
Sorry

Excel Formula: 
=IFERROR(INDEX($A$2:$B$17,CEILING(AGGREGATE(15,6,((COLUMN($A$2:$B$17)-COLUMN($A$2))+((ROW($A$2:$B$17)-ROW($A$2))*COLUMNS($A$2:$B$17)+1))/(COUNTIF($A$2:$B$17,A$2:$B$17)=1),ROWS($C$1:C1)),COLUMNS($A$2:$B$17))/COLUMNS($A$2:$B$17),MOD(AGGREGATE(15,6,((COLUMN($A$2:$B$17)-COLUMN($A$2))+((ROW($A$2:$B$17)-ROW($A$2))*COLUMNS($A$2:$B$17)+1))/(COUNTIF($A$2:$B$17,A$2:$B$17)=1),ROWS($C$1:C1))-1,COLUMNS($A$2:$B$17))+1),"")
sss.xlsx
ABC
1List 1List 2Unique
2Andrew CenciniMichael NeipperAndrew Cencini
3Jan KotasNancy FreehaferJan Kotas
4Mariya SergienkoMariya Sergienko 
5Michael NeipperMariya Sergienko 
6Anne LarsenMichael Neipper 
7Nancy FreehaferAnne Larsen 
8Laura GiussaniNancy Freehafer 
9Nancy FreehaferLaura Giussani 
10Laura GiussaniLaura Giussani 
11Laura GiussaniAnne Larsen 
12Laura GiussaniNancy Freehafer 
13Anne LarsenAnne Larsen 
14Anne LarsenRobert Zare 
15Nancy FreehaferNancy Freehafer 
16Nancy FreehaferAnne Larsen 
17Nancy FreehaferRobert Zare 
Sheet2
Cell Formulas
RangeFormula
C2:C17C2=IFERROR(INDEX($A$2:$B$200,CEILING(AGGREGATE(15,6,((COLUMN($A$2:$B$200)-COLUMN($A$2))+((ROW($A$2:$B$200)-ROW($A$2))*COLUMNS($A$2:$B$200)+1))/(COUNTIF($A$2:$B$200,A$2:$B$200)=1),ROWS($C$1:C1)),COLUMNS($A$2:$B$200))/COLUMNS($A$2:$B$200),MOD(AGGREGATE(15,6,((COLUMN($A$2:$B$200)-COLUMN($A$2))+((ROW($A$2:$B$200)-ROW($A$2))*COLUMNS($A$2:$B$200)+1))/(COUNTIF($A$2:$B$200,A$2:$B$200)=1),ROWS($C$1:C1))-1,COLUMNS($A$2:$B$200))+1),"")





Excel Formula: 
=IFERROR(INDEX($A$2:$B$8,CEILING(AGGREGATE(15,6,((COLUMN($A$2:$B$8)-COLUMN($A$2))+((ROW($A$2:$B$8)-ROW($A$2))*COLUMNS($A$2:$B$8)+1))/(COUNTIF($A$2:$B$8,A$2:$B$8)=1),ROWS($C$1:C1)),COLUMNS($A$2:$B$8))/COLUMNS($A$2:$B$8),MOD(AGGREGATE(15,6,((COLUMN($A$2:$B$8)-COLUMN($A$2))+((ROW($A$2:$B$8)-ROW($A$2))*COLUMNS($A$2:$B$8)+1))/(COUNTIF($A$2:$B$8,A$2:$B$8)=1),ROWS($C$1:C1))-1,COLUMNS($A$2:$B$8))+1),"")

sss.xlsx
ABC
1TotalAttendAbsent
2Andrew CenciniAndrew CenciniNancy Freehafer
3Jan KotasJan KotasLaura Giussani
4Mariya SergienkoMariya Sergienko 
5Michael NeipperMichael Neipper 
6Anne LarsenAnne Larsen 
7Nancy Freehafer 
8Laura Giussani 
Sheet2
Cell Formulas
RangeFormula
C2:C8C2=IFERROR(INDEX($A$2:$B$200,CEILING(AGGREGATE(15,6,((COLUMN($A$2:$B$200)-COLUMN($A$2))+((ROW($A$2:$B$200)-ROW($A$2))*COLUMNS($A$2:$B$200)+1))/(COUNTIF($A$2:$B$200,A$2:$B$200)=1),ROWS($C$1:C1)),COLUMNS($A$2:$B$200))/COLUMNS($A$2:$B$200),MOD(AGGREGATE(15,6,((COLUMN($A$2:$B$200)-COLUMN($A$2))+((ROW($A$2:$B$200)-ROW($A$2))*COLUMNS($A$2:$B$200)+1))/(COUNTIF($A$2:$B$200,A$2:$B$200)=1),ROWS($C$1:C1))-1,COLUMNS($A$2:$B$200))+1),"")
 
Upvote 0
A bit less compliacted
Book1 (version 1).xlsb
ABC
1List 1List 2Unique
2Andrew CenciniMichael NeipperAndrew Cencini
3Jan KotasNancy FreehaferJan Kotas
4Mariya SergienkoMariya Sergienkoabdulla88
5Michael NeipperMariya Sergienko 
6Anne LarsenMichael Neipper 
7Nancy FreehaferAnne Larsen 
8Laura GiussaniNancy Freehafer 
9Nancy FreehaferLaura Giussani 
10Laura GiussaniLaura Giussani 
11Laura Giussaniabdulla88 
12Laura GiussaniNancy Freehafer 
13Anne LarsenAnne Larsen 
14Anne LarsenRobert Zare 
15Nancy FreehaferNancy Freehafer 
16Nancy FreehaferAnne Larsen 
17Nancy FreehaferRobert Zare 
Sheet6
Cell Formulas
RangeFormula
C2:C17C2=IFERROR(IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$17)/(COUNTIF($A$2:$B$17,$A$2:$A$17)=1),ROWS(C$2:C2))),INDEX($B:$B,AGGREGATE(15,6,ROW($B$2:$B$17)/(COUNTIF($A$2:$B$17,$B$2:$B$17)=1),ROWS(C$2:C2)-SUMPRODUCT(--(COUNTIF($A$2:$B$17,$A$2:$A$17)=1))))),"")
 
Upvote 0
Solution
conditional format
sss.xlsx
ABC
1List 1List 2Unique
2Andrew CenciniMichael NeipperAndrew Cencini
3Jan KotasNancy FreehaferJan Kotas
4Mariya SergienkoMariya Sergienko 
5Michael NeipperMariya Sergienko 
6Anne LarsenMichael Neipper 
7Nancy FreehaferAnne Larsen 
8Laura GiussaniNancy Freehafer 
9Nancy FreehaferLaura Giussani 
10Laura GiussaniLaura Giussani 
11Laura GiussaniAnne Larsen 
12Laura GiussaniNancy Freehafer 
13Anne LarsenAnne Larsen 
14Anne LarsenRobert Zare 
15Nancy FreehaferNancy Freehafer 
16Nancy FreehaferAnne Larsen 
17Nancy FreehaferRobert Zare 
Sheet2
Cell Formulas
RangeFormula
C2:C3C2=IFERROR(INDEX($A$2:$B$17,CEILING(AGGREGATE(15,6,((COLUMN($A$2:$B$17)-COLUMN($A$2))+((ROW($A$2:$B$17)-ROW($A$2))*COLUMNS($A$2:$B$17)+1))/(COUNTIF($A$2:$B$17,A$2:$B$17)=1),ROWS($C$1:C1)),COLUMNS($A$2:$B$17))/COLUMNS($A$2:$B$17),MOD(AGGREGATE(15,6,((COLUMN($A$2:$B$17)-COLUMN($A$2))+((ROW($A$2:$B$17)-ROW($A$2))*COLUMNS($A$2:$B$17)+1))/(COUNTIF($A$2:$B$17,A$2:$B$17)=1),ROWS($C$1:C1))-1,COLUMNS($A$2:$B$17))+1),"")
C4:C17C4=IFERROR(INDEX($A$2:$B$200,CEILING(AGGREGATE(15,6,((COLUMN($A$2:$B$200)-COLUMN($A$2))+((ROW($A$2:$B$200)-ROW($A$2))*COLUMNS($A$2:$B$200)+1))/(COUNTIF($A$2:$B$200,A$2:$B$200)=1),ROWS($C$1:C3)),COLUMNS($A$2:$B$200))/COLUMNS($A$2:$B$200),MOD(AGGREGATE(15,6,((COLUMN($A$2:$B$200)-COLUMN($A$2))+((ROW($A$2:$B$200)-ROW($A$2))*COLUMNS($A$2:$B$200)+1))/(COUNTIF($A$2:$B$200,A$2:$B$200)=1),ROWS($C$1:C3))-1,COLUMNS($A$2:$B$200))+1),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:B17Expression=(COUNTIF($A$2:$B$200,B2)=1)textNO
A2:A17Expression=(COUNTIF($A$2:$B$200,A2)=1)textNO
 
Upvote 0
There is an option in conditional formatting to 'Format only unique or duplicate values' you don't need a mega long formula for that.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Hello what is the best formula to find missing invoices? someone told me this formula but it doesnt really work?
Replies
6
Views
103
kingjet
K
N
  • Locked
  • Question
Compare data in columns, remove duplicates and return unique values in a new column
2
Replies
12
Views
513
Peter_SSs
Peter_SSs
L
  • Solved
Sum values(column C) based on unique value(column A) after filtering(column B)
2
Replies
11
Views
409
Fluff
Fluff
Jovinchious
  • Question
Vlookup but with unique values
Replies
4
Views
128
etaf
etaf
N
  • Question
Copy Unique Values to New Sheet VBA
Replies
3
Views
123
nirvehex
N

Forum statistics

Threads
1,216,028
Messages
6,128,382
Members
449,445
Latest member
JJFabEngineering

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