Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

Extract data from Excel Report

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
joeserrone
Asked:
joeserrone
  • 7
  • 7
  • 2
2 Solutions
 
Rob HensonIT & Database AssistantCommented:
No attachment!!
0
 
joeserroneAuthor Commented:
Sorry I forgot to attach the file
Forecast-Report.xls
0
 
Rob HensonIT & Database AssistantCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rob HensonIT & Database AssistantCommented:
Copy of workings attached.
Copy-of-Forecast-Report.xls
0
 
Rob HensonIT & Database AssistantCommented:
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
 
joeserroneAuthor Commented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
joeserroneAuthor Commented:
Thank you!!! that would be fantastic, can't wait to see the solution
0
 
Rob HensonIT & Database AssistantCommented:
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
 
joeserroneAuthor Commented:
I actually have Excel 2010
0
 
Saqib Husain, SyedEngineerCommented:
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
 
joeserroneAuthor Commented:
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
 
joeserroneAuthor Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
joeserroneAuthor Commented:
Both great options!!! I appreciate all your help
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now