In this excel file I show what I have and what I need. In short I have list of unique distinct values from a column, that is ok . But I need list that will show unique distinct values from a column but only values that contain data in column data. Please help
what I have and what I need.xlsx
Maybe the Pivot Table in the attached will suffice. It has the huge advantage of not needing any coding or formulae
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Thank you my friend. But is there a way not to use a pivot table ? I just don't like it
In D8
=INDEX($A$8:$A$14,MATCH(0,INDEX(COUNTIF($D$7:D7,$A$8:$A$14)+($B$8:$B$14=""),0,0),0))
Copy down
Last edited by Ace_XL; 01-26-2015 at 04:28 PM.
Life's a spreadsheet, Excel!
Say thanks, Click *
Put this ARRAY formula in K8, then copy down and across...
=IFERROR(INDEX(H$8:H$14,SMALL(IF($I$8:$I$14<>"",ROW($I$8:$I$14)-7),ROWS($H$8:H8))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
FDibbins and Ace_XL
Sorry my friends, for some reason both of codes it's not working
I get that they may not be working for you, but we would hardly take the time to create a formula like that, without 1st testing it before we suggested it for your problem. Saying it doesnt work, doesnt help much - what did it do...wrong value...no value...error...?Originally Posted by pic2pic
Did you use CTRL SHFT ENTER to enter ir, or just enter? You need to use CSE
Thank you all. I had some idea, the fallowing code :
=IF(B8<>"";A8;"")
After that I can use mentioned code in another column to find unique distinct values . What I don't like is that this will make empty cells in between filled cells . Is there a way to remove these cells ?
It's in a file
what I have and what I need - version 2.xlsx
Enter this in K8 and fill down:
Formula:
=IFERROR(INDEX($H$8:$H$14,MATCH(1,INDEX((COUNTIF($K$7:K7,$H$8:$H$14)=0)*($H$8:$H$14<>"")*($I$8:$I$14<>""),0),0)),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Enter this in L8 and fill down:
Formula:=IFERROR(INDEX($I$8:$I$14,MATCH(K8,$H$8:$H$14,0)),"")
<---------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
The formulae that I gave you in message # 7 will not leave blanks between values and will only return values that have a value in the next column.
Here is the formula with the correct cell references for your current workbook. This doesn't require Array Entry as previously stated. Enter normally.
Formula:=IFERROR(INDEX($A$8:$A$14,MATCH(1,INDEX((COUNTIF($D$7:D7,$A$8:$A$14)=0)*($A$8:$A$14<>"")*($B$8:$B$14<>""),0),0)),"")
If you don't want to use the criteria in column B then the formula would be as follows to return the unique values from column A.
Formula:=IFERROR(INDEX($A$8:$A$14,MATCH(1,INDEX((COUNTIF($D$7:D7,$A$8:$A$14)=0)*($A$8:$A$14<>""),0),0)),"")
wooow.Thank you sir
How do I mark this as "SOLVED" ?
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Hi guys ! This works very good, but is it possible to have the same thing but in alphabetical order ?
Thank you for the feedback.
To start to figure out the formula, start in the middle with the COUNTIF. With the formula in the formula bar, click on COUNTIF then click on fx on the left of the formula bar. The COUNTIF function will be displayed with all of the elements filled in. Click on each element and an explanation of what it is, is given. Do this for each of the functions working your way outwards and you should have a good idea of what is going on.
If you select all the data, the extracted values can be sorted in order.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
pic2pic 
Register To Reply
Bookmarks