at if "delete" formula

[ab3d4u]

I downloaded a huge file in to excel. I selectively need to delete som
entries which in this case are all in "rows" NOT columns. Is there
formula to do this?
Example
A B C
1.J.Smith 12/01/95 Active
rose - flower
2. D. Aaron 1/2/08 Active
Pink - flower
3. G. Stephen 9/04/04 Inactive
rose and pink -flower
What I want to do is sort a) by type of flower they sale b) selec
when they sale more than one product c) by hire date.
The file has 185 employees. Each person's info is in two rows. Sort t
be done by the second row only

 

[Conan Kelly]

ab3d4u,

First, I would say that you need to "normalize" this data by getting each
persons info on one row. After that, the rest of the stuff will be easier
to do.

I would get the rose/flower info in column D. If your data starts in row 1
with no column headers/labels, then insert a row before row 1. Now your
data starts in row 2 with row 1 being blank. Select just the data in colum
A, so from your description, you should select cells A2 to A371 (after
inserting a blank row). Copy and paste into cell D1. In E1 enter this
formula:

=isodd(row())

I would now insert another blank row before row 1 and throw in column
lables/headers into A1:E1. Put anything you want in there, they will get
deleted later. Select A3. Turn on Auto Filter (click Data menu > Filter >
AutoFilter). Filter column E for TRUE. Select all data (A3:E372). Select
only visible data ([Alt] + [;:]). Delete cells/rows ([Ctrl] + [Shift] +
[=+]). Unfilter, turn off Auto Filter, remove extra rows at the top, remove
column E, and fill in column headers appropriately.

After that is all done, then we can work on the other stuff.

HTH,

Conan

 

[Conan Kelly]

If, you have column headers to begin with, adjust my instructions as
necessary in order to achieve the same results.

ab3d4u,

First, I would say that you need to "normalize" this data by getting each
persons info on one row. After that, the rest of the stuff will be easier
to do.

I would get the rose/flower info in column D. If your data starts in row
1 with no column headers/labels, then insert a row before row 1. Now your
data starts in row 2 with row 1 being blank. Select just the data in
colum A, so from your description, you should select cells A2 to A371
(after inserting a blank row). Copy and paste into cell D1. In E1 enter
this formula:

=isodd(row())

I would now insert another blank row before row 1 and throw in column
lables/headers into A1:E1. Put anything you want in there, they will get
deleted later. Select A3. Turn on Auto Filter (click Data menu > Filter

AutoFilter). Filter column E for TRUE. Select all data (A3:E372).

Select only visible data ([Alt] + [;:]). Delete cells/rows ([Ctrl] +
[Shift] + [=+]). Unfilter, turn off Auto Filter, remove extra rows at the
top, remove column E, and fill in column headers appropriately.

After that is all done, then we can work on the other stuff.

HTH,

Conan

I downloaded a huge file in to excel. I selectively need to delete some
entries which in this case are all in "rows" NOT columns. Is there a
formula to do this?
Example
A B C
1.J.Smith 12/01/95 Active
rose - flower
2. D. Aaron 1/2/08 Active
Pink - flower
3. G. Stephen 9/04/04 Inactive
rose and pink -flower
What I want to do is sort a) by type of flower they sale b) select
when they sale more than one product c) by hire date.
The file has 185 employees. Each person's info is in two rows. Sort to
be done by the second row only.

 

[Lt. Dan]

Hello Conan,

I'm wondering if ab3d4u is aware that that the function =ISODD() is included
in Excel's Analysis Tool Pack as an Add-in. Doesn't the Tool Pack Add-in
have to be loaded before ab3d4u can call the ISODD function? Just wondering
if that might be holding things up for ab3d4u.

Lt. Dan

ab3d4u,

First, I would say that you need to "normalize" this data by getting each
persons info on one row. After that, the rest of the stuff will be easier
to do.

I would get the rose/flower info in column D. If your data starts in row 1
with no column headers/labels, then insert a row before row 1. Now your
data starts in row 2 with row 1 being blank. Select just the data in colum
A, so from your description, you should select cells A2 to A371 (after
inserting a blank row). Copy and paste into cell D1. In E1 enter this
formula:

=isodd(row())

I would now insert another blank row before row 1 and throw in column
lables/headers into A1:E1. Put anything you want in there, they will get
deleted later. Select A3. Turn on Auto Filter (click Data menu > Filter >
AutoFilter). Filter column E for TRUE. Select all data (A3:E372). Select
only visible data ([Alt] + [;:]). Delete cells/rows ([Ctrl] + [Shift] +
[=+]). Unfilter, turn off Auto Filter, remove extra rows at the top, remove
column E, and fill in column headers appropriately.

After that is all done, then we can work on the other stuff.

HTH,

Conan

I downloaded a huge file in to excel. I selectively need to delete some
entries which in this case are all in "rows" NOT columns. Is there a
formula to do this?
Example
A B C
1.J.Smith 12/01/95 Active
rose - flower
2. D. Aaron 1/2/08 Active
Pink - flower
3. G. Stephen 9/04/04 Inactive
rose and pink -flower
What I want to do is sort a) by type of flower they sale b) select
when they sale more than one product c) by hire date.
The file has 185 employees. Each person's info is in two rows. Sort to
be done by the second row only.