Hi,
attached is a workbook with two sheets. I wish to extract rows relating to a 'name' on the Data Sheet and gather them on the Analysis Sheet, prompted by entering a name in cell K2. So far so good, but I wish to have the command recognize and collect rows if a name appears more than once on the Data Sheet.
Thank you
John
In a4, copied across and down:
=IFERROR(INDEX(Data!A:A,AGGREGATE(15,6,ROW(Data!$B$1:$B$100)/(Data!$B$1:$B$10=Analysis!$K$2),ROWS(A$4:A4))),"")
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
Hi Glenn,
many thanks for the quick reply. I'm attaching an expanded version of your file (added names). I don't seem to be getting the additions to show up.
Cheers,
John
Theres a typo in mine.Both ranges should be the same, 100 or whatever. In my posted formula
make both 100, or more if needed.
. Much obliged. Have a good weekend.
You, too. You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
Glenn, I can't for the life of me do the solve thing. I could see the pull down menu but it has only two options; Solved is not one of them. The present screen as I reply does not show the 'Thread Tools' menu link anymore, or the add reputation for that matter. Unless I'm missing something. Sorry.
John
Hi Glenn, I'm back.
I wish to have rows that contain "0" in column D of the "Data" sheet not appear on the "Analysis" sheet. Thank you. File you worked with attached.
Hi. Modify to this, and drag across & down:
=IFERROR(INDEX(Data!A:A,AGGREGATE(15,6,ROW(Data!$B$1:$B$100)/((Data!$B$1:$B$100=Analysis!$K$2)*(Data!$D$1:$D$100<>0)),ROWS(A$4:A4))),"")
Thank you Glenn. Much obliged. Good day.
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks