Need Help with Macro to Clean Up Many : 1 Relationship to Unique Identifier

Hello,

I have a spreadsheet that has a many : 1 relationship with a unique identifier in 2 columns where this should not be the case. I need for the macro to check the date column for the latest date, then check the unique ID column to see if the values are all the same. If they are the same, then take the value in the content title column and populate all records relating to the unique ID with the content title value and the author value in the record with the latest date.

From what I can I tell, I need to replace <>0 with code that says "are all the same".

Sub CleanTitles()
Dim j As Long, k As Long
Undo
Workbooks("Contributor-Dashboard-Dummy-Data.xlsm").Sheets("Data").Activate
k = Range("a6").End(xlDown).Row
Maximum = Application.WorksheetFunction.Max(Worksheets("SFDCData").Range("E2:E84627"))
For j = 2 To k
'MsgBox j
If Cells(j, "E") = Maximum And Cells(j, "A") <> 0 Then Cells(j, "B").Value.Copy _
    Workbooks("Contributor-Dashboard.xlsm").Worksheets("SFDCData").Cells(j, "B").End(xlUp).Offset(1, 0)
If Cells(j, "C") = 0 And Cells(j, "A") <> 0 Then Cells(j, "B").Value.Copy _
    Worksheets("Contributor-Dashboard.xlsm").Worksheets("SFDCData").Cells(j, "B").End(xlUp).Offset(1, 0)
Next j
With Range("b3").Values.Copy
Workbooks("Contributor-Dashboard.xlsm").Worksheets("SFDCData").Range("B1").PasteSpecial
End With
Workbooks("Contributor-Dashboard.xlsm").Worksheets("SFDCData").UsedRange.Columns.AutoFit
End Sub
Sub CleanTitles()
Dim j As Long, k As Long
Undo
Workbooks("Contributor-Dashboard.xlsm").Sheets("SFDCData").Activate
k = Range("a6").End(xlDown).Row
Maximum = Application.WorksheetFunction.Max(Worksheets("SFDCData").Range("E2:E84627"))
For j = 2 To k
'MsgBox j
If Cells(j, "E") = Maximum And Cells(j, "A") <> 0 Then Cells(j, "B").Value.Copy _
    Workbooks("Contributor-Dashboard.xlsm").Worksheets("SFDCData").Cells(j, "B").End(xlUp).Offset(1, 0)
If Cells(j, "C") = 0 And Cells(j, "A") <> 0 Then Cells(j, "B").Value.Copy _
    Worksheets("Contributor-Dashboard.xlsm").Worksheets("SFDCData").Cells(j, "B").End(xlUp).Offset(1, 0)
Next j
With Range("b3").Values.Copy
Workbooks("Contributor-Dashboard.xlsm").Worksheets("SFDCData").Range("B1").PasteSpecial
End With
Workbooks("Contributor-Dashboard.xlsm").Worksheets("SFDCData").UsedRange.Columns.AutoFit
End Sub

Open in new window

Contributor-Dashboard-Dummy-Data.xlsm
calyx_terenAsked:
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.

gowflowCommented:
Sorry it is not clear what you want can you please explain better ? I don't see this code in the workbook.
gowflow
0
calyx_terenAuthor Commented:
Hi gowflow. I added the macro to the workbook. The macro is supposed to clean data from the DummyData sheet. The DummyData sheet lists every download and view for a file. Each file has a unique identifier that is called Content ID. There should be 1 title and 1 author for each Content ID. Currently, there are several titles and authors for each Content ID. I need for the macro to find the latest date that a file was viewed or downloaded. Then, use the title and author from that record  to update all records that relate to the unique Content ID. Let me know if this makes more sense.
0
gowflowCommented:
Sorry for the delay in talking this one but when I looked up again the file that you posted didn't have the macro that you first posted should it be there ??? the macro refers to a sheet called SFDCData that does not even exist in the attached workbook. So what is the story with the macro you posted ?

Anyway I am reading again your last post and to be very honest your have a sheet called DummyData that have data in all of the columns A to I
I see content ID in Col A so If I understand this well you want to do the following:

Will take the first record which is content ID 06980000000PgoSAAS
The macro should lookup all the rows that have this content ID and find the latest date in Col E which is 11/18/2014 in row 22 and then take the following:
Content Title in cell B22 Article 42
Author in cell C22 Author 31
and then copy these 2 fields respectively in rows 2 to 21
and then move to the next content ID and do the same etc.. till end of file.

Is this what you want ?
gowflow
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

calyx_terenAuthor Commented:
Hi gowflow, yes, that's exactly what I need. Sorry for the confusion. I have a spreadsheet with the real data and then a copy with dummy data so when I go between them, I need to edit the macro because the workbook and spreadsheet names are hard coded. I edited the macro again to work with the dummy data workbook.
Contributor-Dashboard-Dummy-Data.xlsm
0
gowflowCommented:
So let me get this correctly.

You believe that your macro that you posted is fine and the only thing that is needed is the <>0 like in your original post ? Is that what you want or we need to provide you with a macro that will do the job ?

As if it is your macro then my first look at it I see that you have the value of Maximum takes the latest date of ALL DATES in the workbook is this correct ? As my understanding was to take the latest date per content ID block and not the one from the whole workbook.

gowflow
0
calyx_terenAuthor Commented:
Hi gowflow. I don't believe that my macro is fine. It is my attempt at writing a macro that will do what you described in an earlier post. I am open to new code. Your understanding is correct: take the latest date per content ID block and not the one from the whole workbook. Thanks for your help.
0
gowflowCommented:
ok clear then I will work on it. and sorry you had to request attention but was caught in several issue these last 2 days and you got delayed. I will try to expedite as much as possible.
Tks your patience.
gowflow
0
calyx_terenAuthor Commented:
Not at all! I appreciate your help. Thanks again.
0
gowflowCommented:
ok one more question
Any problem if we sort this worksheet ? or you need the data to stay exactly as it is ?
gowflow
0
calyx_terenAuthor Commented:
No problems for sorting the worksheet. I do have some formulas in place, but they should be unaffected.
0
gowflowCommented:
for sure formulas will not be affected it is only if you have other macros that rely on a certain sort then the outcome of these macros will be erroneous. And also for sure if you are used to looking at the data a certain way then by sorting it will change this view. My intention is to sort by Content ID and then by date reverse (like latest to oldest) this should facilitate the looping.

gowflow
0
calyx_terenAuthor Commented:
That sounds fine, thanks.
0
gowflowCommented:
ok here it is this this the macro give it a try and it is attached in the workbook. To activated simply activate the sheet you need to fix the titles for and from Macroes select CleanTitlesNew and run.

Sub CleanTitlesNew()
Dim WS As Worksheet
Dim I As Long, K As Long
Dim MaxRow As Long
Dim ID As String, sAuthor As String, sTitle As String
Dim Rng As Range

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

'---> Set Variables
Set WS = ActiveSheet
MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row

'---> Sort Worksheet basis Col A and Col E reverse
WS.Range("A1:I" & MaxRow).Sort Key1:=WS.Range("A1"), order1:=xlAscending, key2:=WS.Range("E1"), order2:=xlDescending, Header:=xlYes, MatchCase:=False

For I = 2 To MaxRow
    '---> Check is New Content ID
    If WS.Cells(I, "A") <> ID Then
        sTitle = WS.Cells(I, "B")
        sAuthor = WS.Cells(I, "C")
        WS.Range("A1").AutoFilter Field:=1, Criteria1:=WS.Cells(I, "A")
        Set Rng = WS.Range("A2:A" & MaxRow).SpecialCells(xlCellTypeVisible)
        Rng.Offset(, 1).Value = sTitle
        Rng.Offset(, 2).Value = sAuthor
        ID = WS.Cells(I, "A")
        I = I + Rng.Rows.Count - 1
        WS.ShowAllData
    End If
    
Next I

'---> Autofit All Columns
WS.UsedRange.EntireColumn.AutoFit

'---> Enable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

'---> Advise User
MsgBox ("Cleaning of Titles Completed.")

End Sub

Open in new window



gowflow
Contributor-Dashboard-Dummy-Data-V01.xls
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
calyx_terenAuthor Commented:
Hi gowflow,

Thank you for your help! The macro worked like magic on the dummy data spreadsheet. :) I am running it on the spreadsheet with the real data now, which is considerably longer. Thanks again!
0
gowflowCommented:
Yes when it is longer, it will take more time undoubtly but the way I coded it is to minimize looping and maximize speed.

Let me know if any problem.
gowflow
0
calyx_terenAuthor Commented:
Hi gowflow, yes, it did take longer, but did a great job when it finished. Thank you!
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.