Hi Malcolm
You're welcome. Glad to know the problem is resolved.
Thanks for the feedback


--
Regards

Roger Govier


"malycom" wrote in message
...
Roger

You are a star.

Thanks very much....the problem has been resolved

"Roger Govier" wrote:

Hi Malcolm

Not a macro solution but a formula solution that should work.
Assuming your data is in columns A B and C and that column C will carry
the
new Director 1 value, E will have Director 2 etc. then
also assuming your data starts in row 3 as 1 is a header and 2 is a row
of
underlines,
enter in cell D3
=IF(OFFSET($B3,-1,0)=OFFSET($B3,1,0),"",IF(OFFSET($B3,COLUMN()-3,0)=$B3,OFFSET($B3,COLUMN()-3,1),""))
Copy this formula through columns E3:L3 (this will deal with up to 10
directors extend the range for more)
Copy D3:L3 down for as many rows as you have data.

Now, Mark row 1. DataFilterAutofilter and on the drop down for column D
select NonBlanks

Copy the block of filtered data and Paste SpecialValues to another
worksheet.


--
Regards

Roger Govier


"malycom" wrote in message
...
Hi

I have a spread sheet that has been soreted by client Key in ascending
order.

Currently, anyone who is a director to the client key is in a single
column.
For example, I may have 50 different client keys but because there are
3
directors for each key column A (CLient Key) will show 3 of each client
key
50 times. See below for quick example.

Client Key Full CLient Name Director
---------------------------------------------------------
AAA Appy Arry Director 1
AAA Appy Arry Director 2
AAA Appy Arry Director 3
BBB Bobs Beer Bike Director 1
BBB Bobs Beer Bike Director 2
BBB Bobs Beer Bike Director 3
CCC Chick Chick Chick Director 1
CCC Chick Chick Chick Director 1
CCC Chick Chick Chick Director 1

What I would like to happen is to be able to run a macro that will look
for
the client key in column A and remove the duplicated rows of
information
but
place the Director 2 and Director 3 into there own seperate colum as we
will
need this information. See below for how it should then look.

Client Key Full CLient Name Director Director
2
Director 3
---------------------------------------------------------------------------------------------
AAA Appy Arry Director 1
Director
2
Director 3
BBB Bobs Beer Bike Director 1
Director 2
Director 3
CCC Chick Chick Chick Director 1 Director
2
Director 3

Please could you give as much information as possible as to how to
achieve
this as I am a complete novice at this stuff.

Also, I have seperate worksheets that will be running a similar routine
but
each worksheet was created based on how many directors are for each
client,
so in this example there are 3 directors for every client but other
worksheets will have 4 or even 5 directors. Please try and explain the
code
so I can then copy the information into the other sheets and not have
to
re-write completely in order to run for the other sheets.

Thanks in advance

Malcolm