Solved

Extract data from Excel Report

Posted on 2013-12-11
16
216 Views
Last Modified: 2013-12-11
Attached I have file with contains a tab called "Monthly_Forecast_Report 1" this is how the data is exported to our system, but I need to create a macro that would only bring in the totals for each of the Stamp Projects. In the Combined Report tab I placed the information the way it woul look like once the macro runs. Clearly this example only has two records but in Reallit this report can have many more records in the Monthly_forecast_Report worksheet. How can I accomplish this??
0
Comment
Question by:joeserrone
  • 7
  • 7
  • 2
16 Comments
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39711719
No attachment!!
0
 

Author Comment

by:joeserrone
ID: 39711743
Sorry I forgot to attach the file
Forecast-Report.xls
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39711860
With addition of a helper column on Source sheet, you can do it fairly simply.

Insert column to left of Stamp Project column and insert the following, starting in A4 and copied down as far as required.

=IF(B4="",A3,B4)

This populates the blank cells for the Project Title.

Then on the Combined sheet, the following formula in column B and copied right and down.

=SUMIFS('Monthly_Forecast_Report 1 '!F$2:F$1000,'Monthly_Forecast_Report 1 '!$A$2:$A$1000,$A2,'Monthly_Forecast_Report 1 '!$C$2:$C$1000,"Total")

Assumes same Stamp Project title in cell A2.

Thanks
Rob H
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39711866
Copy of workings attached.
Copy-of-Forecast-Report.xls
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39711870
Ah, just realised by .xls file format that you might be working in Excel 2003; I have used SUMIFS that only started in Excel 2007.

Will have to work differently if that is the case.
0
 

Author Comment

by:joeserrone
ID: 39711883
Thanks Rob... That is a great solution, the only thing is that the user might need to copy the STAMP Project names in the Combined Report under Cell A1, is there a way to get the unique list of Stamp Projects from the helper colum in the other worksheet and automatically compile it in column A of the Combined Report?
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39711894
There certainly is, strangely enough part of another answer I suggested earlier today; spooky.

Give me two minutes and I will update the sheet.
0
 

Author Comment

by:joeserrone
ID: 39711921
Thank you!!! that would be fantastic, can't wait to see the solution
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
ID: 39711934
Attached.

I assume my comment earlier about being in Excel 2003 wasn't applicable as I have added further features that might not be in Excel 2003, can't remember eventhough it is only 2 months since I was working in an environment with 2003 + 2007 Compatibility Pack.

Combined report now has formulae to pull Stamp Project Names from Column A of Monthly Report, has option for Grand Total on Combined Report and Conditional Formatting to add Bold and Borders to Grand Total line.

Hope this is OK.

Thanks
Rob H
Copy-of-Forecast-Report.xls
0
 

Author Comment

by:joeserrone
ID: 39711943
I actually have Excel 2010
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39711971
Here is a macro to do the same

Sub combinereport()
    Dim sws As Worksheet
    Dim tws As Worksheet
    Dim startcel As Range
    Dim newrow As Range
    Dim srcrow As Long
    Set sws = ActiveSheet
    Set tws = Sheets.Add
    Set startcel = sws.Range("A2")
    Do While startcel <> ""
        Set newrow = tws.Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow
        srcrow = startcel.Row + startcel.MergeArea.Rows.Count - IIf(startcel.Row = 2, 2, 1)
        newrow.Cells(1, 1).Value = startcel.Value
        newrow.Range("B1").Resize(, 12).Value = sws.Cells(srcrow, 5).Resize(, 12).Value
        If startcel.Row = 2 Then newrow.Range("B1").Resize(, 12).Font.Bold = True
        Set startcel = startcel.Offset(1)
    Loop
    tws.Rows(1).Delete
    tws.Cells.EntireColumn.AutoFit
End Sub

Open in new window

0
 

Author Comment

by:joeserrone
ID: 39711989
Beautiful!!! the only thing I notices is when I remove the old information from the Monthly_Forecast_Report worksheet and load it with the actual records the formulas in column A
=IF(B4="",A3,B4) end up looking like this... =IF(#REF!="",A3,#REF!)

Sometimes a report can have 10 recoreds but other times it can have 100 and the user would have to somehow delete the old ones in order to put the new set of records in
0
 

Author Comment

by:joeserrone
ID: 39712073
Love the Code Saqibh, If I wanted to add colum "D" funding source to my code how it would look like?
Sub combinereport()
    Dim sws As Worksheet
    Dim tws As Worksheet
    Dim startcel As Range
    Dim newrow As Range
    Dim srcrow As Long
    Set sws = ActiveSheet
    Set tws = Sheets.Add
    Set startcel = sws.Range("A2")
    Do While startcel <> ""
        Set newrow = tws.Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow
        srcrow = startcel.Row + startcel.MergeArea.Rows.Count - IIf(startcel.Row = 2, 2, 1)
        newrow.Cells(1, 1).Value = startcel.Value
        newrow.Range("B1").Resize(, 12).Value = sws.Cells(srcrow, 5).Resize(, 12).Value
        If startcel.Row = 2 Then newrow.Range("B1").Resize(, 12).Font.Bold = True
        Set startcel = startcel.Offset(1)
    Loop
    tws.Rows(1).Delete
    tws.Cells.EntireColumn.AutoFit
End Sub 

Open in new window

0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 250 total points
ID: 39712130
Sub combinereport()
    Dim sws As Worksheet
    Dim tws As Worksheet
    Dim startcel As Range
    Dim newrow As Range
    Dim srcrow As Long
    Set sws = ActiveSheet
    Set tws = Sheets.Add
    Set startcel = sws.Range("A2")
    Do While startcel <> ""
        Set newrow = tws.Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow
        srcrow = startcel.Row + startcel.MergeArea.Rows.Count - IIf(startcel.Row = 2, 2, 1)
        newrow.Cells(1, 1).Value = startcel.Value
        newrow.Cells(1, 2).Value = startcel.Offset(, 3).Value
        newrow.Range("C1").Resize(, 12).Value = sws.Cells(srcrow, 5).Resize(, 12).Value
        If startcel.Row = 2 Then newrow.Range("C1").Resize(, 12).Font.Bold = True
        Set startcel = startcel.Offset(1)
    Loop
    tws.Rows(1).Delete
    tws.Cells.EntireColumn.AutoFit
End Sub

Open in new window

0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39712187
I suspect you are removing old data by deleting the columns rather than just the data.

Formula is referring to column B which you are removing so it doesn't know where to refer so gives #Ref.
0
 

Author Closing Comment

by:joeserrone
ID: 39712324
Both great options!!! I appreciate all your help
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now