I have partially figured this out and have attached a document with what i have.
I have a list of projects, document names, THREE testing points, etc. The goal is to asses if testing was NOT completed for a specific testing point and returns a list containing several bits of criteria that is unique to "document name".
I am able to create a list that assesses only ONE testing point and it is not unique to "document name".
Thanks in advance!
Joanne
Hopefully someone can come up with a less complex solution.
I created helper columns in K,L and M to count No's for Test 1,2 and 3 respectively. These columns can be hidden.
In N21 copied to the right and down
=IF(SUM($K$10:$M$10)>= ROW(A1), IF(ROW(A1)> $K$10, IF(ROW(A1)> ($K$10+$L$10), INDEX(A$2:A$10, MATCH(ROW(A1)-($K$10+$L$10),$M$2:$M$10,0)), INDEX(A$2:A$10, MATCH(ROW(A1)-$K$10,$L$2:$L$10,0))),INDEX(A$2:A$10, MATCH(ROW(A1),$K$2:$K$10,0))),"")
In R21 copied down
=IF(LEN(N21)>0,IF(ROW(A1)<=$K$10,"Test 1", IF(ROW(A1)<= $K$10+$L$10, "Test 2", "Test 3")),"")
In S21 copied down
=IF(SUM($K$10:$M$10)>= ROW(F1), IF(ROW(F1)> $K$10, IF(ROW(F1)> ($K$10+$L$10), INDEX(J$2:J$10, MATCH(ROW(F1)-($K$10+$L$10),$M$2:$M$10,0)), INDEX(H$2:H$10, MATCH(ROW(F1)-$K$10,$L$2:$L$10,0))),INDEX(F$2:F$10, MATCH(ROW(F1),$K$2:$K$10,0))),"")
See attachment
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
I bow to you, ChemistB... nice solution!
Here's an alternate way using pivot tables (no formulas needed!)... with one big caveat... you have to change the look of your data! Anyway, here's my take...
Thank you both for such fast solutions to my complex problem!!
Is there really such a place as Normal, Illinois? Does it live up to its name?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks