Excel Macro to move data from column into rows

Can you post your sample file along with what it's right now and what you are looking for as it will be easy to write a macro for you post then..

Saurabh...

See attached.  The way the data exports is one Opportunity Number can contain many Record Types.  On the example provided you can see the duplicates have both First Call and Professional Services. For instance, the macro would find the duplicate at Opportunity Number 103701 (A3), would then move the information from Column L3,M3,N3, and U3 and move that information to V2, W2,X2, and Y2.  After doing that, it would delete the whole row of A3.

This does not have to be done by a macro if you know another solution.  Macro is the first thing I thought of.

oops, don't think I uploaded on the last comment.  Attached.
Screen-Shot-2015-09-02-at-4.34.16-PM.png

Csjoberg,

Quick question so you are saying if you find any data point of first call post that move that data point to the earlier one and delete the row..

Now help me understand what you want to do when you have 2 data points after first call?? What i meant to say like in your screenshot it's 2 data points of a same record what in case if the total number of data points are 3 rather then 2??

Also if you can give me a sample file rather then screenshot..it will be easy to write a macro since i don't have to create a file myself..You can give me all dummy data and i'm fine with that too..

Saurabh...

I think I understand what you're asking....are you saying if there are triplicates or even quadruples?  The way I have this report configured out of the program, it's only grabbing Professional Services and First Call records, therefore, there should never be more than two.  One Opportunity Number will only have one First Call and/or one Professional Services.  You would not have multiples of either.  Excel attached.
Macro-Example.xlsx

Enclosed is the macro code..Along with desired results which you can check in sheet2.. The only assumption i have made is that your professional call entry will be their for sure and it will be after first call...

Sub scrubdata()
    Dim lrow As Long, rng As Range

    lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

    Range("V2:X" & lrow).Formula = "=IF($U2=""First Call"",LOOKUP(2,1/(($A$2:$A$" & lrow & "=$A2)),L$2:L$" & lrow & "),"""")"
    Range("V2:X" & lrow).Value = Range("V2:X" & lrow).Value
    Set rng = Range("V2:X" & lrow).SpecialCells(xlCellTypeBlanks)

    If Not rng Is Nothing Then rng.EntireRow.Delete

    MsgBox "Done"

End Sub

Select allOpen in new window

Your workbook...

Saurabh...
Macro-Example.xlsm

Saurabh - Thanks for your help!  Unfortunately, I'm fear I may not have explained it correctly.  I ran your macro against my real spreadsheet which is 684 rows and maybe has 20 duplicates and it compresses the whole document to 44 rows.  Only the 20 duplicates should have been affected.  I have attached a spreadsheet with more rows.  The macro would need to find duplicate Opportunity Numbers and from there would move the data in the columns over.  It looks like it moves properly, but not sure what data it is finding to move and delete.  Again, I really appreciate your help!
macro-example-2.xlsx

csjoberg,

I asked you that question that time and you said everytime your report have two records and first call will be always be the first one and then Professional Services will be a next one. Now in the revised data which you uploaded that's not the case and their are lot of out liars in the data which are:-

1. If you check row number-2 the entry is blank their is no service associated to it..What did you do in this case??
2. if you row number-11 for 104017 you have this account only exist for First Call and their is no account of professional services..Which i'm not sure why that's the case?
3. Now if you row-27 104519 you have that this has only entries for professional services and it doesn't have the entry of First call..? Which is couple of moving particles...

Saurabh...

I apologize for the miscommunication.

1. Not all Opportunities Numbers have First Call or Professional Services so if it's blank then nothing would be done.  Also, if an Opportunity is not a duplicate, then nothing is done.  It's only when an opportunity has both First Call AND Professional Services.

2. The macro would only be for duplicates.  Row 11 is not a duplicate.

3. The macro would only be for duplicates.  Row 27 is not a duplicate.

The red highlights indicate Opportunity Numbers with both First Call and Professional Services, therefore it's duplicated minus the information we are moving.  Those would be the rows that would be "combined" essentially.  

Again, sorry for not answering your questions properly.

Okay one last query when the data exists of first call and professional call.. Will it be always after First call or it can be before then that as well??

Saurabh...

Yes, rows with First Call will display before rows with Professional Services.

Thank you so much!  Saved me hours!!!

You are welcome...Always Happy to Help.. :-)

Saurabh...