I don't know whatt to call it ??

[john rennie]

I have a list of homes that are "Active", "Sold", "Pending" and "Backup
on a sheet1.
I would like to put only the
"Active" on sheet2,
"Sold" on sheet3
and "Pending" and "Backup" on sheet4 note there are 2 criteria in thi
last part

Note that I want all the data from each record to paste to ther
respective pages. ie List Price, Sale Price, Bedrooms, Baths, Street
etc

At the end of the day I should have 4 work sheets

sheet1 all the raw data

sheet2 all the Active homes with there List Price, Sale Price
Bedrooms, Baths, Street, etc

sheet3 all the Sold homes with there List Price, Sale Price
Bedrooms, Baths, Street, etc

sheet4 all the Pending AND Backup homes with there List Price, Sal
Price, Bedrooms, Baths, Street, etc


Once I get this done I can then do caculations on each group. i
average list price of all Active homes
average list price of all Sold homes
average list price of all Pending and Backup home

 

[Bob Umlas, Excel MVP]

You can sort all the data by the column containing these keywords, then
they'll be grouped together. Now you can cut each group (or copy) and paste
to the appropriate sheet and repeat this process for each of the groups.

 

[Dave]

IF formula's on all your other sheets will paste the correct data from sheet
one.
Dave

 

[john rennie]

Can you post a sample "IF" formul

 

[Don]

-----Original Message-----
Can you post a sample "IF" formula

=IF(SHEET1!A1="","",SHEET1!A1)

Put in Cell A1 of sheet2 and the data that is in cell A1
of sheet1 will appear....if no data is in Sheet1!A1,
Sheet2!A1 will remain blank.

HTH,

Don

 

[Dave]

or perhaps sheet1 A1 says "active" and sheet1 B1 is the address, and you
want active data on sheet 2, then in sheet2 A1 place this formula:

=IF(SHEET1!A1="active",SHEET1!B1,"") just alter this formula for
whatever conditions you need. Copy it down the columns and it will place
only "active" addresses on sheet 2.
It will leave gaps in rows that you will need to filter out........

 

[john rennie]

The If formulas works
but
It retuns 250 "active" and leaves the remaining 2500 fileds with th
word "FALSE"in them.
Is there a way to get rid of this additional "False" data withou
filtering or sorting?

 

[Dave]

are you putting those "" at the end of the formula?

 

[Dave Peterson]

I wouldn't separate my data.

I'd keep it altogether, sort it nicely and use Data|subtotals.

Maybe insert an extra column to merge "backup" and "pending" as one category.

And you may find using Data|pivottable makes life even simpler.