See attached.
On 'sheet1' i have a list of names. Some names are listed more than once
On 'sheet2' is the result i am trying to get. That is to list the names, but only display them once.
Any help is always appreciated
See attached.
On 'sheet1' i have a list of names. Some names are listed more than once
On 'sheet2' is the result i am trying to get. That is to list the names, but only display them once.
Any help is always appreciated
1. Highlight your range of names
2. Data-->Sort & Filter-->Advanced
3. See the picture below.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Sorry, i should have specified this is in google sheets
No worries. I don't work in that area. Hopefully someone will come along. I have moved this thread to that sub-forum.
Hi all, still require some help with this please.
I'm not familiar with Google Sheets, but I assume that the "common" functions in Excel are also available in GS, so you can put this formula in B2 of Sheet1:
=IF(COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,"-")
Copy down to the bottom of your list (or beyond, if you want to add new data in column A), and then you can use this formula in cell A2 of Sheet2:
=IFERROR(INDEX(Sheet1!A:A,MATCH(ROWS($1:1),Sheet1!B:B,0)),"")
Copy down until you start to get blanks.
Hope this helps.
Pete
That seems to do the trick. Many thanks
It's actually much easier in GS than it would be in Excel (pending the new functionality). Just use:
=UNIQUE(Sheet1!A2:A)
in one cell and GS will produce the full unique list for you.
Rory
I now have an updated issue. I now have a list of names in two separate columns, but want to be listed on a different sheet showing the name only once.
See attached
I can't use macro coding.
I have just noticed that this is a triple post. Do this again and you can expect to be banned from this site.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Sorry Glenn. I was getting confused as to where i should be posting this. I do apologies.
In response to your comment :
One way:
=IFERROR(IFERROR(INDEX(Sheet1!$A$2:$A$8,MATCH(0,INDEX(COUNTIF($A$1:A1,Sheet1!$A$2:$A$8),0,0),0)),INDEX(Sheet1!$E$2:$E$6,MATCH(0,INDEX(COUNTIF($A$1:A1,Sheet1!$E$2:$E$6),0,0),0))),"")
If thenumber of names in each column can be variable, I'd be tempted to create two named ranges to adjust the lengths of the ranges containing the names automatically, as this formula does not like blanks.
I don't quite seem to be able to get it working for what i have. When i drag down the names seem to be doubling up and a "0" appears in the middle of it. Maybe the name ranges might work??
Ok.
Im away for a couple of hours. Two Qs. Have you used exact ranges on your real sheet ( no blank rows at the bottom)? Are there blank cells in the middle of either list?
I have attached a newer version showing more of what i am looking for. I need to extract names from "1st_XI" column B and column J into "1st_XI_Statistics" in column A.
Create 2 Named Ranges List1 and List2. CTRL-F3 to view edit. As an example, here's List1:
='1st_XI'!$B$19:INDEX('1st_XI'!$B$19:$B$33,COUNTA('1st_XI'!$B$19:$B$33))
then in A2, copied down:
=IFERROR(IFERROR(INDEX(List1,MATCH(0,INDEX(COUNTIF($A$1:A1,List1),0,0),0)),INDEX(List2,MATCH(0,INDEX(COUNTIF($A$1:A1,List2),0,0),0))),"")
If this is still Google Sheets:
=unique({A2:A8;E2:E6})
As the O.P. version states 2010.
and I spent time exercising my grey matter I may as well post my donation.
torachan.
Thanks Glenn. Much appreciated. I still have a couple of issues.
1) If you scroll down in "1st_XI" sheet you will see there are more names that I need to add
2) I've noticed the name range formula does not work for google sheets. I assume this is going to cause issues.
Rorya,
This actually works well. I can break it down to each range I am looking to extract.
Many thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks