Excel Macro to move data from column into rows

I have a report that can export duplicate data with most column values being exactly the same excluding four columns (L,M,N and U).  I need a macro that will find the duplicates, remove columns L,M,N, and U to the one of the rows that has the duplicated information and add the data to empty columns (V,W,X,Y).  After doing that, I need for the other duplicated row that doesn't have to new columns to be deleted.  

Sorry, I don't even know where to begin!!
csjobergAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
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...
0
csjobergAuthor Commented:
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.
0
csjobergAuthor Commented:
oops, don't think I uploaded on the last comment.  Attached.
Screen-Shot-2015-09-02-at-4.34.16-PM.png
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Saurabh Singh TeotiaCommented:
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...
0
csjobergAuthor Commented:
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
0
Saurabh Singh TeotiaCommented:
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

Open in new window


Your workbook...

Saurabh...
Macro-Example.xlsm
0
csjobergAuthor Commented:
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
0
Saurabh Singh TeotiaCommented:
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...
0
csjobergAuthor Commented:
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.
0
Saurabh Singh TeotiaCommented:
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...
0
csjobergAuthor Commented:
Yes, rows with First Call will display before rows with Professional Services.
0
Saurabh Singh TeotiaCommented:
Their you go use this code...

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

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

    Range("V2:X" & lrow).Formula = "=IF(AND(COUNTIF($A$2:$A$" & lrow & ",$A2)>1,COUNTIF($A$1:$A2,$A2)=1),LOOKUP(2,1/(($A$2:$A$" & lrow & "=$A2)),L$2:L$" & lrow & "),IF(COUNTIF($A$2:$A$" & lrow & ",$A2)>1,NA(),""""))"
    Range("V2:X" & lrow).Value = Range("V2:X" & lrow).Value
    Set rng = Range("V2:X" & lrow).SpecialCells(xlCellTypeConstants, 16)

    If Not rng Is Nothing Then rng.EntireRow.Delete

    MsgBox "Done"

End Sub

Open in new window


Sheet2 contains data post macro has run...

Saurabh...
Copy-of-macro-example-2-1.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
csjobergAuthor Commented:
Thank you so much!  Saved me hours!!!
0
Saurabh Singh TeotiaCommented:
You are welcome...Always Happy to Help.. :-)

Saurabh...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.