Hello,
I had a quick question I hope someone can answer.
I have a list of over 400 names. I am trying to extract any name that has a "0" assosciated with the name, and place them in a separate worksheet. Some names do not have 0, but just have a blank. I would not want them on this list.
Example:
John 0
Cindy 1
Steve 3
Jason 4
Gary 5
Michelle
Alex 0
The other worksheet would then display:
John 0
Alex 0
Any help?
Hi,
Assuming your source data is in the range A2:B400 on Sheet1, enter this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) in your other sheet and copy down until you start to get blanks:
=IFERROR(INDEX(Sheet1!$A$2:$A$400,SMALL(IF(EXACT(Sheet1!$B$2:$B$400,0),ROW(Sheet1!$B$2:$B$400)-MIN(ROW(Sheet1!$B$2:$B$400))+1),ROWS($1:1))),"")
Regards
Here!
See the file attached.
Change it to whichever sheet you like..
Deep
Cheers!
Deep Dave
I am unclear whether the word and number are in the same Cell or different cells.
Here is a solution assuming it is in different cells -
Move it to whichever sheet you want.
Hope it helps.
Deep
Please see attached file.
List of names located on Sheet6 and extracted names on Sheet5Test_ExtractedNames.xlsx
Formula needs to entered as an array (Ctrl+Shift+Enter)
Last edited by AlKey; 08-12-2013 at 04:34 PM.
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
Wow! I just came back to this, am about to leave for the night... but thank all of you so much for the help. That's great!
You're Welcome. Don't forget to thank those who helped by clicking on Add Reputation *
Rep added to all.. thank you for the quick and helpful information.. Really appreciate it.
Quick question on this... Msexcel, I am playing with your formula, one thing it is doing is pulling names that have no value (blank cell), when it really has to pull a cell that has "0.00%" in it. For yours I have "=IFERROR(INDEX(Sheet16!$A$9:$A$668,SMALL(IF(--(Sheet16!$I$9:$I$668=0)=1,ROW(Sheet16!$A$9:$A$668)-ROW(Sheet16!$A$9)+1),ROW(Sheet16!A12))),"")" and am playing around with the IF functionality to see if I can have it not pull blank cells.
AlKey I am playing with yours because it seems to be built a little differently and might fix that, but I am having a hard time making it work. I will keep playing around. Thanks for all your help! This is what I had for yours: "=IFERROR(INDIRECT("Sheet16!A"&SMALL(IF(ISERROR(SEARCH("0%",Sheet16!$A$9:$A$668)),"",ROW(Sheet16!$A$9:$A$668)),ROW(A9))),"")" .. Ctrl shift enter just ends up being a blank.
Thanks again!
Ah, I see why with the 2nd function, the names are in Column A with the values I am needing to pull in Column I, which is why Msexcel's works.
I see that you have there "0%" instead of "0". Could that be a problem?. It would be easier if you upload a spreadsheet with a sample data.
examples.xlsxSo here would be an example of how this would look.Originally Posted by AlKey
Ok, you want the names that have "0" on the end to be extracted but without "0"? Correct?
That part doesn't really matter too much.. It's just that it cannot extract names that have a blank in the cell... So like cell I8 that is blank, would not extract.. but the value in cell I5 with 0.00% would.
Now I am totally confused. If the cell is blank this means that there is nothing in cell. Besides, this total contradicts the requirements that you provided in the original post. Unless, what describe as "blank" is space?
So in the original post, Michelle has no number next to her name. She would not be pulled into the extracted list. Anything other than 0.00%, including an empty cell, would not be pulled. Sorry if I did not explain that well.
Here!
Try this in Sheet Zeros cell E2 -
Use Ctrl+Shift+Enter
Deep
Please Login or Register to view this content.
Last edited by NeedForExcel; 08-13-2013 at 12:10 PM.
It worked! You rock. Thanks so much for the help.
Glad it helps!
Deep
If you ever feel like taking a crack at this one, I could always use the help!
http://www.excelforum.com/excel-form...html?p=3359359
?? Why isn't that just an almost exact duplicate of the very first formula solution (ranges amended, of course) which I posted many hours ago (and for which I am yet to receive any feedback)?
Regards
Sorry I missed it, I believe I gave a rep for it but think I went straight to the workbooks and forgot. Apologies though, in looking at it, it looks great!
No worries.
Haha.. Thats Strange! How did everyone miss XOR LXs solution?Did it disappear for a while!!
A 24 Post Thread, with a solution already there in the 2nd post..
Maybe that's some sort of record!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks