Solved

Extract data from Excel Report

Posted on 2013-12-11
16
240 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 33

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 33

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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 33

Expert Comment

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

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 33

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
 
LVL 33

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 33

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

790 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