transfer data from one worksheet to another

[Allan Jackson]

Can anyone please help
I have a worksheet that contains 12 columns and a variable number of rows.
the 4 main columns
Col A = Identity Number
Col B = Surname
Col C = First name
Col D = Department
etc
There is a separate worksheet for each department.There are 6 departments
and I need to transfer the identity number,names and details held in the
remaining columns to a separate worksheet depending upon what department the
person belongs to. The data on the master list is not sorted in any
particular order. Is there a way to copy this data to each sheet without
having to sort and then copy/paste to each worksheet. Ideally I would like a
formula in each worksheet that will transfer the data depending upon the
department name. I can do this however each row has a "if statement" formula
and if the department name does not match I am then left with a blank row. I
do not want numerous blank rows amongst the other data. I know I can then
sort the data and remove the blank rows but this has to be done every time a
change is made to the master worksheet (addition, deletion or modification
of department etc). this sort then throws out other formula references. I
hope that you can understand what I am trying to achieve.
Many Thanks
Allan

 

[Ron de Bruin]

Hi Allan

Start here and post back if you need help
http://www.rondebruin.nl/copy5.htm

 

[Max]

Perhaps this "formulas only" approach could also work ..

In sheet: Master
----------------------
You have the 12 cols across in cols A:L
headers in row1, data in row2 down
(Identity Number, Surname ... etc)

List across in row1 in 6 empty cols to the right,
say in cols N:S, the 6 dept names: Dept1, Dept2 ... Dept6

Put in N2: =IF(ISBLANK($D2),"",IF($D2=N$1,ROW(),""))

Copy N2 across to S2, then down as many rows as there is data

This will set it up for us to extract nicely into each of the dept sheets

In sheet: Dept1
---------------------
With the same headers in row1 across in cols A:L

Put in A2:

=IF(ISERROR(MATCH(SMALL(Master!$N:$N,ROW(A1)),Master!$N:$N,0)),"",OFFSET(Mas
ter!$A$1,MATCH(SMALL(Master!$N:$N,ROW(A1)),Master!$N:$N,0)-1,COLUMN(A1)-1))

Copy A2 across to L2,
then copy down a "safe" number of rows, say to L100.

In A2:L100 will be the extracted details
of all the 12 cols of info for "Dept1" staff from sheet: Master
(.. and there'll be *no* blank rows)

For a neater look, we can suppress extraneous zeros
from showing in A2:L100 via:
Tools > Options > View tab > Uncheck "Zero values" > OK
--
Repeat the construct above
for the rest of the other depts' sheets: Dept2 .. Dept6

The only change needed in the formula in A2 for each dept
is to the col ref "$N:$N".

For each dept, just change the col ref "$N:$N" to point
to the dept's column set-up in sheet: Master
(viz. col N is for Dept1, O for Dept2 ... S for Dept6), viz.:

Dept2: Change $N:$N to $O:$O
Dept3: Change $N:$N to $P:$P
Dept4: Change $N:$N to $Q:$Q
Dept5: Change $N:$N to $R:$R
Dept6: Change $N:$N to $S:$S

Just copy>paste the formula above for Dept1 into A2 in each sheet,
then do an Edit > Replace to change the col refs in the formula

Fill A2 across to L2, then down (as done for Dept1)

 

[Max]

A fine clarification (just in case) ..

List across in row1 in 6 empty cols to the right,
say in cols N:S, the 6 dept names: Dept1, Dept2 ... Dept6

The names listed across, viz.: Dept1, Dept2 ... Dept6
must of course, be the *actual* 6 department names
matching those listed down in Col D (department)

 

[Allan Jackson]

Hi Ron,
Thanks for the link.
Regards Allan

Hi Allan

Start here and post back if you need help
http://www.rondebruin.nl/copy5.htm

 

[Allan Jackson]

Max,
Many thanks, it works and you have saved me a lot of time.
Best regards
Allan

 

[Max]

Pleasure' Allan !
Thanks for the feedback