This is a question about coding and excel Are there any experts out there?

[G+_Harry Henze]

This is a question about coding and excel. Are there any experts out there?

[G+_Tony Martin]

Expert? Hmmm I would not say that I am an expert in anything. However, I am sure that we can help you out. Whats the question?

[G+_Cory Albrecht]

Just ask your question straight off. Don't ask if you can ask.

[G+_Harry Henze]

I am trying to create a way to find a path through a entity relationship diagram. I set up each worksheet with a separate table from the diagram. Each table it links to is below that information. I hyperlinked the tables/ worksheets to match the diagram. Can I use excel and coding to put in a start table and an end table and get all the available paths as a result?

[G+_Mr covert]

Harry Henze yep. Excel supports scripting. Write the code to explore the paths and save the results

[G+_Harry Henze]

That's where I need help. I know a little bit of coding but not much. I have been looking online but it hasn't given me the ideas to do this yet

[G+_Ben Reese]

I was gonna say... "Write the code..." doesn't tell a whole lot.

 

I guess I'm having a hard time wrapping my head around what you want to do though. You've drawn a diagram (like flowchart?) and you want Excel to find all paths through that diagram?

[G+_Harry Henze]

It's a like flowchart on the wall. I took each box and created a worksheet. Each path from a box was created as a hyperlink in the worksheet. Does that help?

[G+_Harry Henze]

This is my wall

[G+_Tony Martin]

This is a complicated question. I do not know much about excel scripting but I have seen things like this done in many different programing languages. I guess the best place to start would be with a search algorithm, something like a DFS algorithm or one of the many other algorithms out there.

[G+_Ben Reese]

Ah, I see what you're doing. I should have known by "Entity Relationship Diagram". You've obviously put a lot of work into this then.

 

You'll definitely have to use VBA if you want to do this in Excel, but it might be easier to accomplish in SQL. Even then...

 

So if each table joins to 2 other tables and you have 100 tables (each joining to an average of 2 other tables), that would be 2^100 possibilities. Excel will hold about 1,000,000 rows per sheet which doesn't come close to accomplishing this. (and maybe my math is off too and I am looking at this all wrong)

 

I can definitely see why you'd want this though.

[G+_Harry Henze]

How would I set this up with sql?

[G+_Harry Henze]

I have crystal reports pointed at this database. That's the whole reason for this to start. Is it possible to do in crystal reports?

[G+_Jason Perry]

Wow! My version of "programming" in excel is nesting functions inside functions, and creating formulas to spit out what I am after.

 

I am officially interested and confused about your problem.

[G+_Ben Reese]

I'm not familiar enough with crystal reports to say for sure. I think you're still looking at 1.2676506e+30 possibilities.

 

To do it in SQL, I think all the tables, columns in one table then the foreign keys in another table. Something like....

 

Tab_col

----------------

tab_col_id int

tab_nm varchar(200)

col_nm varchar(200)

 

col_ref

----------------

col_ref_id int

tab_col_left int

tab_col_right int

 

Loading those tables might take a bit and VB script would come in handy to combine everything into one sheet that could then be loaded into SQL.

[G+_Harry Henze]

Definitely seems out of my range. Thank you all very much for the feedback

[G+_Tony Martin]

I am not seeing this as being as difficult as it is being made out to be. Unless I am misunderstanding something you essentially just want to go from point (a) to point (b) and know which entities were touched along the way. In its simplest form this is a search function in which you track each point that is looked at in the search until the search finds point (b) which would provide a path through the diagram. Like I said I have never seen this done in excel but I have seen it done in other programming languages.

 

For example using DFS / DFT:

 

geeksforgeeks.org - Print all paths from a given source to a destination - GeeksforGeeks

[G+_Swiftbird 0]

If the pict was your problem I have no idea what you're doing. I consider myself an Excel power user. Even so i probably use no more than 10-15% of Excel's power.

 

There is considerable power in programming within cells. This is referred to as algebraic functions started by entering "=" in a cell. Everything after the equals sign is evaluated as an algebraic equation including referenced cells. Syntax is important. There are more excel functions available than anyone could possibly use. Besides equations cells can obtain Labels and numbers. Labels are text and start with a " or a '. Starting a cell with a non-number or math operator will start a label or text cell.

 

Once you get proficient in General Excel tasks you can at least play around with Visual Basic for Applications(VBA). There is a VBA "Dashboard" like ribbon called "Developer". Find it in 'Options' just above 'Exit' at bottom of "File" menu.

 

There are some differences between Excel versions. I'm using Office 2010. Access VBA in the 'View' ribbon, Macro button. Choose 'View' to see macros already recorded or manually entered. One needs to figure out the difference between 'Absolute and Relative' addressing. Hint: Its similar to using '$' in cell addresses so when you copy cells they maintain same address no matter where they are pasted. Absolute $A$1, relative A1, the $ sign means a specific cell while without means row and columns are calculated based on original location when copying-pasting.

 

Recording macros: The advantage here is that you can record keystrokes of things you need to do. Learn the syntax via the recording process. Edit macros after recording for new uses. Macros are proceedures that can be called in a program or other macro you create and edit. As you record moving around your spreadsheet to copy and or paste data you are recording these movements as well. Just edit the stuff you don't need out.

 

If the Excel data structures don't meet your needs one can write a VBA program that reads data from an Excel spreadsheet and loads it into a VBA array where you have normal programming controls. Data can then be written back to the spreadsheet.

 

Other hints:

Word processing, 'Fill, Justify' will take a long piece of text in one cell and even it out in the area selected prior to 'Fill Justify'. Excel has functions for examining, picking apart, and reassembling Strings. Try in a cell entering "qwer"&" "&"asdf".

 

 

swiftbird0

 

 

 

 

 

 

 

[G+_Benjamin Webb]

Expert in VBA and SQL here. Looks like basically you have a state machine here and you have your beginning state and final state and want to know all possible paths in between. This is similar to how a brute force algorithm for a GPS works. Caculate all possible routes but it then would sort for the shortest (which you don't have to do).

 

I think Ben Reese suggestion of mapping it in sql is the best in terms of wrapping your head around it and implementing it. This problem is too large for excel VBA. Companies often bring in AI or neuron computers/algorithms for such tasks as general computing algorithms suck at this sort of thing and use a ton of resources.

 

Use the best tool for the problem and implement in a way that is easiest to understand.

[G+_Tony Martin]

I still think yous are making this to hard. So I will provide one more example of DFT (depth first traversal) using recursive queries but this can be done just as easy or even easier with just a few lines of code in any programming language.

 

So, here is a complete SQL query retrieving all paths from the node with id=1 to the node with id=6:

 

WITH RECURSIVE search_path (path_ids, length, is_visited) AS

(

SELECT

ARRAY[node_id, destination_node_id],

link_length,

node_id = destination_node_id

FROM

node_links_view

 

UNION ALL

SELECT

path_ids || d.destination_node_id,

f.length + d.link_length,

d.destination_node_id = ANY(f.path_ids)

FROM

node_links_view d,

search_path f

WHERE

f.path_ids[array_length(path_ids, 1)] = d.node_id

AND NOT f.is_visited

)

SELECT * FROM search_path

WHERE path_ids[1] = 1 AND path_ids[array_length(path_ids, 1)] = 6

ORDER BY length;

 

As a result we get all paths from node 1 to node 6 ordered by total path length:

 

path_ids | length | is_visited

---------------+--------+------------

{1,3,2,5,6} | 140 | f

{1,2,5,6} | 150 | f

{1,3,4,5,6} | 150 | f

{1,3,4,6} | 190 | f

{1,2,3,4,5,6} | 200 | f

{1,2,3,4,6} | 240 | f

(6 rows)

 

The shortest path is the first one, so we could add a LIMIT clause to get just one result.

 

academy.vertabelo.com - Recursive Queries

[G+_Mike B]

I don't think Excel is the appropriate tool for this.

Maybe google ERD diagramming tools if it's one of their features. If you'd like a database implementation, try these out: stackoverflow.com - What are the options for storing hierarchical data in a relational database?

Just insert the data in a database as described (you may need to Google them separately, if you don't get enough info from this), and run the appropriate queries against them.

[G+_Benjamin Webb]

Tony Martin?? This will work assuming his diagram does not loop back as this may loop indefintely. This may be like a GPS problem where the driver can U turn at any node. State machines are used for control as you can ESTOP a control system and return to a failsafe ground state.

 

I figure a modification of this where the previous decision at the node that caused a loop is removed.

 

Excel connecting through ODBC to a SQL database via VBA should handle this. There may be faster connection methods but plenty of documentation for ODBC.

[G+_Ben Reese]

I've had decent luck with ODBC connection and Excel. Far from perfect and not preferred typically, but it works. The query language for Excel ODBC is more like Access than SQL Server, btw.

 

The GPS analogy is pretty good. Perhaps it would be better to map out a few known static routes. Chances are you can break your diagram into logical groups of tables. As Benjamin Webb? pointed out, things probably can loop back - sometimes that's actually required to make the joins you need.

 

The other thing to consider is that some of your paths will require multiple columns to actually make the join.

 

All I have to say is you'll have an awesome tool once you get it working!