Hi all,
i am trying to generate a list that did not not have a test and there are no duplicates.
thanks for the help!
Joanne
Hi all,
i am trying to generate a list that did not not have a test and there are no duplicates.
thanks for the help!
Joanne
You can autopopulate data using countifs and ranking.
joannelittell.xlsx
Does it help?
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
A Pivot Table has to be the easiest, and least resource demanding solution.
Please click the * icon below if I have helped.
@sktneer
That's the prettiest advanced filter macro I've ever seen.
these are good ideas, unfortunately they won't work.
@sktneer- very cool programming, but, i just need the list to generate automatically.
@daffodil11- the results include the test = Yes. i only need in the list test = No
I can modify daffodil11's quite easily, however, is there another way to do the calculations without having to create columns with numbers 1-24 (i.e., column A)? like use the function "Row"?
can anyone help?
thanks
Joanne
array at J3
=IFERROR(INDEX(B$3:B$21,SMALL(IF(MATCH($B$3:$B$21&$C$3:$C$21&$D$3:$D$21&$E$3:$E$21&$F$3:$F$21&$G$3:$G$21,$B$3:$B$21&$C$3:$C$21&$D$3:$D$21&$E$3:$E$21&$F$3:$F$21&$G$3:$G$21,0)+ROW($B$2)=ROW($B$3:$B$21),ROW($B$3:$B$21)-ROW($B$2)),ROW(1:1))),"")
copy right and down
@Ghozi Alkatiri
Pretty cool formula. Very good use of index, small, match and rows together.
You're missing the condition that Test (column H) = no.
Array entered**:
=IFERROR(INDEX(B$3:B$21,SMALL(IF($H$3:$H$21="no",IF(MATCH($B$3:$B$21&$C$3:$C$21&$D$3:$D$21&$E$3:$E$21&$F$3:$F$21&$G$3:$G$21,$B$3:$B$21&$C$3:$C$21&$D$3:$D$21&$E$3:$E$21&$F$3:$F$21&$G$3:$G$21,0)+ROW($B$2)=ROW($B$3:$B$21),ROW($B$3:$B$21)-ROW($B$2))),ROWS(J$3:J3))),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thank you both for such fast replies that work!
really appreciate you
thanks
Joanne
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks