I need some help cleaning up an imported document. Right now, each employee
appears numerous times, and I need to condense it to one row per employee.
All of the information - barring one piece - already appears on one row.

Here is what I know:

Column A: contains the employee name (already sorted)

Column B: determines the validity of the row. A lot of gibberish (left
over from headers, page breaks, etc) is included in this report, but the one
thing that I can rely on is that if "Bldg. A", "Bldg. B", OR "Bldg. C"
appears in column B, then this is the row that contains most of the employee
data and must not be deleted. I'll call this "MySaveRow".

Column E: the problem child - may or may not include data that must be
saved. For each employee in column A, if column E of MySave row is Null,
then I can quit looking and go to the next employee. If E is not Null, then
I need to see if it contains "FUL", "PR4", "MGR", OR "CON" . If one of
those is found, I would like column F of MySaveRow to be written to. For
example, if "FUL" is found, I would like column F of MySaveRow to say:
"Full time", if "CON" then "Consultant". If E is not Null but does not
contain "FUL", "PR4", "MGR", OR "CON", then I need to keep checking for
those in each row until I get to the next employee. They won't
necessarily be found.

Once I capture any required information from Column E and place the text
remark in column F of MySaveRow, then anything that is not MySaveRow should
be deleted.

Any help *greatly* appreciated!

Patti

Hi
you may also post some example data as plain text :-)
Some questions:
- you can have several rows per employee?
- is column B the only relevant column to identify if a row should
stay?

--
Regards
Frank Kabel
Frankfurt, Germany

"Patti" schrieb im Newsbeitrag
...
I need some help cleaning up an imported document. Right now, each
employee
appears numerous times, and I need to condense it to one row per
employee.
All of the information - barring one piece - already appears on one
row.

Here is what I know:

Column A: contains the employee name (already sorted)

Column B: determines the validity of the row. A lot of gibberish
(left
over from headers, page breaks, etc) is included in this report, but
the one
thing that I can rely on is that if "Bldg. A", "Bldg. B", OR "Bldg.
C"
appears in column B, then this is the row that contains most of the
employee
data and must not be deleted. I'll call this "MySaveRow".

Column E: the problem child - may or may not include data that must
be
saved. For each employee in column A, if column E of MySave row is
Null,
then I can quit looking and go to the next employee. If E is not
Null, then
I need to see if it contains "FUL", "PR4", "MGR", OR "CON" . If one
of
those is found, I would like column F of MySaveRow to be written to.
For
example, if "FUL" is found, I would like column F of MySaveRow to
say:
"Full time", if "CON" then "Consultant". If E is not Null but does
not
contain "FUL", "PR4", "MGR", OR "CON", then I need to keep checking
for
those in each row until I get to the next employee. They won't
necessarily be found.

Once I capture any required information from Column E and place the
text
remark in column F of MySaveRow, then anything that is not MySaveRow
should
be deleted.

Any help *greatly* appreciated!

Patti

Hi Frank,

You are correct - each employee can have several rows and column B is the
only relevant column to determine what rows should stay after I am done
processing. Here is a dummy sample (hopefully the columns will remain
somewhat aligned):

Column A Column B Column E


EMPLOYEE NAME CAT
-------------------- ----
Anderson, Daniel Bldg A
Anderson, Daniel ---
Brown, Wendy Bldg A ----
Brown, Wendy --- --
Brown, Wendy CON
Brown, Wendy -----------
Brown, Wendy
Daniels, Joe Bldg B FUL
Daniels, Joe
Daniels, Joe


So for Anderson, since column E of his first row is Null, I don't need to
look any further (the rest will be Null too). I can just delete the next
row and move to Brown.

For Brown, column E of her first row is not Null, but it also doesn't
contain "FUL", "PR4", "MGR", OR "CON" . So I want to loop through the rest
of the rows for Brown until I hit "CON". Now I have found what I am looking
for, so I want to go back to the first row with her name (the one with "Bldg
A" in column B) and enter "Consultant" in column F. Now Brown has a
complete record and I can delete all of her rows except the one with "Bldg
A" in column B.

Moving forward to Daniels, I find "FUL" in column E of his first row. Since
this is meets my criteria, I can quit looking, enter "Full time" in column
F, and delete the rest of his rows.

I hope this is clear - if not please let me know.

(I do have a macro where I just delete any row in my range that does not
have Bldg. A", "Bldg. B", OR "Bldg.
C" in column B, but I've discovered that I was deleting the employee
category if it was not in the same row as the Bldg. In most cases it will
be, but I need to catch those cases where it isn't.)

PS. The dashes are garbage that come over with the import process. They are
not consistently the same length. There may also be other errant
characters, but not in the same cell as the valid data.

Thanks!

Patti






"Frank Kabel" wrote in message
...
Hi
you may also post some example data as plain text :-)
Some questions:
- you can have several rows per employee?
- is column B the only relevant column to identify if a row should
stay?

--
Regards
Frank Kabel
Frankfurt, Germany

"Patti" schrieb im Newsbeitrag
...
I need some help cleaning up an imported document. Right now, each
employee
appears numerous times, and I need to condense it to one row per
employee.
All of the information - barring one piece - already appears on one
row.

Here is what I know:

Column A: contains the employee name (already sorted)

Column B: determines the validity of the row. A lot of gibberish
(left
over from headers, page breaks, etc) is included in this report, but
the one
thing that I can rely on is that if "Bldg. A", "Bldg. B", OR "Bldg.
C"
appears in column B, then this is the row that contains most of the
employee
data and must not be deleted. I'll call this "MySaveRow".

Column E: the problem child - may or may not include data that must
be
saved. For each employee in column A, if column E of MySave row is
Null,
then I can quit looking and go to the next employee. If E is not
Null, then
I need to see if it contains "FUL", "PR4", "MGR", OR "CON" . If one
of
those is found, I would like column F of MySaveRow to be written to.
For
example, if "FUL" is found, I would like column F of MySaveRow to
say:
"Full time", if "CON" then "Consultant". If E is not Null but does
not
contain "FUL", "PR4", "MGR", OR "CON", then I need to keep checking
for
those in each row until I get to the next employee. They won't
necessarily be found.

Once I capture any required information from Column E and place the
text
remark in column F of MySaveRow, then anything that is not MySaveRow
should
be deleted.

Any help *greatly* appreciated!

Patti