• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating a Priority Table from multiple Tables [SOLVED]

N

NickyG

New Member
Hi All, I'm Nic a Civil Engineer looking at harnessing the mighty power of excel.


At current I'm looking to essentially create a Priority Table. In essence this is the background on the excel file.


Background:

Essentially as part of my works I complete many conceptual designs for works. As such I would like to create a summary table for which others can view and see which tasks or projects from multiple sheets are priority.


The Breakdown:


Essentially at the moment there are various small project components to my work, these include footpath design, signage implementation, Drainage Projects, Road Projects, Pedestrian Bridge Design etc

Each of these components are assigned a sheet with the following heading

Project Location, Project Description, Inception Date, Project Type (Be it maintenance or Capital Works), Project Cost, Priority Level,Priority Score (Which is a component of Priority Level Plus Additional Components) and Project Status, Project Comments.


What I would like:

Basically from those sheets I would like to create a summary sheet with a table which searches through the the various tables and identifies the highest priority projects (Say Top 20 Projects) and lists them in order by score. I guess you could call it a glorified to do list that others can look at and carry out the tasks.


At the moment I'm in the process of changing over a whole lot of data to give some uniformity to my tables and due to the sensitive nature of the projects I'll have to make some dummy projects for you as well.


Can you guys illustrate for me what I need to do?


To make it easier as suggested I'm using Excel 2007 & a skeleton sample of what I'm currently setting up can be found here https://www.dropbox.com/s/pln7w05ofs94bfy/Sample%20Sheet.xlsx
 
Hi Nic ,


What is needed is firstly some sample data , the more the better.


Since you have already explained what your objective is , once the data is available , work can start !


Narayan
 
Here is a link to a sample excel sheet with essentially is the skeleton which will form the greater works.


https://www.dropbox.com/s/pln7w05ofs94bfy/Sample%20Sheet.xlsx
 
Knowing what version of Excel you have would be good too. Since we're talking about multiple sheets here, then a solution could well involve some of the newer functionality in Excel, for instance tables (introduced in 2007) or PowerPivot (introduced in 2010).
 
Is a civil engineer like a really polite one? ;-)


Nice file layout, Nic. I'm thinking that one way to mash this data up is with a little SQL. Another is to use something called PowerPivot, which is a free add-in for Excel 2010 (and unfortunately only availble in Excel 2013 professional plus edition due to some strange marketing from Microsoft)


What version of Excel do you have? Obviously it's at least 2007 given the file type. Do you have PowerPivot addin installed? (If you're unsure on that last one, then the answer will be no)


Will people viewing the file have the same version of Excel as you?
 
Unfortunately Jeffrey I do not have that add-on installed. As this will be set up at work with the networking and power hungry admins (kidding) getting modifications done to a computer may not be the best option. Ive never set up an SQL server before so unsure on the process for such a thing in excel. Other people viewing the file will have the same version.


And to your first question.... Yes
 
Okay, rules PowerPivot out. Know anything about SQL? It's basically a simple language that most databases uses (and that Excel understands) that will let you mash up data from different tabs and return it as a table.


In this case it's going to need a little VBA programming to execute it, and also to make sure that you don't get any nasty things happening as a result of querying the file while it's still open (something called memory leak.)


Or maybe just a VBA approach will do. We could simply copy all those seperate tabs into your summary tab and rank them by priority.


A formula-based approach is probably not going to cut it.
 
I think that my idea to simply copy all those seperate tabs into your summary tab and rank them by priority will be the best. But I gotta get some shut-eye...just gone past midnight here.


I suspect that by the time I check this thread again, one of the ninjas would have whipped something up for ya. But if not, I'll have a crack tomorrow. Can't have any more delays on fixing those aussie footpaths, can we...
 
I know slim to very little about SQL.. Back when I was 16 i dabbled a little bit when I ran a website.. Thats it.. I'm no good at coding but if someone was able to set it up for me I'm sure i could rope learn enough to modify it when needed. i.e. if I add or change a row.. Essentially I will be the only person editing it but people will want to view the file on their computers and as a project progresses I'll change the status to completed.


Ohh that brings up an important point. If the Project Status is listed as Completed I would like the table to automatically exclude it from the list. In the future I might copy the summary table and modify it to also show a list of completed projects
 
Yes I'm only a graduate so at this stage my portfolio includes only minor works. Thanks for your help either way very much appreciated
 
Jeffrey, Do you require any further information from me regarding the issue? if so feel free to let me know. I really do appreciate the help.
 
Just need some time to whip up the code. But finding it hard to find a spare moment today. If someone else out there can take a look, feel free. Otherwise I'll get to it by monday at the latest.
 
Hi Nic. Just sat down to take a look at this. Strikes me that it would be easier and more robust if rather than bringing three tables into one, you just had all tables in one in the first place, and just add another column 'Type' that you can filter on, that has either 'DDA Priorities', 'Footpaths' or 'Other'.


Your thoughts?


That's not to say we can't do it the other way. But sometimes the simplest solution is best.
 
Failing that, here's some code that does what you want. Basically it wipes the Summary table clean any time the Summary tab is activated, then copies the info from the other tabs into the blank Summary table, and sorts them.


This relies on you naming your tables Summary, DDA_Priorities, Footpaths, and Other. And note that it completely wipes the data that was previously in the Summary table. So any changes to the summary table won't stick. But you shouldn't be making any changes within the summary table in any case.

[pre]
Code: 
Private Sub Worksheet_Activate()

Dim lo As ListObject
Dim lr As ListRow
Dim rngSource As Range
Dim rngDest As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With

Set lo = [Summary].ListObject
With lo

' Clear the Summary table without actually deleting it
On Error Resume Next
.DataBodyRange.Rows.Delete
On Error GoTo 0

'Copy the first source table into the Summary table.
Set rngSource = [DDA_Priorities].ListObject.DataBodyRange
Set rngDest = .HeaderRowRange.Offset(1)
rngDest.Resize(rngSource.Rows.Count).Value = rngSource.Value

'Copy the other tables in. Note that we can just add a
' listrow, and then copy the data there.
' But we couldn't do that above, because there weren't any
' listrows to add another one to, which is why I used the
' Set rngDest = .HeaderRowRange.Offset(1) line.

Set rngSource = [Footpaths].ListObject.DataBodyRange
Set rngDest = .ListRows.Add.Range
rngDest.Resize(rngSource.Rows.Count).Value = rngSource.Value

Set rngSource = [Other].ListObject.DataBodyRange
Set rngDest = .ListRows.Add.Range
rngDest.Resize(rngSource.Rows.Count).Value = rngSource.Value

With .Sort
.SortFields.Clear
.SortFields. _
Add Key:=Range("Summary[[#All],[Priority]]"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub
[/pre]

And here's the link to a document with this already done. Note I added an 'Other' and 'Summary' table.

https://www.dropbox.com/s/ejv28zpgxk6slf0/Amalgamate%20Tables%20to%20Summary%20Table%2020130622.xlsb
 
Hi Jeffrey Sorry For the late reply.. Its working great I implimented it today. There is one problem i've found with it. I've modified it a bit to include other things however when the priority or summary table amalgamates all the data it doesnt copy over any hyperlinks that where in the source tables. What modification to the code would I have to make to include any formatting or hyperlinks in the summary table?


I tried using a method myself but got to many errors haha..
 
Hi Nic. Can't see any hyperlinks in the sample file I played around with. Where abouts are they?


Hey, before I forget, those tabs with hyperlinks are cool. Where did you learn that trick?
 
Hi Jeffrey,


The tab trick is pretty simple they are just text boxes shaped and filled with a colour. Then the current tab is the same except you insert a line up the top to represent the current tab and change the background colour to white. The rest is simply cell fill colouring to give the illusion of a page. I believe I found where someone used something similar to that for a costing sheet.. I liked the idea and created a similar one


We have changed the setup of the excel a bit since we implemented it. So essentially Before the details column there is a reference column which contains a reference number and when clicked opens up a third party program based off a hyperlink. We also have another column after the costing tab which does a similar thing except brings up the engineering plans for the project. I'm away from work today and will not be back until tomorrow. Would you be able to just add a hyperlink into the file you already possess then just let me know the code on how you did it. I've rope learned a bit of your coding to the extent that which I can add pages and have the table update. Failing that I'll upload a sample document tomorrow.
 
Hyperlinks are easy enough. Say you add new columns to the source tables called 'Reference' and another called 'Plans', and put some hyperlinks in them. Add these variables under the existing DIM statements at the top:

[pre]
Code: 
Dim wks As Worksheet
Dim cell As Range
Add this bit of code between the two End With statements down the bottom (i.e. the two End With statements that occur just before the 'With Application' bit:

End With

End With


    With Application


For Each cell In Union(.ListColumns("Reference").DataBodyRange, .ListColumns("Plans").DataBodyRange)
If Left(cell, 4) = "http" Then wks.Hyperlinks.Add Anchor:=cell, Address:=cell.Value, TextToDisplay:=cell.Value
Next
[/pre]
And that's it! Let me know how you get on.
 
Hi Jeffrey, Thanks for the prompt reply


I've added that exactly into the file however no luck, I may have changed something in the code or misrepresented my changed and thus it hasnt worked. So i've got below a sample file with all the changes made to help you.


https://www.dropbox.com/s/lu5lm7objved6ey/Amalgamate%20Tables%20to%20Summary%20Table%2020130622%281%29.xlsm


Thank you very much for your help
 
Ahh. My code needed the actual text of the hyperlink stored in the column to display. i.e. if you had http://linked.cada.asd/ in the column instead of the text REQ-111-111-111 wrapped in a hyperlink, my approach would work.


So there's two approaches - one is to add extra columns that contain the hyperlink text, and the other is to write some more VBA. Should be simple enough, although I'm supposted to be doing my tax return today. And I work for the tax man, so filing late is not good.


Will have a quick play.
 
Okay, very simple to rectify. Originally my code was copying only the values of the table, and bypassing the clipboard while it did it:

rngDest.Resize(rngSource.Rows.Count).Value = rngSource.Value


This is the most efficient way to copy data from one place to another. But it only copies values and ignores formats, hyperlinks etc.


So rather do it efficiently, we'll use the slightly less efficient Copy/Paste approach:

rngSource.Copy rngDest


Just make those simple tweaks to my original code, and you should be good to go.
 
You must log in or register to reply here.
Back
Top