Solved

Extract data from Excel Report

Posted on 2013-12-11
16
251 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

626 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