asked on

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

Jacques Geday

Sorry it is not clear what you want can you please explain better ? I don't see this code in the workbook.
gowflow

Calyx Teren

ASKER

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.

Jacques Geday

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

Calyx Teren

ASKER

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

Jacques Geday

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

Calyx Teren

ASKER

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.

Jacques Geday

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

Calyx Teren

ASKER

Not at all! I appreciate your help. Thanks again.

Jacques Geday

ok one more question
Any problem if we sort this worksheet ? or you need the data to stay exactly as it is ?
gowflow

Calyx Teren

ASKER

No problems for sorting the worksheet. I do have some formulas in place, but they should be unaffected.

Jacques Geday

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

Calyx Teren

ASKER

That sounds fine, thanks.

ASKER CERTIFIED SOLUTION

Jacques Geday

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Calyx Teren

ASKER

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!

Jacques Geday

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

Calyx Teren

ASKER

Hi gowflow, yes, it did take longer, but did a great job when it finished. Thank you!