Solved

Create sheet based on multiple values

Posted on 2014-01-28
35
133 Views
Last Modified: 2014-11-03
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

In Last report please note that Cycle index column can contain multiple values.

Thanks
Mas
0
Comment
Question by:mas1963
  • 18
  • 14
  • +1
35 Comments
 
LVL 80

Expert Comment

by:byundt
ID: 39816950
Depending on the version of Excel 2010 that you have, you may be able to download the PowerQuery add-in for it. I used PowerQuery in a 3 step process to create the desired table. You can download PowerQuery add-in from Microsoft:
http://office.microsoft.com/en-us/excel/download-microsoft-power-query-for-excel-FX104018616.aspx "Download Microsoft Power Query for Excel"

The three steps were:
1. Start with the Control worksheet table, then merge the Risk worksheet table to it. Then merge the Cycle worksheet table to that.
2. Expand the two Table columns
3. Delete the duplicate columns

The PowerQuery "formulas" for those three steps are:
Source = Table.AddJoinColumn(Merge1,{"Cycle Index", "Cycle Name"},()=>tbCycle,{"Cycle Index", "Cycle Name"},"NewColumn.1"),
    #"Expand NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"Cycle Index", "Cycle Name", "Risk Index", "Risk Number"}, {"NewColumn.Cycle Index", "NewColumn.Cycle Name", "NewColumn.Risk Index", "NewColumn.Risk Number"}),
    #"Expand NewColumn.1" = Table.ExpandTableColumn(#"Expand NewColumn", "NewColumn.1", {"Cycle Index", "Cycle Name"}, {"NewColumn.1.Cycle Index", "NewColumn.1.Cycle Name"}),
    RemovedColumns = Table.RemoveColumns(#"Expand NewColumn.1",{"NewColumn.Cycle Name", "NewColumn.Cycle Index", "NewColumn.1.Cycle Name", "NewColumn.1.Cycle Index"})

Open in new window

CreateRelationalTableQ28350514.xlsm
0
 

Author Comment

by:mas1963
ID: 39827663
Hi byundt,
Thanks for reply. I have few questions.

1) For each user to run the report do they need to down load PowerQuery add-in in their computer or only just developer will need it develop the report.


2) Can we threee steps in following order

Start with the Cycle worksheet table, then merge the Risk worksheet table to it. Then merge control worksheet table to that.
2. Expand the two Table columns
3. we don't need to delete duplate columns as we need duplicate item.
if we have duplicate value in Control or Risk then another line same entry should be added in Cycle column
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

Thanks
Mas
0
 
LVL 80

Expert Comment

by:byundt
ID: 39827972
Mas,
To run the report, I believe that you need PowerQuery. I haven't tried it, but it should be a matter of refreshing rather than creating the report from scratch.

In step 3, I asked you to delete duplicate columns, not duplicate rows. I was fully aware that you needed the duplicate values in your table, but you probably don't want two columns for Control Index.

To be honest, this question was the first time that I have ever used PowerQuery. That said, I've attended a number of presentations at Microsoft, and was quite impressed by its capabilities.

When I was playing around with PowerQuery, one of the problems I ran into was getting the data to line up correctly. The instructions I provided work for your sample data, but may not work as well on different data. The issue I was trying to resolve was getting the "duplicate" items in the table, so I started with the table that had all the duplicates. If one of the other tables has a different assortment of "duplicate" items, I'm not sure if the sequence I suggested would work as desired.

If PowerQuery is not something that you can roll out to your users, you are left with two possibilities:
A.  Construct your report in a database app like Access, then export it to Excel
B.  Write a macro to build your report in Excel. I imagine it might work like this:
     1.  Append your three input tables, one after the other, taking care to align the columns
     2.  Use INDEX & MATCH formulas in each of the blank cells of the appended table to bring back the required data from the original tables.
     3.  Use Advanced Filter (or other means) to identify rows that are completely identical to a previous row. Delete these duplicates.
     4.  Sort the remaining rows as required
0
 

Author Comment

by:mas1963
ID: 39836051
Hi byundt,

I tried to install Powerquery in windows 7 Enterprise 64 bit with Server pack 1, but requirement is Internet Explorer 9 but in my company we using  Internet Explorer 8, so I was not able to install Powerquery. Any suggestion or alternative if not using Powerquery.


Thanks
Mas
0
 
LVL 80

Expert Comment

by:byundt
ID: 39837305
Mas,
I started to write a macro to produce the desired report, but soon realized that I need a sample workbook that shows the data layout. The sample workbook needs to exactly mirror the real workbook in header labels and location of data on each worksheet. It does not need to contain any more data than you have already posted. Could you provide one?

I also need to know if the real data on worksheet Control contains 100% of the records. If not, can I confidently match the Control and Risk index numbers? In other words, does US-C4-C2 always match US-C4-R2?

Brad
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39838938
QUestion was reassked here
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28357372.html

THe requested file is attached in that question

Because both questions have valid comments, I have reduced the points to 250.

mlmcc
TA
0
 

Author Comment

by:mas1963
ID: 39839132
Hi byundt,

Attached you will find sample worksheet.  Sheets cycle, Control and Risk contain data and micro  should create the report that should be like in Reference Chart Sheet.

Please let me know if you have any question.

Thanks
FCR-Report-Reference-Chart.xlsm
0
 
LVL 80

Expert Comment

by:byundt
ID: 39840767
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 Comment

by:mas1963
ID: 39840936
Hi byundt,

Thanks for your code. I tested it working beautifully. I have one more question.

For sample report, I added Cycle, control, and Risk Sheet, but in actual report I have few more sheet i.e Handoff and Common  and data is almost same as in Control and Risk, then what i need to change in code to accommodate those two sheet.

Thanks
Masood
0
 
LVL 80

Expert Comment

by:byundt
ID: 39840954
Masood,
You will be needing to revise the sub to handle the additional worksheets. It is written in modular fashion, so you may be able to make the changes by mimicking what I had done.

The function probably will not need changes, as it is designed to identify the columns containing the unique key, and to return data from an arbitrary selection of columns from tb2. You just need to modify the statement calling the function.

Even if I tried to suggest the required changes to add Handoff and Common, you would still need to edit the code to get the right columns entered into your report.

If you aren't able to make the required changes, please post a new sample workbook with the additional worksheets, data and desired report appearance. I don't think it will take me long to change the code.

I would also suggest that you close either this question or http:/Q_28357372.html and let the discussion continue in the thread that remains open.

Brad
0
 

Author Comment

by:mas1963
ID: 39840967
Hi Brad,
I will appreciate if you can add code in attached sheet as it is data from real import.

I added your code but when I ran i got error.


Actually tomorrow I have demo for this report.

Thanks for all your help.

Mas
Reference-Chart-Using-Expert.xls
0
 

Author Comment

by:mas1963
ID: 39840993
Hi Brad,
I attached sheet I created Reference Chart sheet and copy data of Cycle Sheet to first two column of Reference chart then I used vlookup to get the data from Control and other sheets but I was not able to get the multiple entries of control and Risk and other. I used the button to copy the whole reference chart to create another sheet name Reference Chart - Report to filter data base on drop down list of Cycle selection to get multiple values of Control and Risk and other.

Thanks
Mas
0
 

Author Comment

by:mas1963
ID: 39841002
Hi Brad,
Please let me know if you can help for this short notice.

Thanks
Mas
0
 
LVL 80

Expert Comment

by:byundt
ID: 39841007
Masood,
I'm working on it now.

I assume the flow is Cycle-->Control-->Risk-->Handoff-->Common. If so, how do I link the data on Handoff to a column on a previous table? And likewise, from Common to a column on a preceding table?

Brad
0
 

Author Comment

by:mas1963
ID: 39841017
Brad,

In Notes application I have for each control type i.e Control, Risk, Handoff and common control I have two form one is like control and other is Controldraft, so same is for Risk and RiskDraft and so on.

When I exported in excel I created for each type of control I created a sheet.

Cycle is main form and contain relationship with sub form which are control, controldraft, Risk, Riskdraft and so on.

Flow is Cycle->Control>controlDraft>Risk>RiskDraf>Handoff>HandoffDraft>Common>CommonDraft


let me know if I explain enough

thanks
MAS
0
 
LVL 80

Expert Comment

by:byundt
ID: 39841046
At each stage in the workflow for the ReportBuilder sub, I need to know:
1.  What is first table?
2.  What is second table?
3.  What is the header label that I use to link data in the two tables?
4.  What column numbers from the second table do I append to the first table when producing the report?

Example:
To append data from Control onto Cycle, the answers to the questions are:
1. Cycle table
2.  Control table
3.  "Cycle Index"
4.  3, 4, 5 & 6

I do this with the statements:
Set tb1 = Worksheets("Cycle").Range("A1").CurrentRegion
Set tb2 = Worksheets("Control").Range("A1").CurrentRegion
With ws
    v = OneToMany(tb1, tb2, "Cycle Index", 3, 4, 5, 6)
    If IsEmpty(v) Then Exit Sub
    .Range("A1").Resize(UBound(v), UBound(v, 2)).Value = v
    Set tbResults = .Range("A1").CurrentRegion

Open in new window


All that said, I'm not seeing why both worksheets Control and Control Draft are in the workflow. They have the same column header labels, but there is no other data in Control Draft. If Control Draft is supposed to contain all the data from Control, but with perhaps some updates, then I should be using Control Draft instead of Control.

The same issues apply to the other pairs of worksheets (Risk and Risk Draft, Handoff and Handoff Draft, Common and Common Draft).
Reference-Chart-Using-ExpertQ283.xlsm
0
 

Author Comment

by:mas1963
ID: 39841056
Hi Brad,

I deleted all draft sheets. Now what sheet are we only need report based on them.

Thanks
Reference-Chart-Using-Expert.xls
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 80

Expert Comment

by:byundt
ID: 39841057
One more assumption that I need to mention: the code requires that the data be sorted by the unique key. That assumption isn't currently met on worksheets Control and Risk.

I can add code to perform those sorts. Would I need to undo the sort after my macro runs?
0
 

Author Comment

by:mas1963
ID: 39841059
Hi Brad,

What is first table?
2.  What is second table?

2nd table> Control
3rd> Risk
4th> Handoff
5ht > common

3.  What is the header label that I use to link data in the two tables?

Cycle Index

4.  What column numbers from the second table do I append to the first table when producing the report?


From the 1st table only use Cycle index and Cycle name and from all table use there column names

thanks
Mas
0
 

Author Comment

by:mas1963
ID: 39841078
its good to sort and if not difficult you can do that.

For tomorrow I just need a working report, then I will discuss with you later on to make it properly work.

Thanks
Mas
0
 
LVL 80

Expert Comment

by:byundt
ID: 39841094
Masood,
See if the attached workbook is getting close to what you need. It includes the sorting plus all the data on your Cycle, Control, Risk, Handoff and Common worksheets.

I'm hoping the workbook is close enough, as it is getting quite late in the day for me. I'll revisit the question tomorrow morning.

Brad

Sub ReportBuilder()
Dim tb1 As Range, tb2 As Range, tb3 As Range, tb4 As Range, tb5 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
Set tb4 = Worksheets("Handoff").Range("A1").CurrentRegion
Set tb5 = Worksheets("Common").Range("A1").CurrentRegion
With ws
    v = OneToMany(tb1, tb2, "Cycle Index", 3, 4, 5, 6)
    If IsEmpty(v) Then Exit Sub
    .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, 5, 6, 7)
    If IsEmpty(v) Then Exit Sub
    .Range("A1").Resize(UBound(v), UBound(v, 2)).Value = v
    Set tbResults = .Range("A1").CurrentRegion
    
    Set v = Nothing
    v = OneToMany(tbResults, tb4, "Cycle Index", 2, 3, 4, 5)
    If IsEmpty(v) Then Exit Sub
    .Range("A1").Resize(UBound(v), UBound(v, 2)).Value = v
    Set tbResults = .Range("A1").CurrentRegion
    
    Set v = Nothing
    v = OneToMany(tbResults, tb5, "Cycle Index", 2, 3, 4, 5)
    If IsEmpty(v) Then Exit Sub
    .Range("A1").Resize(UBound(v), UBound(v, 2)).Value = v
    Set tbResults = .Range("A1").CurrentRegion
End With
End Sub

Private 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, k As Long, n As Long, n1 As Long, n3 As Long, nCols1 As Long, nCols3 As Long
Dim ii As Variant, jKey1 As Variant, jKey2 As Variant, vtb1 As Variant, vtb2 As Variant, v As Variant, vv As Variant, vResults As Variant
n1 = tb1.Rows.Count
nCols1 = tb1.Columns.Count
nCols3 = nCols1 + UBound(AddCol) + 1
jKey1 = Application.Match(UniqueKey, tb1.Rows(1), 0)
jKey2 = Application.Match(UniqueKey, tb2.Rows(1), 0)
If IsError(jKey1) Or IsError(jKey2) Then Exit Function

SortTable tb1, CLng(jKey1)
SortTable tb2, CLng(jKey2)
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 = Application.Match(vtb1(i, jKey1), tb2.Columns(jKey2), 0)
    'ii = 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

Private Sub SortTable(rg As Range, SortColumn As Long)
Dim rgSort As Range
With rg.Worksheet
    Set rgSort = rg.Columns(SortColumn)
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=rgSort, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
        .SetRange rg
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With
End Sub

Open in new window

Reference-Chart-Using-Expert.xlsm
0
 

Author Comment

by:mas1963
ID: 39841107
Thanks Brad for all your help.

We are getting closer with all your help.

Yes, It's getting late. I will discuss with you tomorrow.

Thanks
Masood
0
 

Author Comment

by:mas1963
ID: 39841849
Hi Brad,
Good morning. Let me know  when you have some time today.

I did some testing of last version of report you sent me. Few issues I will discuss with you.

Thanks
MAS
0
 
LVL 80

Expert Comment

by:byundt
ID: 39841911
Mas,
What are the issues?
Brad
0
 

Author Comment

by:mas1963
ID: 39842630
Brad,

I removed two sheets i.e Reference Chart and Reference Chart- Report as your code is doing that, I have one more question,
Can we create Report sheet from Column 5  and column A2 can we add Cycle index and in Column B3 can we add a drop down list which show all Cycle Index, so when user select any Cycle index in list box all relevant information will show in report section.

I attached your worked report for demo.

Thanks
MAS
Reference-Chart-Using-Expert-Wor.xlsm
0
 
LVL 80

Expert Comment

by:byundt
ID: 39843129
MAS,
The requested changes involve just the ReportBuilder sub. Modifications include:
1.  Start the report in cell A5 instead of A1
2.  Put "Cycle Index:" in A2:B2 with a light blue highlight
3.  Make the header row bold font with salmon highlight
4.  Add an AutoFilter for the first column
5.  Mimic the column widths on the reference report
6.  Turn on Freeze Pane so you can always see column A and row 5

Brad

Sub ReportBuilder()
Dim celHome As Range, tb1 As Range, tb2 As Range, tb3 As Range, tb4 As Range, tb5 As Range, tbResults As Range
Dim i As Long
Dim v As Variant
Dim ws As Worksheet
Application.ScreenUpdating = False
Set celHome = ActiveCell
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
Set tb4 = Worksheets("Handoff").Range("A1").CurrentRegion
Set tb5 = Worksheets("Common").Range("A1").CurrentRegion

With ws
    .Range("A2:B2").Interior.ColorIndex = 24
    .Range("A2").Value = "Cycle Index:"
    For Each v In Array(12.86, 49.86, 15.29, 14.71, 11.86, 17.29, 11.71, 8.43, 8.86, 24, 40.86, 15.29, 43.14, 13.86, 17.29, 25.57, 12, 17.86, 22)
        i = i + 1
        .Columns(i).ColumnWidth = v
    Next
    
    With .Range("A5")       'Top left cell of report goes here
        v = OneToMany(tb1, tb2, "Cycle Index", 3, 4, 5, 6)
        If IsEmpty(v) Then Exit Sub
        .Resize(UBound(v), UBound(v, 2)).Value = v
        Set tbResults = .CurrentRegion
        
        Set v = Nothing
        v = OneToMany(tbResults, tb3, "Cycle Index", 3, 4, 5, 6, 7)
        If IsEmpty(v) Then Exit Sub
        .Resize(UBound(v), UBound(v, 2)).Value = v
        Set tbResults = .CurrentRegion
        
        Set v = Nothing
        v = OneToMany(tbResults, tb4, "Cycle Index", 2, 3, 4, 5)
        If IsEmpty(v) Then Exit Sub
        .Resize(UBound(v), UBound(v, 2)).Value = v
        Set tbResults = .CurrentRegion
        
        Set v = Nothing
        v = OneToMany(tbResults, tb5, "Cycle Index", 2, 3, 4, 5)
        If IsEmpty(v) Then Exit Sub
        .Resize(UBound(v), UBound(v, 2)).Value = v
        Set tbResults = .CurrentRegion
    
    End With
End With
    
Application.Goto ws.Range("B6")
ActiveWindow.FreezePanes = True
tbResults.Rows(1).Interior.ColorIndex = 19
tbResults.Rows(1).Font.Bold = True
tbResults.AutoFilter
tbResults.Cells(1, 1).AutoFilter
tbResults.Columns(1).Select
Selection.AutoFilter

Application.Goto celHome
End Sub

Open in new window

Reference-Chart-Using-Expert-Wor.xlsm
0
 

Author Comment

by:mas1963
ID: 39844950
Hi Brad,
Did you make the changes in your last attached report which I requested in my previous mail as follows

"Can we create Report sheet from Column 5  and column A2 can we add Cycle index and in Column B3 can we add a drop down list which show all Cycle Index, so when user select any Cycle index in list box all relevant information will show in report section."

Or you suggesting that I can make the change based on your suggestion in you previous mail.

Please let me know.

Thanks
Mas
0
 
LVL 80

Expert Comment

by:byundt
ID: 39844956
Mas,
I made the changes I thought that you wanted in my previous Comment, which includes both revised code and a sample workbook. If I misunderstood what you wanted, please advise.

Brad
0
 

Author Comment

by:mas1963
ID: 39845417
Brad,
From your last working report which you attached on 2014-02-07 at 13:37:02 ID: 39843129.

I need in Cell A2 heading Cycle Index and in Cell B2 i need a drop down list which shows values from Column A6 to end of column A and when user select a value from Cell B2 drop down list then all the values related to selected value in B2 display in Report section in same sheet from Row A6 to S6 then end of values related to B2 value.


Please let me know if you need more info.

Thanks
MAS
0
 
LVL 80

Expert Comment

by:byundt
ID: 39845433
I did include the requested dropdown and filtering feature, but it doesn't work quite the way you requested.

I put an AutoFilter in column A (dropdown in cell A5). If you click it, you should see the requested list of topics. If you check just one, it will filter the list as you requested. AutoFilter also allows you to choose more than one item, which I see as a nice feature.

Another way of doing it uses Advanced Filter. With that, you would put the dropdown list in cell A3 (under Cycle Index rather than to its right). A Worksheet_Change macro would then trap the user selection and perform the filtering. The required code would also include producing the list of unique items from Cycle Index column and storing it on another worksheet for a data validation dropdown.
0
 

Author Comment

by:mas1963
ID: 39845755
Brad,

In the attached report I added Micro name ReportBuilder which copy content of Report sheet and created another Sheet name Reference Chart and added Cell A2 heading Cycle Index and in Cell B2 i need a drop down list which shows values from Column A6 to end of column A and when user select value from B2 it shows the report.

I have couple of issues in this code and need your expertise

1) Drop down list in Column B2 is not showing unique values

2) when run the ReportBuilder not able to debug it.


Thanks
Mas
Reference-Chart-Using-Expert-Wor.xlsm
0
 
LVL 80

Accepted Solution

by:
byundt earned 250 total points
ID: 39846400
Mas,
To add code to a worksheet code pane using a macro, you need to set a reference (by checking its box in Tools...References menu item in VBA Editor) to Microsoft Visual Basic for Applications (VBA) Extensibility. You also need to check the box for "Trust access to the VBA project object model" in the File...Options...Trust Center...Trust Center Settings...Macro Settings menu item.

Each user must check the box for "Trust access to the VBA project object model". This setting is unchecked by default. If the user has a different version of Excel than you do, he may need also need to set the reference in the VBA Editor himself.

Because of the inconvenience of setting up each user so a macro can add code to a worksheet code pane, I decided to use a Workbook_SheetChange event sub instead. This sub runs whenever anything is changed on any worksheet in the workbook, so I added a test for the worksheet name. I also added a statement to display all the data in your Reference Chart worksheet if cell B2 is cleared by the user.

Because the Workbook_SheetChange sub would be triggered extensively when the ReportBuilder macro runs, I added statements to turn events off at the beginning of that macro, and then back on at the end.

I modified the CreateReport sub so it would use an existing Reference Chart worksheet if you have one, or create a new sheet if you don't. I used Advanced Filter to make a list of the unique items in column A, and store them in a hidden column to the right of your data. This hidden column is the source for your data validation dropdown in cell B2.

Brad

'This code must go in ThisWorkbook code pane. It won't work at all if installed anywhere else!
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Sh
    If .Name = "Reference Chart" Then
        If Not Intersect(.Range("B2"), Target) Is Nothing Then
            If Not .AutoFilter Is Nothing Then .Range("$A$1").AutoFilter
            If .Range("B2") <> "" Then
                .Range("$A$5").AutoFilter Field:=1, Criteria1:=.Range("B2")
            Else
                .Range("$A$5").AutoFilter Field:=1
            End If
        End If
    End If
End With
End Sub

Open in new window

Sub CreateReport()
Dim wsReport As Worksheet
Dim rg As Range

On Error Resume Next
Set wsReport = Worksheets("Reference Chart")
On Error GoTo 0

Application.EnableEvents = False
If wsReport Is Nothing Then
    Set wsReport = Worksheets("Report").Copy(After:=Worksheets(Worksheets.Count))
    wsReport.Name = "Reference Chart"
Else
    wsReport.Columns.Hidden = False
    Worksheets("Report").Cells.Copy wsReport.Cells(1, 1)
End If

With wsReport
    .Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("A2").Value = "Cycle Index"
    .Range("A2:B2").Interior.Color = 65535
    
    Set rg = .UsedRange
    Set rg = rg.Cells(1, 1).Offset(0, rg.Columns.Count + 1)
    .Range("A6").CurrentRegion.Columns(1).AdvancedFilter xlFilterCopy, , CopyToRange:=rg, Unique:=True
    Set rg = rg.CurrentRegion
    Set rg = rg.Offset(1, 0).Resize(rg.Rows.Count - 1)
    rg.EntireColumn.Hidden = True
    
    With .Range("B2").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & rg.Address
    End With
End With
Application.EnableEvents = True
End Sub

Open in new window

Sub ReportBuilder()
Dim celHome As Range, tb1 As Range, tb2 As Range, tb3 As Range, tb4 As Range, tb5 As Range, tbResults As Range
Dim i As Long
Dim v As Variant
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.EnableEvents = False
Set celHome = ActiveCell
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
Set tb4 = Worksheets("Handoff").Range("A1").CurrentRegion
Set tb5 = Worksheets("Common").Range("A1").CurrentRegion

With ws
    .Range("A2:B2").Interior.ColorIndex = 24
    .Range("A2").Value = "Cycle Index:"
    For Each v In Array(12.86, 49.86, 15.29, 14.71, 11.86, 17.29, 11.71, 8.43, 8.86, 24, 40.86, 15.29, 43.14, 13.86, 17.29, 25.57, 12, 17.86, 22)
        i = i + 1
        .Columns(i).ColumnWidth = v
    Next
    
    With .Range("A5")       'Top left cell of report goes here
        v = OneToMany(tb1, tb2, "Cycle Index", 3, 4, 5, 6)
        If IsEmpty(v) Then Exit Sub
        .Resize(UBound(v), UBound(v, 2)).Value = v
        Set tbResults = .CurrentRegion
        
        Set v = Nothing
        v = OneToMany(tbResults, tb3, "Cycle Index", 3, 4, 5, 6, 7)
        If IsEmpty(v) Then Exit Sub
        .Resize(UBound(v), UBound(v, 2)).Value = v
        Set tbResults = .CurrentRegion
        
        Set v = Nothing
        v = OneToMany(tbResults, tb4, "Cycle Index", 2, 3, 4, 5)
        If IsEmpty(v) Then Exit Sub
        .Resize(UBound(v), UBound(v, 2)).Value = v
        Set tbResults = .CurrentRegion
        
        Set v = Nothing
        v = OneToMany(tbResults, tb5, "Cycle Index", 2, 3, 4, 5)
        If IsEmpty(v) Then Exit Sub
        .Resize(UBound(v), UBound(v, 2)).Value = v
        Set tbResults = .CurrentRegion
    
    End With
End With
    
Application.Goto ws.Range("B6")
ActiveWindow.FreezePanes = True
tbResults.Rows(1).Interior.ColorIndex = 19
tbResults.Rows(1).Font.Bold = True
tbResults.AutoFilter
tbResults.Cells(1, 1).AutoFilter
tbResults.AutoFilter

Application.Goto celHome
Application.EnableEvents = True
End Sub

Open in new window

Reference-Chart-Using-Expert-Wor.xlsm
0
 

Author Comment

by:mas1963
ID: 39846475
Hi Brad,

I really appreciate your quick reply. Let me test it. I will get back to you.

Thanks and Regards
MAS
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40419088
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

746 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

12 Experts available now in Live!

Get 1:1 Help Now