Hi!
Is there a way to separate males from females from a continuous list of students in sheet1 into sheet2?
Please see the attachment for example.
Thank you.
Hi!
Is there a way to separate males from females from a continuous list of students in sheet1 into sheet2?
Please see the attachment for example.
Thank you.
Paste in B21 on Sheet 2 and copy down...
=IFERROR(INDEX(Sheet1!$B$2:$B$12,SMALL(IF(Sheet1!$C$2:$C$12=LEFT($A$20,1),ROW(Sheet1!$B$2:$B$12)-ROW(Sheet1!$B$2)+1),ROWS($B$21:B21))),"")
IMPORTANT
- This is an array formula
- Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
- If entered correctly, the formula will be enclosed in {brackets}
- Do not enter the {brackets} manually
HTH
Regards, Jeff
Thanks for the quick reply Sir. I will have to make further testing with the code but right now it works for the females, I have to test it out for the males. I'll be back for further queries if something comes up.
Thank you again sir.
Hello!
I tried the formula with my attachment. I found out the there were names not being reflected, like the first and the last three (3) students in female were not in promo sheet.
Looks Like you have a small typo in your formula. Change the highlighted value B57 to B56. Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
Please Login or Register to view this content.
Thanks,
Mike
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
oh my...been looking at the formula for so long and I didn't noticed that very minute detail yet it has a very big effect on the formula/output. Thanks mike for pointing it out...
Last edited by Simply_Me; 04-02-2017 at 05:50 AM.
Have you consider the use of a pivot table
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
could you please share the equation for finding male and female in 2 different excel sheets.
Administrative Note:
Hello Rohini Krishnakumar & Welcome to the forum,
We are happy to help; however, while you feel your request is similar to this thread, experience has shown things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please start a new thread - See Forum rule #4
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks