Hi,

I have a question that I'm sure will have been asked before, but I
can't for the life of me find it by searching - all i find is
information on using Data Sort.

The problem is, after manipulating some personnel data, I'm left with
the following layout of information:

Column A
....
DEPARTMENT: Human Resources
Adam Brown
Cecil Davies
DEPARTMENT: Finance
Elliot Farthing
Gary Holmes
Ian Jacobs
DEPARTMENT: Human Resources
Kris Lambs
DEPARTMENT: Finance
Mark Namara
....

Column B is blank for the Department rows but contains personnel ID
numbers for the employee rows. What I want to do is sort/group this
information by department and their corresponding employee ID's. ie:
....
DEPARTMENT: Finance
Elliot Farthing 123
Gary Holmes 456
Ian Jacobs 789
Mark Namara 246
DEPARMENT: Human Resources
Adam Brown 810
Cecil Davies 121
Kris Lambs 416
....

Can anyone please tell me how to go about doing this?

I'd try to put the department name on each row.

I inserted a new column A.
I put this in A1: =TRIM(MID(B1,12,255))
(It returned "Human Resources")
Then I put this in A2: =IF(LEFT(B2,11)="department:",TRIM(MID(B2,12,255)) ,A1)
and dragged that formula down as far as column B had data.

I ended up with:

Human Resources DEPARTMENT: Human Resources
Human Resources Adam Brown
Human Resources Cecil Davies
Finance DEPARTMENT: Finance
Finance Elliot Farthing
Finance Gary Holmes
Finance Ian Jacobs
Human Resources DEPARTMENT: Human Resources
Human Resources Kris Lambs
Finance DEPARTMENT: Finance
Finance Mark Namara

Then I could convert column A to values
(select column A, Edit|copy, edit|paste special|values)

and do what I wanted with the data...including filtering column B to show only
those rows that begin with "department: " and delete those visible rows.



wrote:

Hi,

I have a question that I'm sure will have been asked before, but I
can't for the life of me find it by searching - all i find is
information on using Data Sort.

The problem is, after manipulating some personnel data, I'm left with
the following layout of information:

Column A
...
DEPARTMENT: Human Resources
Adam Brown
Cecil Davies
DEPARTMENT: Finance
Elliot Farthing
Gary Holmes
Ian Jacobs
DEPARTMENT: Human Resources
Kris Lambs
DEPARTMENT: Finance
Mark Namara
...

Column B is blank for the Department rows but contains personnel ID
numbers for the employee rows. What I want to do is sort/group this
information by department and their corresponding employee ID's. ie:
...
DEPARTMENT: Finance
Elliot Farthing 123
Gary Holmes 456
Ian Jacobs 789
Mark Namara 246
DEPARMENT: Human Resources
Adam Brown 810
Cecil Davies 121
Kris Lambs 416
...

Can anyone please tell me how to go about doing this?

--

Dave Peterson

Ingenious! Thanks Dave.

On Sep 19, 9:36 pm, Dave Peterson wrote:
I'd try to put the department name on each row.

I inserted a new column A.
I put this in A1: =TRIM(MID(B1,12,255))
(It returned "Human Resources")
Then I put this in A2: =IF(LEFT(B2,11)="department:",TRIM(MID(B2,12,255)) ,A1)
and dragged that formula down as far as column B had data.

I ended up with:

Human Resources DEPARTMENT: Human Resources
Human Resources Adam Brown
Human Resources Cecil Davies
Finance DEPARTMENT: Finance
Finance Elliot Farthing
Finance Gary Holmes
Finance Ian Jacobs
Human Resources DEPARTMENT: Human Resources
Human Resources Kris Lambs
Finance DEPARTMENT: Finance
Finance Mark Namara

Then I could convert column A to values
(select column A, Edit|copy, edit|paste special|values)

and do what I wanted with the data...including filtering column B to show only
those rows that begin with "department: " and delete those visible rows.