I need to sort out the uniques values from column A and column B.
A B
asdf 123
asdf xyz
asdf2 xyz
This is how my columns look. I would like to keep only asdf2 since asdf is in duplicate. How do i do that?
Thank you.
I need to sort out the uniques values from column A and column B.
A B
asdf 123
asdf xyz
asdf2 xyz
This is how my columns look. I would like to keep only asdf2 since asdf is in duplicate. How do i do that?
Thank you.
Assuming that A2:B10 contains the data, try...
D2, confirmed with CONTROL+SHIFT+ENTER:
=SUM(IF(FREQUENCY(IF(A2:A10<>"",MATCH("~"&A2:A10,A2:A10&"",0)),ROW(A2:A10)-ROW(A2)+1)=1,1))
E2, confirmed with CONTROL+SHIFT+ENTER, and copied down and across:
=IF(ROWS(D$2:E2)<=$D$2,INDEX(A$2:A$10,MATCH(1,COUNTIF($A$2:$A$10,IF(COUNTIF($E$1:$E1,$A$2:$A$10)=0,$A$2:$A$10)),0)),"")
Hope this helps!
Further to your Private Message, the solution I offered should return the desired result. Have a look at the attached sample file.
Hope this helps!
P.S. Please keep all questions on the Board, thanks!
Sorry about that. I will post the PM sent to you here:
thank you for answering my topic. D2 returns me the number of "real unique" values and E2 returns me the first "real unique" value. but i need to filter 2 columns containing 2 rows. is it possible to use it as criteria range? or how can i make it that from a huge list to filter only the "real unique" values? every 2 columns contain 2 rows too.
i don't know if you understand me.
A B
asdf xyz
asdf xyz2
asdf2 qwe
asdf3 qwe2
asdf4 poi
asdf4 plk
asdf5 hhh
I would like to keep asdf2 with column B as well. So after the filter to have:
A B
asdf2 qwe
asdf3 qwe2
asdf5 hhh
Is it possible?
Your example is perfect, but i still don't know how to apply that formula to a full 40k rows sheet.
With the data covering 40k rows, the solution I offered will be very slow. Try Advanced Filter instead. Assuming A1:B40000 contains the data, including the column headers, try...
C1: leave empty
C2:
=COUNTIF($A$2:$A$40000,A2)=1
Data > Filter > Advanced Filter
List range: $A$1:$B$40000
Criteria range: $C$1:$C$2
Click Ok
Hope this helps!
How much time can it take with the first solution?
I tried the second solution. It returns a TRUE value in C2 and if i select the rows with the header it returns the same list value. If i select the rows without the header it returns the unique values from the list, but it also returns the double value once.
From the example given it returns this:
A B
asdf xyz2
asdf2 qwe
asdf3 qwe2
I think i will do them manually. Thank you tho.
Have a look at the first sheet in the attached sample file. Adjust the range to include the the actual range and you'll see that the calculation time will be very slow. Also, you'll notice I made a correction to the second formula. I replaced...Originally Posted by testaredescript
ROWS(D$2:E2)
with
ROWS(E$2:E2)
Have a look at the second sheet of the attached sample file. You'll see that Advanced Filter returns the desired result.I tried the second solution. It returns a TRUE value in C2 and if i select the rows with the header it returns the same list value. If i select the rows without the header it returns the unique values from the list, but it also returns the double value once.
From the example given it returns this:
A B
asdf xyz2
asdf2 qwe
asdf3 qwe2
I think i will do them manually. Thank you tho.
Hope this helps!
Great! Got it now. Thank you very much. I didn't thought this is possible
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks