Hi
I'm getting a bit mixed up with my conditions whilst indexing.
I have enclosed the sample data. Basically my indexing function is working on sheet2 to pull the date from a list of orders into a client matrix. I was hoping to add a condition of "=Y" from the column D (Repeat Order) on the Orders sheet.
I have tried various combinations and am now loseing my way (and my hair). Can anyone help?
Try this array formula with added "Y" condition
=IFERROR(INDEX(Orders!$A:$A,SMALL(IF(Orders!$B$2:$B$23=$A2,IF(Orders!$D$2:$D$23="Y",ROW(Orders!$B$2:$B$23))),COLUMNS($B2:B2))),"")
...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.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Interesting. This is similar to my inital thoughts however I was finding the value did'nt change when a "N" was added.
The purpose of formula if to eradicate those clients who just had repeat orders, thereby leaving a list of only new clients who then go on to repeat order.
Did the formula work?
Gave it another go on a different dataset and it worked great. When I originally did this I used ,"=Y" (do you know why this wouldn't work?
Thanks for the help anyway.
Thank you for the feedback
BTW, the values do change if you change "Y" to "N". Also you may want to set a cell where you can switch between Y/N and have a formula set to this cell. In addition, to get unique Company Names you can use this formula in A2
No need for helper column and this formula does not require Ctrl+Shift+Enter confirmation.
=IFERROR(INDEX(Orders!$B$2:$B$23,MATCH(0,INDEX(COUNTIF($A$1:A1,Orders!$B$2:$B$23),,),)),"")
Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).
Thanks AlKey.
Nice tip for removing the column. No point in going round the houses when a motorway (thats what we call a highway) runs through the middle of it
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks