Hi All
Hope that you can help me with the following!
I have found a formula which will give me each unique entry from a column which ignores any duplicate entries.
this works great on the entire column, but can this be amended to only return the results based on certain criteria? If it can how can this be achieved?
Please Login or Register to view this content.
I have attached a sample of a workbook to give an idea of my setup and what I would like to be able to achieve.
Thanks
C J W
Rather than telling us what doesn't work, can you explain what you do want? I have made a guess. It will almost certainly be wrong, but it shows you haven't been forgotten about!! However, to really help - for a set of conditions - please provide your expected answer.
I also took the liberty of killing the merged cells. They are the spawn of Satan and will get you in the end. You don't need them. Just widen the columns. Avoid using them in your data, today, tomorrow and forever.
Last edited by Glenn Kennedy; 05-28-2017 at 10:22 AM. Reason: Correcting formula as pointed out by JT, below
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
@GK: H & I columns wrong way round ..
=IFERROR(INDEX($J:$J,SMALL(IF($G$3:$G$26=$C$3,IF($H$3:$H$26=$C$4,IF($I$3:$I$26=$C$5,ROW($G$3:$G$26)))),ROWS(B$10:B10))),"")
They probably got messed up when I was removing the merged cells... The answer won't be correct, as the desired result wasn't clearly specified!!
ARRAY formula in X2, then drag down
ARRAY formula is usedPlease Login or Register to view this content.
To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets by excel.
Thanks for the reply and looking into my question - I will try and be clearer! Want I would like to be able to do is display each unique model number depending upon what options have been selected.
If only the make has been selected I would to be able to show all version numbers associated with that make(but ignore any duplicate version numbers), if make and model are selected I would like to be able to display only the version numbers which match the make and model, and finally if all three options are selected then only display that version.
Hope that makes more sense than my original post!
Completely clear... now. I'm away for a couple of hours, maybe for the night. If no-one else has sorted this out; I'll do so later. I just did not understand why the results column was also a criterion column.
Have you checked my previous post.
b10=IFERROR(INDEX($O$3:$O$26, MATCH(0, COUNTIF($B$9:B9, $O$3:$O$26&"")+COUNTIF(E$3,"<>"&M$3:M$26)+COUNTIF(E$4,"<>"&N$3:N$26), 0)),"")
Try this and copy towards down
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
thanks for the reply kvsrinivasamurthy the formula doesn't work for what I would like
Thanks nflsales that works great when I select both make and model, but not if only one option is selected I don't have any results.
appreciate all of you looking into this one!
I ALMOST have it working. I've done this before, but my memory and my filing system are letting me down... Still working on it...
sorry,Originally Posted by C J W
try below arrow formula and copy towards down
b10=IFERROR(INDEX($O$3:$O$26, MATCH(0, COUNTIF($B$9:B9, $O$3:$O$26&"")+IF(E$3="",0,COUNTIF(E$3,"<>"&M$3:M$26))+IF(E$4="",0,COUNTIF(E$4,"<>"&N$3:N$26)), 0)),"")
Pl upload file showing what is the problem.
nflsales, Siva, I think that 95% there. All that was needed was to add another term to cover C5, in accordance with the OPs spec. the final array formula should be (note that the cell refs are slightly different, as I have removed the horrible merged cells in the attached sheet.
Formula:
Please Login or Register to view this content.
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.
You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
That was NOT the way i had been trying to get it to work. For my own satisfaction, I shall persist.
Thanks All, both solutions give me what I needed, especially Glenn Kennedys which factors in all three options.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks