Solved

Creating Report by combing all Sheets in workbook

Posted on 2014-02-05
5
279 Views
Last Modified: 2014-02-06
Hi All,
I have sheet name Cycle has values as follows

Col Cycle Index      Col Cycle Name

US-C1                      Boli
US-C2                      Finance
US-C3                      Equity
US-C4                      Investment
US-C5                      Corporate
Another Sheet Control
Col Cycle Index        Col Cycle Name               Control Index   Control Number
US-C2                         Finance                           US-C2-C1          C1
US-C2                         Finance                           US-C2-C2          C2    
US-C4                         Investment                     US-C4-C1          C1
US-C4                         Investment                     US-C4-C2          C2
US-C4                          Investment                    US-C4-C3          C3
US-C5

Another Sheet Risk

Col Cycle Index        Col Cycle Name               Risk Index         Risk Number
US-C2                         Finance                           US-C2-R1           R1
US-C2                         Finance                           US-C2-R2           R2
US-C4                         Investment                     US-C4-R1           R1
US-C4                         Investment                     US-C4-R2           R2
US-C5                      Corporate                            US-C5-R1          Ri

Now I need create a Reference Chart report which shows following info

 
Cycle Index      Cycle Name            Control Index    Control Number  Risk Index Risk Number

US-C1                         Boli        
US-C2                         Finance               US-C2-C1          C1                      US-C2-R1           R1
US-C2                         Finance               US-C2-C2          C2                      US-C2-R2           R2
US-C4                         Investment         US-C4-C1          C1                      US-C4-R1           R1
US-C4                         Investment         US-C4-C2          C2                      US-C4-R2           R2
US-C5                          Corporate                                                              US-C5-R1           R1

I attached workbook, last sheet Reference Chart should show all the values from all other sheet, if Control or Risk contain multiple values then one more row will be added to show Cycle Index.

My company is using Internet explorer 8 with window 7 64 bit so I was not able to download and use Chartquery as suggested by another export while I asked same quesiton.

Thanks
Mas
FCR-Report-Reference-Chart.xlsm
0
Comment
Question by:mas1963
  • 2
5 Comments
 
LVL 19

Expert Comment

by:regmigrant
ID: 39838341
I took a look at this last night and would need some additional information:-

- how often do the Cycle/Control/Risks sheets change?
- can the reference sheet be recreated each time or do you expect a dynamic refresh each time a change is entered
- do you want this automated or are you happy to copy new information in each time
- is Excel your only option
- how many lines would you typically expect the sheets to run to
- do any of the lines 'expire' and if so do you handle that manually
- is the example complete or just a sample
- are you the only user who enters information
- can the format/layout be changed
- where does the data come from and is it entered automatically or do you re-type from another source
0
 

Author Comment

by:mas1963
ID: 39839228
Hi regmigrant,

- how often do the Cycle/Control/Risks sheets change?

I have script in Lotus Notes application send which send data to excel and created Cycle, Control and Risk sheet and it is quite often when ever user wants to create reports

- can the reference sheet be recreated each time or do you expect a dynamic refresh each time a change is entered

Each time when user create the report by sending data from Lotus Notes application Reference chart will be created based on data of Cycle, Control and Risk sheet. Each time report will be new. Previous report will not be used.

-do you want this automated or are you happy to copy new information in each time

Can be automated i.e once user send data from Lotus Notes and created excel worksheet which contain Cycle, Control and Risk then a micro in excel can create the Reference Chart.

- is Excel your only option

As each user has excel and once report created user can play with data.


- how many lines would you typically expect the sheets to run to

It is on demand basis, but around 20 user use this functionality and can create report any time.

- do any of the lines 'expire' and if so do you handle that manually

We are not updating any line of data. Once user send data from Lotus Notes a report generated then user play with that data and if user need to created another report then he can create new report and save it.

- is the example complete or just a sample
it is just a sample. Actual report contain hundred on line of data

- are you the only user who enters information
more than 20 user generate reports


- can the format/layout be changed
Format can be change


- where does the data come from and is it entered automatically or do you re-type from another source

Data exported from Lotus Notes application using the script.


Please let me know if you have more question.

Thanks
Mas
0
 
LVL 80

Accepted Solution

by:
byundt earned 250 total points
ID: 39840762
Here is a macro (with supporting function) to build the report for you.

As written, the macro adds a worksheet called Report and populates it with the requested report using the tables on worksheets Cycle, Control and Risk. I included the option to include rows in the report if there were no matching items in the Control or Risk tables (such as for Boli and Equity in your sample data). The code for this option (a single statement) is in function OneToMany and is indicated by a comment. The alternative choice (excluding such rows) is commented out.

Sub ReportBuilder()
Dim tb1 As Range, tb2 As Range, tb3 As Range, tbResults As Range
Dim v As Variant
Dim ws As Worksheet
Application.ScreenUpdating = False
On Error Resume Next
Set ws = Worksheets("Report")
On Error GoTo 0
If ws Is Nothing Then
    Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    ws.Name = "Report"
Else
    ws.Cells.ClearContents
End If

Set tb1 = Worksheets("Cycle").Range("A1").CurrentRegion
Set tb2 = Worksheets("Control").Range("A1").CurrentRegion
Set tb3 = Worksheets("Risk").Range("A1").CurrentRegion
With ws
    v = OneToMany(tb1, tb2, "Cycle Index", 3, 4)
    .Range("A1").Resize(UBound(v), UBound(v, 2)).Value = v
    Set tbResults = .Range("A1").CurrentRegion
    
    Set v = Nothing
    v = OneToMany(tbResults, tb3, "Cycle Index", 3, 4)
    .Range("A1").Resize(UBound(v), UBound(v, 2)).Value = v
End With
End Sub

Function OneToMany(tb1 As Range, tb2 As Range, UniqueKey As String, ParamArray AddCol() As Variant)
'tb1 and tb2 are tables that include a header row and is sorted by the UniqueKey column _
'The function adds data from tb2 to tb1 and returns the resulting table
'UniqueKey is the header label used to match data between tb1 and tb2
Dim i As Long, i3 As Long, j As Long, jj As Long, jKey1 As Long, jKey2 As Long, k As Long, _
    n As Long, n1 As Long, n2 As Long, n3 As Long, nCols1 As Long, nCols2 As Long, nCols3 As Long
Dim ii As Variant, vtb1 As Variant, vtb2 As Variant, v As Variant, vv As Variant, vResults As Variant
n1 = tb1.Rows.Count
n2 = tb2.Rows.Count
nCols1 = tb1.Columns.Count
nCols2 = tb2.Columns.Count
nCols3 = nCols1
For Each vv In AddCol
    nCols3 = nCols3 + 1
Next
For j = 1 To nCols1
    If tb1(1, j) = UniqueKey Then
        jKey1 = j
        Exit For
    End If
Next
For j = 1 To nCols2
    If tb2(1, j) = UniqueKey Then
        jKey2 = j
        Exit For
    End If
Next
If jKey1 = 0 Or jKey2 = 0 Then Exit Function

vtb1 = tb1.Value
vtb2 = tb2.Value
ReDim v(1 To n1)
For i = 1 To n1
    'v(i) = Application.CountIf(tb2.Columns(jKey2), tb1.Cells(i, jKey2))     'Exclude rows if no match in tb2
    v(i) = Application.Max(1, Application.CountIf(tb2.Columns(jKey2), tb1.Cells(i, jKey2)))     'Include rows even if no match in tb2
Next
n3 = Application.Sum(v)
ReDim vResults(1 To n3 + 1, 1 To nCols3)
For i = 1 To n1
    n = v(i)
    ii = IIf(i = 1, 1, Application.Match(vtb1(i, jKey1), Application.Index(vtb2, , jKey2), 0))
    For k = 1 To n
        For j = 1 To nCols1
            vResults(i3 + k, j) = vtb1(i, j)
        Next
        If Not IsError(ii) Then
            For j = nCols1 + 1 To nCols3
                jj = AddCol(j - nCols1 - 1)
                vResults(i3 + k, j) = vtb2(ii - 1 + k, jj)
            Next
        End If
    Next
    i3 = i3 + n
Next
OneToMany = vResults
End Function

Open in new window


Brad
FCR-Report-Reference-ChartQ28357.xlsm
0
 

Author Closing Comment

by:mas1963
ID: 39840969
very good solution
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This collection of functions covers all the normal rounding methods of just about any numeric value.
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

706 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

20 Experts available now in Live!

Get 1:1 Help Now