Solved

Extract data from Excel Report

Posted on 2013-12-11
16
234 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 32

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 32

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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 32

Expert Comment

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

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 32

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 32

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 32

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

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…
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 …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 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