Copy entire Row (via formula) if multiple criteria met

SuzyQPA2

Board Regular
Joined
Oct 28, 2004
Messages
90
Hi,
I have columns & rows of data

I have inserted Columns where i am comparing data and get the result True/False.
What i would like is a formula that pulls a row that has any false in it to a summary worksheet (say Sheet1)

Thanks any help appreciated.
I would prefer a formula if possible to do by formula.
eg
Excel Workbook
ABCDEFGHI
1GroupAcctMemberNameFacType per rateType per FidMatch TypePlan Per IBXPlan per FidMatch Plan
2123456Doe, JaneAMsinglesingleTRUEHMO HighHMO High FTTRUE
3333328Wolf, Red RidingAMSINGLESINGLETRUEDIRECTPOSHMO HIGH FT- AMERIHEALTHFALSE
4123456Mouse, MickeyAMFamilysingleFALSEHMO HighHMO High FTTRUE
AM-IBX
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
You could have an extra column to make a pointer, and then reference it in your summary sheet, like this:

Excel Workbook
ABCDEFGHIJ
1GroupAcctMemberNameFacType per rateType per FidMatch TypePlan Per IBXPlan per FidMatch PlanExtract pointer
2123456Doe, JaneAMsinglesingleTRUEHMO HighHMO High FTTRUE0
3333328Wolf, Red RidingAMSINGLESINGLETRUEDIRECTPOSHMO HIGH FT- AMERIHEALTHFALSE1
4123456Mouse, MickeyAMFamilysingleFALSEHMO HighHMO High FTTRUE2
Sheet2



with the summary sheet fetching rows like this:

Excel Workbook
ABCDEFGHI
1GroupAcctMemberNameFacType per rateType per FidMatch TypePlan Per IBXPlan per FidMatch Plan
2333328Wolf, Red RidingAMSINGLESINGLETRUEDIRECTPOSHMO HIGH FT- AMERIHEALTHFALSE
3123456Mouse, MickeyAMFamilysingleFALSEHMO HighHMO High FTTRUE
4         
Sheet1
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,249
Office Version
  1. 365
Platform
  1. Windows
My suggestion is similar in concept. Formulas a little different.

Sheet2
If I have understood your requirements correctly then you could use these formulas in F2 and I2 (copied down) instead of the formulas you currently have. If I have that wrong then keep your current formulas but the rest of my suggestion should still hold.

J1 is blank or houses a 0
J2 copied down

Excel Workbook
ABCDEFGHIJK
1GroupAcctMemberNameFacType per rateType per FidMatch TypePlan Per IBXPlan per FidMatch Plan2
2123456Doe, JaneAMsinglesingleTRUEHMO HighHMO High FTTRUE0
3333328Wolf, Red RidingAMSINGLESINGLETRUEDIRECTPOSHMO HIGH FT- AMERIHEALTHFALSE1
4123456Mouse, MickeyAMFamilysingleFALSEHMO HighHMO High FTTRUE2
5
Sheet2



Sheet1
J1 is blank or houses a 0
J2 copied down.
A2 copied across to column I and then A2:I2 copied down.

Excel Workbook
ABCDEFGHIJ
1GroupAcctMemberNameFacType per rateType per FidMatch TypePlan Per IBXPlan per FidMatch Plan
2333328Wolf, Red RidingAMSINGLESINGLETRUEDIRECTPOSHMO HIGH FT- AMERIHEALTHFALSE1
3123456Mouse, MickeyAMFamilysingleFALSEHMO HighHMO High FTTRUE2
4
Sheet1
 

SuzyQPA2

Board Regular
Joined
Oct 28, 2004
Messages
90
Peter,
I would either leave the True/False columns in or have the differences highlighted with a conditional formula. That way there is a summary page with the differences and a detail page with all data showing differences.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,249
Office Version
  1. 365
Platform
  1. Windows
Peter,
I would either leave the True/False columns in or have the differences highlighted with a conditional formula. That way there is a summary page with the differences and a detail page with all data showing differences.
I was not suggesting that you remove the True/False columns. I was just suggesting simpler formulas to produce the True/False values in those columns.

Did you try either of the suggestions for producing the summary page? Did they produce the desired result.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,041
Messages
5,526,417
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top