Copy-Paste Special - Transpose values if condition applies

Hi guys and anticipated thanks for the help:

Let me try describe the data first.
Worksheet 1:

C1 C2 C3 .... C8
M2 M3
M2 M20
M2 M31
M5 M4
M5 M10
M5 M13


To describe it: Column1 contains a value which may repeat...Column 8 has unique values which correspond to the content in Column 1. The "Ms" are random.

Worksheet 2:

C1 C2 C3 C4 C5 C....
M1
M2
M3
M4
M5
...
M50
M51
M52
...
M99
M100
...


Explain what my macro needs: Has to go through C1 of Worksheet 1and for each M should copy-paste transpose the values from C8 into worksheet 2 C2, C3 (whatever is needed)

in other words, Worksheet 2 should look like this:

C1 C2 C3 C4
M1
M2 M3 M20 M31
M3
M4
M5 M4 M5 M13
M6
M7
...


Many thanks in advance. MUCH MUCH appreciated

Please attach a sample workbook, including expected results.

Thanks a lot.
Here is the example data with results expected.

See if this does what you want:

Please Login or Register  to view this content.

This does the trick. Absolutely brilliant! Many many thanks!

Hey Stephen.

I have some problems when trying to actually implement it into my real data.
Some questions:

1) Does it still work if cells are actually references rather than actual values (which means that x1Down will go much lower than the last value available
2) In the real data GIVE CODE and GET CODE (headers) are B3 and I3 respectively (which means that content starts at B4 / I4)
3) In the actual "Macros Needed" sheet, there is an additional column between A and C.

I am sorry if this is really easy to adjust and I just don't see it. Could you help me out with this?

BIG THANKS!

Florin

Florin - easiest if you can attach a small workbook with a representative sample of your actual data.

OK, having all those blank vlookup formulae is not the most efficient way of doing things. The 'give milestone' values in CriticalPath are taken from the values in column A of the Input Milestones sheet as I understand it. If that's right, would it be an option to copy the latter list to CriticalPath and then run the rest of the macro. If you think that sounds sensible, I can add the copying bit to the beginning of the code.

Originally Posted by StephenR

OK, having all those blank vlookup formulae is not the most efficient way of doing things. The 'give milestone' values in CriticalPath are taken from the values in column A of the Input Milestones sheet as I understand it. If that's right, would it be an option to copy the latter list to CriticalPath and then run the rest of the macro. If you think that sounds sensible, I can add the copying bit to the beginning of the code.

Yes, that would definitely be an option. As long as the copy-paste is dynamic obviously - the milestone list will grow a lot. Is that possible at all?

Florin

Try this. Every time you run it, it clears the last sheet and copies over the data anew.

Please Login or Register  to view this content.

Stephen. This seems to do it perfectly. I am amazed. Thanks for being so helpful.
If I can make up for it, let me know - I see you're from London so I can cover the drinks in a pub

Hi stephen,
I have a problem in excel 2010, it's about copy-paste transpose.
May I know your email address?
I can't attach here.
thanks