Hi everyone,
I've attached an example spreadsheet,
it contains IFERROR(MATCH & IFERROR(SMALL formula's,
the problem is if there are 2 denominations the same,
it will list them as the first respective number associated.
I've colour coded the 2 errors for ease of finding the problem,
Any help on this would be much appreciated, it's already done my head in lol
Best regards,
Please Login or Register to view this content.
Chad
Hi Chad
see attached my proposed solution
your first number is using the formula you are using
the rest is using a CSE formula shown below
CSE is 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.
Formula in E2
Formula:
Please Login or Register to view this content.
i used a named range to make it easier to update but you can leave that out if you want
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or without formula array, but needing an empty line before the table
Formula:Please Login or Register to view this content.
see the file Query(1).xlsx
Thanks for the reply buddy, especially from a fellow Aussie
I've attached an additional sample to further explain, Hope it helps explain better,
As I fear I may have dropped the ball on correctly pointing out the prob.
Thanks for your reply also Jose, please see attached for further explanation.
Regards,Please Login or Register to view this content.
Chad
Just looking @ yours Jose, looks like you could be on to something there mate,
Let me put it in the sheet & see if it works....
sorry i misunderstood what your after
Jose seems on the money
just you don't need to leave blank row
use a normal Iferror/match formula as first match will always be first match
I think it will work but I am having trouble putting it all together,
or putting the pieces of the puzzle together so to speak.
I have attached the file, areas that need to have these formulas are colour coded purple!
Thanks,Please Login or Register to view this content.
Chad
in AO8
Formula:Please Login or Register to view this content.
AO15
Formula:Please Login or Register to view this content.
Thank you to all that responded!
Living up to your name mate, Humdinga got me across the line,
Cannot thank you enough mate, Will leave a Rep shortly!
Have a great 2016
Regards,
Chad
In AP15:
=IFERROR(LARGE(WinOdds+ROW(WinOdds)*10^-10,COUNT(RaterIIIII!WinOdds)-ROW(A1)+1),0)
AO15:
=IFERROR(MATCH(AP15,WinOdds++ROW(WinOdds)*10^-10,0),0)
Both 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.
Drag down.
Quang PT
not a problem
Jose did all the heavy lifting
i just massaged it through
so full credit to him on this one
Cheers you too mate
Hum
Hi guys,
I know I have marked this as solved, BUT lol
have since moved it to another sheet & the same thing has cropped up,
Double up errors occur in:
Z7-Z9
Z16-Z19
AD7-AD9
AD16-AD19
Would you mind taking a look for me please,
I have attached the altered file again,
everything else works perfectly.
With two prices listed in the same column it recognises them as the same.
Thanks,Please Login or Register to view this content.
Cahad
Hi Cahad
See the file TEST (1).xlsm with same adjustments.
Regards
Thank you my friend, again you solve the puzzle,
May 2016 be your best year yet buddy!
I'm happy for you. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks