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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
  • Solved
Count number of max values per unique value and column heading?
Replies
5
Views
285
RobP
R
P
  • Solved
Removing duplicates with a formula. Is the Unique formula an option?
Replies
3
Views
171
RoryA
RoryA
A
  • Solved
Count unique IDs with a wildcard criteria for another column
Replies
2
Views
164
ausswe
A
D
  • Solved
Using Vlookup, If statements??
Replies
3
Views
222
Dprobasco
D
E
  • Solved
Return Unique Values based on certain cell & column
Replies
5
Views
283
Fluff
Fluff

Forum statistics

Threads
1,223,949
Messages
6,175,581
Members
452,653
Latest member
craigje92

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