Ello Excel Gurus
I'm back, since I Know you guys I lerarned many thinks, but now I'm stuck with unique list
I Know How to creat a unique list of items from one list,bt now I've two list
I prefer the solution with the FREQUENCY Function
Thank You do much
List1 List2 Result Anna Nik Anna Mark Mike Mark Luis Anna Luis Joe Luis Joe Lucy Nik Mike Lucy
Ervin
Please have a look at this.....
Kind regards,
Piet Bom
PietBom thank you for the answer
This was just a small sample, I have 100 of rows and as you said doesnt work, i know is possible with FREQUENCY function but i stuck
Thank you
The FREQUENCY method won't work on a 2d range. Specifically, you have to use the MATCH function within the FREQUENCY function and the MATCH function won't accept 2d ranges as the lookup array argument.Originally Posted by XLalbania
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.
@Tony,
Sorry for my behavior.
I was not aware of that.
I will give more substantial detail in the future.
@XLalbania,
I do have a formula for listing unique items from a single data list, but not for 2 data lists.
Sorry for this.
Maybe another guru can help you.
@Tony,
Sorry for my behavior.
I was not aware of that.
I will give more substantial detail in the future.
@XLalbania,
I do have a formula for listing unique items from a single data list, but not for 2 data lists.
Sorry for this.
Maybe another guru can help you.
This should work for stacked columns (column A first with Column B under it)
Enter in C2 and fill down until blanks appear.
Formula:
Please Login or Register to view this content.
This will work for "shuffled" columns (alternating Column A value then Column B value)
Enter in C2 and fill down as above
Formula:Please Login or Register to view this content.
This is the result using your example
Data Range
Last edited by newdoverman; 01-15-2017 at 02:27 PM.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Thank you everybody for your answer
Probably I didn't expalint me very well
I need a Unique list with data coming from two different tables (list1 and list2
in this table you can see the answer from Newdovermann but the right answer is in th elast column
Thank you fro your help
List 1 List 2 Newdovermann Newdovermann expected result Anna Nik Anna Anna Anna Mark Mike Mark Nik Mark Luis Anna Luis Mark Luis Joe Luis Joe Mike Joe Lucy Nik Luis Nik Mike Anna Mike Anna Joe Lucy Luis Luis Lucy Lucy
So we keep open thi threads because i'd like a olution
Very kind
If you inserted another new column D, so that the results are shown in column E, then you could use this formula in C2:
=IF(COUNTIF(A$2:A2,A2)=1,MAX(C$1:C1)+1,"-")
and this one in D2:
=IF(AND(COUNTIF(B$2:B2,B2)=1,COUNTIF(A:A,B2)=0),MAX(D$1:D1)+1,"-")
with this one in D1:
=MAX(C:C)
You can copy the formulae in C2:D2 down as far as you like to accommodate new data being added, and these set up a unique sequential number for each name in columns A and B that is unique.
Then you can use this formula in E2:
=IF(ROWS($1:1)>MAX(D:D),"",IF(ROWS($1:1)<=MAX(C:C),INDEX(A:A,MATCH(ROWS($1:1),C:C,0)),INDEX(B:B,MATCH(ROWS($1:1),D:D,0))))
and copy this down. The approach is shown in the attached file.
Hope this helps.
Pete
I forgot the "Unique" bit.
In your vacant column C enter in C2 and fill down
Formula:Please Login or Register to view this content.
In D2 enter this and fill down
Formula:Please Login or Register to view this content.
Result:
Data Range
The values in column C can be hidden by formatting the text as WHITE.
Last edited by newdoverman; 01-15-2017 at 02:47 PM.
Try this...
Data Range
This array formula** entered in D2:
=SUM(IF(A2:B10<>"",1/COUNTIF(A2:B10,A2:B10)))
This array formula** entered in E2:
=IF(ROWS(E$2:E2)>D$2,"",INDEX(A$2:B$10,MIN(IF(A$2:B$10<>"",IF(ISNA(MATCH(A$2:B$10,E$1:E1,0)),ROW(A$2:B$10)-ROW(A$2)+1))),MOD(MIN(IF(A$2:B$10<>"",IF(ISNA(MATCH(A$2:B$10,E$1:E1,0)),(ROW(A$2:B$10)-ROW(A$2)+1)*100000+(COLUMN(A$2:B$10)-COLUMN(A$2)+1)))),100000)))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Copy down until you get blanks.
I have a shorter formula that will do this but it uses volatile functions and will probably be slow to calculate on large amounts of data.
Wooow You rocks newdoverman
Last question, since I'm studing the FREQUENCY function approcc formula, I know How to do it with one list but with to list is impossible
I think I have to bild a vertical array like (this in red) an than everythig else is easy
=INDEX{"Anna"."Mark"."Luis"."Joe"."Nik"."Mike"."Anna"."Luis"."Lucy"};SMALL(IF(FREQUENCY(MATCH..............
I relly appreaciate your help
Last edited by XLalbania; 01-15-2017 at 03:20 PM.
Excellt Tony
Your formula works very well, but pease read post#13
Thank
you
Excellt Tony
Your formula works very well, but please read post#13
Thank
you
Here's how to use the FREQUENCY function on a single list...
Data Range
This array formula** entered in C2:
=IFERROR(INDEX(A:A,SMALL(IF(FREQUENCY(MATCH(A$2:A$10,A$2:A$10,0),ROW(A$2:A$10)-ROW(A$2)+1),ROW(A$2:A$10)),ROWS($C$1:C1))),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Copy down until you get blanks.
The reason the FREQUENCY function won't work on a 2d range is the highlighted section in the MATCH function won't accept 2d ranges.
Did you check out my approach in Post #10 ?
Pete
This array formula (Enter with Ctrl + Shift + Enter) does not require helper columns.
Array enter in D2 and fill down
Formula:Please Login or Register to view this content.
Thank You Pete
I didn't see you work
That works fine but still is not what a needet
see post#13 my request
Thank you any way
This array formula (Enter with Ctrl + Shift + Enter) does not require helper columns.
Array enter in D2 and fill down
Formula:Please Login or Register to view this content.
Yess!!
This work perfectly
even if not with FREQUENCY function
I will stay with this approch
Thank you every body for your help
Bye
Ervin
Here's another one that works on 2 lists.
Data Range
This array formula** entered in D2:
=IFERROR(INDEX(A$2:A$10,MATCH(1,(A$2:A$10<>"")+COUNTIF(D$1:D1,A$2:A$10),0)),IFERROR(INDEX(B$2:B$10,MATCH(1,(B$2:B$10<>"")+COUNTIF(D$1:D1,B$2:B$10),0)),""))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Thank you Tony
Good formula too!
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks