Grouping rows based on header cells

R

rbtr8r

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?
 
D

Dave Peterson

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.
 
Top