Extract The Entire Row Based on One cell Value
Hello To All
Please let me know the excel formula of how to extract the entire row based on one single cell value. For eg: If B2 has "Yes" than entire row of 2 should be copied in another table. Please provide formula not VBA Code..
This formula should be designed in such manner that it focus on entire column till bottom of the sheet as we don't know where till the sheet records will go and on how many places B row will have yes and need to copy the entire row will arise.
This thread is very similar to what you want to do:
https://www.excelforum.com/showthread.php?t=1141058
Post #4
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hi Tony,Originally Posted by Tony Valko
I have posted one image below could you please have a look to understand the situation I want to overcome..
If you mean to move the original then this isn't possible with a formula you would need VBA in order to move from one location to another. You can easily do this in VBA using filter function which I'm happy to explain if you decide you would actually like the code.
If you mean to look up values based on yes criteria a sample table would be helpful
One way
=IFERROR(INDEX(Sheet1!A$2:A$1000,SMALL(IF(Sheet1!$B$2:$B$1000="yes",ROW($A$2:$A$1000)-ROW($A$2)+1,""),ROWS($1:1)),COLUMN($A:A)),"")
Enter with Ctrl+Shift+Enter
Copy across and down as far as required.
Resource hungry!!!!
Hi JohnTopley
Could you please assist me in understanding in how to work on scenario posted by me below. I tried a lot to implement this formula but its not working.
Must it be a formula? The easiest "off the shelf" solution that I see is to use Excel's filter tools. Advanced filter in particular can easily filter a list based on a value in one column and copy the filtered list into another range. https://support.office.com/en-us/art...8-3f16abdff32b
Its getting complicated to apply
Please check the attached file and provide solution in that. If in C column there is some employee who has left than how can I have the entire row in table two just based on one cell value.
xl.png
Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.
If you need to post an image post it in the *.jpg format.
Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.
xl.jpg
Resubmitting image in jpeg
Hi Tony please have a look at the image once again
Try this array formula** entered in H3:
=IFERROR(INDEX(A:A,SMALL(IF($C$3:$C$8="LEFT",ROW(A$3:A$8)),ROWS(H$3:H3))),"")
** 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.
Copy across to M3 then down until you get a row full of blanks.
Put this in H3
=IFERROR(INDEX(Sheet1!A$3:A$100,SMALL(IF(Sheet1!$C$3:$C$100="LEFT",ROW($A$3:$A$100)-ROW($A$3)+1,""),ROWS($1:1)),COLUMN($A:A)),"")
Enter with Ctrl+Shift+Enter
Copy (drag) across to M
then down
OR
=IFERROR(INDEX(Sheet1!$A$3:$F$100,SMALL(IF(Sheet1!$C$3:$C$100="LEFT",ROW($A$3:$A$100)-ROW($A$3)+1,""),ROWS($1:1)),COLUMNS($A:A)),"")
As above
Last edited by JohnTopley; 05-26-2016 at 04:47 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks