•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Need to create a diagram of connected points
Our take
Creating a visual representation of connected points in a spreadsheet can enhance your understanding of relationships between sites. In your data, each site is represented in the top row and column A, with TRUE indicating a connection, FALSE for no connection, and "Diag Fail" for testing failures. Your goal is to generate an image with dots for each site and lines connecting them based on these relationships.
Spreadsheet looks basically like this: Across the top row and down column A are sites., in order. If there is a connection between "Site A" and "Site B" the "Site A" row will have TRUE in the "Site B" column, and vice versa. If there is no connection it will be "FALSE," if we failed to connect to "Site A" to test the connection to "Site B" that column will say "Diag Fail," and finally "Site A" to "Site A" has the value "Self".
What I would like to generate is a series of dots in an image for each of the sites, and then a line that connects them if there is a connection. I'm pretty sure I've seen this done in a pivot table in the past, but it's been too long.
Edit: Stole u/bradland 's example screenshot. This is what my source data looks like
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Dynamic network graph built entirely in Excel using VBA and Pivot TablesPeople tend to underestimate the power of Excel. This dynamic network graph was built entirely in Excel using VBA to visualize customer relationships, counterparty exposure, and transaction flows. All nodes and connection paths are pre-drawn Excel shapes that are dynamically shown, hidden, resized, and weighted through VBA logic based on filters and relationship strength. Link to the Excel file and Pivot Table explanation: Gitlab Repo - Excel Network Graph Dashboard Data Sense - Excel Network Graph Dashboard submitted by /u/nlomb [link] [comments]
- Need better way to organise spreadsheethttps://preview.redd.it/oiuewnp0pstg1.png?width=599&format=png&auto=webp&s=d34be7eaeb702d5ded087538a2292a57b295dd65 I am currently keeping track of visiting football stadiums (often called the 72 or the 92) as you can see on the attached image, the shear amount of anfield visits leads to many duplicate entries and am wondering if anyone has a suggestion on tidying this up, so that anfield appears once, but perhaps links through so that i can see all visits there will also likely be more stadiums in the future that will have multiple duplicates. *several columns to the right cropped off because of personal data. submitted by /u/EpilepticFlshbng [link] [comments]
- Cell merging / formatting formulasThis might be an odd one. I'm not that skilled with excel as my use of in within my job is pretty limited. However, I tend to use this template my predecessor made to summarize data from our program. Works well, just a simple ='SHEET 1'!A1 for all cells. The first two images give an example. After the data is ported, I have to get rid of the zeros between the data and write system names. When it comes to pasting it on letters, the names are bolded, upped a font size, and two of the cells are merged (3rd image) This gets a bit tedious as the lists can get pretty long so I've been trying to figure out how to streamline it on my own. My idea has so far has been to have a separate cell detect when I'm finished adding my data and then format the aforementioned cells (4th image). For the life of me, just can't figure out how to write a formula to do it. What I would need is for the formula to detect a 1 (could be anything) in cell G10. It would then check for any blank cells in columns A and B. Once found, it would merge & center, bold the text, increase the font size, and align right. Is this only possible with a macro? I've been unable to find any formulas that could accomplish this. https://preview.redd.it/853mpr4jspog1.png?width=788&format=png&auto=webp&s=943a34a57f1d7c7f88256dd89b81b9c6fc301e34 https://preview.redd.it/e3w6ms4jspog1.png?width=453&format=png&auto=webp&s=9b189fa77d6638b627f8676dad60bc148753617c https://preview.redd.it/msh6ys4jspog1.png?width=411&format=png&auto=webp&s=d5f5025b315f24fd5f3a64c6e07e019abd3a77ab https://preview.redd.it/7j66tt4jspog1.png?width=936&format=png&auto=webp&s=1faa4df12ee74892f5fa9dc27ac95621280cf3c5 submitted by /u/Extension_Train9093 [link] [comments]
- Making a fillable form with dropdowns and sections that can be added and moved easily**SEE ATTACHED IMAGE** I am attempting to improve this spreadsheet. I have a decent amount of experience with Excel but I am unfamiliar with the terminology require to describe the exact functions I am attempting to implement. The function of the sheet is to log various statuses and information pertaining to ladders and their inspection status. Each workbook has a header section and a footer section to diffentiate between clients. The sections that detail the individual ladders need to be moveable / re-order more easily, and also be able to insert a blank ladder section for any new ladders that come into service. The reason excel was selected is for access reasons. All of our clients use excel, but if there is an objectively better tool for the job that is part of the Office suite that people can use via the web or with their Office licence then I will consider that. Thank you to everyone who is able to provide some insight into this. https://preview.redd.it/7ln3oocpcjvg1.png?width=1405&format=png&auto=webp&s=584525d55bd27779bd56a9d746b5dcc6dd913c70 submitted by /u/WillowDime [link] [comments]