Solved

# In Excel check a range of data for a particular value in one column. If the condition is met show all rows in a another section of the spreadsheet.

Posted on 2011-10-28
272 Views
In Excel check a range of data for a particular value in one column. If the condition is met show all rows in a another section of the spreadsheet.
In this example if Account on hold = 'Yes' display/copy the entire row to another section of the spreadsheet.

Brief sample of spreadsheet with results

Client                  Account on hold
Joe Bloggs          Yes
Pat Smith             No
John Jones          Yes

Accounts on Hold
Client                  Account on hold
Joe Bloggs          Yes
John Jones         Yes

I'm using Excel 2010.

Thanks!
0
Question by:NieveWalker1

LVL 43

Expert Comment

One way is to apply data filter and then copy the filtered list to the other spreadsheet.
0

LVL 50

Accepted Solution

Hello,

you can create a dynamic solution with formulas if you use a helper column in your original data table:

=IF(B2="yes",ROW(),"")

Then you can use a formula like this to retrieve the values in another part of the sheet

=IF(ISNUMBER(SMALL(\$F:\$F,ROW(A1))),INDEX(\$A\$1:\$F\$20,SMALL(\$F:\$F,ROW(A1)),COLUMN(A1)),"")

see attached. I've marked the cells that are monitored by the formula and the cells where the formula is applied with background fill.

cheers, teylyn
Book3.xlsx
0

## Featured Post

### Suggested Solutions

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
Viewers will learn the different options available in the Backstage view in Excel 2013.