Macro to move data to different column based on data in another co

[malycom]

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

 

[Roger Govier]

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. Data>Filter>Autofilter and on the drop down for column D
select NonBlanks

Copy the block of filtered data and Paste Special>Values to another
worksheet.

 

[malycom]

Roger

You are a star.

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

 

[Roger Govier]

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