We help IT Professionals succeed at work.

Add New Data

Billa7
Billa7 asked
on
Need Experts help to add additional function in the attached code. The current code able to copy only unique title from Data sheet (Column D) and at the same time count number of time the data are appeared in Data sheet (according to date) at Total sheet. Now, the function that required is to be able to update new data (new date/month) from Data sheet to Total without overriding the existing data, and update the total accordingly. Hope Experts could help me to create this function.

 

Sub MakeSummaryTable()
   Dim lngLastRowData As Long
   Dim lngLastRowTotals As Long
   
   ' clear existing data
   Sheets("Total").UsedRange.Offset(2).ClearContents
   
   ' Get unique list
   With Sheets("Data")
      ' get last used row in col D (Titles)
      lngLastRowData = .Cells(.Rows.Count, "D").End(xlUp).Row
      .Range("D1:D" & lngLastRowData).AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Sheets("Total").Range("A2"), Unique:=True
   End With
   
   With Sheets("Total")
      ' get last used row in col A (Titles)
      lngLastRowTotals = .Cells(.Rows.Count, "A").End(xlUp).Row
      ' sort Titles
      .Range("A2", .Cells(lngLastRowTotals, "A")).Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
      
       .Range("B3:AF" & lngLastRowTotals).FormulaR1C1 = _
        "=SUMPRODUCT((DAY(Data!R2C1:R" & lngLastRowData & "C1)=R2C)*(Data!R2C4:R" & lngLastRowData & "C4=RC1))"
   End With
End Sub

Open in new window

Count-V1.xls
Comment
Watch Question

NorieAnalyst Assistant

Commented:
So the next lot of data would be for the 24th?

Where will that be added in the data sheet?

Under the existing data for the 23rd or replacing that data.

Author

Commented:
Hi Imnorie,

>So the next lot of data would be for the 24th?
Yes
Where will that be added in the data sheet?
Yes
>Under the existing data for the 23rd or replacing that data.
Replacing the data with a new data
Analyst Assistant
Commented:
So what happens with the existing data on the Totals sheet?

Especially if the unique title list is different for the new data.

Author

Commented:

Hi Imnorie,

We need to keep the old data, if the new data having a similar title with the old data (column A – Total sheet), we need to omit this title, however, total count for that title need to be captured according to date. E.g “Title A” already exist in Total sheet(column A), the new data set (24th – date) has “TitleA”, therefore we need to omit this title at Column A but update total number of this title at Data sheet in column 24(Date).  

Author

Commented:
Hi Imnorie,

Please let me know if you need more info from my side.
NorieAnalyst Assistant

Commented:
So the list of titles doesn't change?

Author

Commented:
Hi Imnorie,

The old information remained, update a new new entry (Title - Data sheet) and subsequently captured number of total of the title respectively.  
NorieAnalyst Assistant

Commented:
Sorry I'm not explaining this well.

What if on the 24th there are entries for Title1 and Title2 so the list of titles on the Totals sheet just has those 2 titles.

Then on the 25th there are entries for Title1, Title3 and Title4.

What happens there?

Are Title3 and Title4 added to the list?

By the way this could be avoided by simply sticking the new data below the existing data.

In fact if you did that you could get the totals using a pivot table.

Author

Commented:
Hi Imnorie,

From the sample given, since the old data has Title 1 and 2, we just need to copy Title 3 and 4. Even though we not adding the Title 1 and 2 for the 25th, we still need to capture total of this title at 25th (date). In my original Data source (actual), I have many similar titles were repeated in Data sheet and If we not imposing the filter function at the beginning this will create a huge complication. Hope you will consider to create this feature.        
NorieAnalyst Assistant

Commented:
I can't quite see how this can be done, there's something missing.

I think what's missing is the part that deals with the new data.

If you just replace the new data with the old data the existing formulas will either cause errors or give the wrong results.

You would have to replace the formulas with values before you import the new data.

I've no idea how you are doing the import but I think it might be important.

By the way, have you consider the idea of appending the new data rather than overwriting the existing data?

Author

Commented:
Hi Imnorie,

I do understand the complexity of getting the desired result. As for now,  I'm going to cut and paste the data from my original source in this workbook.

If we were able to maintain only unique title are appearing on Total sheet, retaining the old data in Data sheet not become a big issue. However, I have one concern, let say I'm importing the original source in this workbook, does this slow down the data process?    
NorieAnalyst Assistant

Commented:
Sorry I don't quite understand.

Are you going to import all the data?

If you are it shouldn't have a great impact on this, don't know about any other parts of the process though.

You could always try a pivot table.

It might not give you exactly what you are looking for but it wil give you the results which you can then copy and do whatver you want with.

Author

Commented:
Hi Imnorie,

The data was actually generated based on date. In other words, I will import each date's date separately.

If pivot table able to provide the result that I want, I should go for it.
NorieAnalyst Assistant

Commented:
Yes it should but you might need to refresh source every time you add new data.

That can be done manually but it's sometimes tricky to get the range(s) right so things can be set up to do it automatically.

eg dynamic named range.

If you want attach a file with some sample data with more dates - you could probably just use the data you have but change the dates in column A.

Author

Commented:
Hi,

Thanks for your concern, managed to fix this.