Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

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 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
NieveWalker1
Asked:
NieveWalker1
1 Solution
 
Saqib Husain, SyedEngineerCommented:
One way is to apply data filter and then copy the filtered list to the other spreadsheet.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now