few hours with …Grasshopper and Excel

  1. All data which needs to be counted together are in column A
  2. Copy the whole list of data to a new cell F
  3. Select all data in the F column and use the function “Remove duplicates” to get unique values
  4. Use the function “COUNTIF” on unique values to get a count of each data
  1. Add block “Read Excel Sheet” to the canvas. This block will read an Excel file where is filled just column A with data that is necessary to count. You can use “Panel” to see what kinda a piece of information is coming out of blocks.
  2. As you will see, this block requires a few parameters to work. In this case, we will fill just two of them, so in front of the block add “Boolean Toggle” which works like a Button with True or False value. You need to switch it to True so it works. Another block which is necessary to add is “File Path”. This block will hold a reference to your previously saved Excel file. To add your Excel reference to the Grasshopper click the right mouse button on the “File path” and then click “Set One File Path”. Find your excel file and you will have referenced your Excel file. If you have some changes in the Excel file, then save your Excel file and in Grasshopper just toggle “Boolean toggle” to False and then back to True and everything will recalculate
  3. Add block “Create Set” and connect “Columns” output from “Read Excel Sheet”. This will create a new list with unique values (same as “Remove duplicates” in excel)
  4. The next step is to add the “Member Index” block to the canvas. In this block add “Columns” output to “Set” input and “Set” output from “Create Set” to “Member” input. This block can find all indexes of values you need to find in the “Index” output. But in this case, we will use other output “Count” which already counts together values with the same indexes
  5. If you need to know the result and that’s all then you are finished, but we can give a nice output to excel. To do that first we need to merge data together so it can be displayed in Excel. Add the “Merge” block to the canvas. Connect outputs “Set” from “Create Set” and “Count” from “Member Index”
  6. The last step is to add the “Write To Excel” block and fill it with four inputs. The first input is “Boolean Toggle”, you can add a new one or just use the old ones. Next, you will need two “Panel” blocks, in the first write “Result” and connect it to the “Excel Worksheet” (excel will have a new sheet with this name). In the other panel write “FALSE” and connect it in “Columns or Rows?” (this just gives information that you need to use vertical alignment). And the last thing to do is add “Results” output from “Merge” to “Data to write into Columns”. I strongly advise connecting “Boolean Toggle” as a false and when everything is ready change it to true. If the toggle is on true, then every time you do something with blocks you send new info to excel and if something is not finished you can get strange results
  1. Use function “Concatiante” with a comma between width and height to get both values in one cell, in this example it’s F
  2. Copy data from the column F to column H without formula (copy special — values)
  3. And then you can use the steps from the first situation
  1. First, we add the “Cull Index” with parameters “Row” from “Read Excel Sheet” and 0 to “Indicies”. This will remove A column from excel, where was written “DATA”. We don’t need this information for this situation so we get rid of it.
  2. Next, we add “List Item” to get B and C columns from excel. When zooming closer to “List Item” you will see + sign, click on it to get “+1” output in “List Item”
  3. And one more block that is necessary to add is “Concatenate”. Same as in excel — putting both dimensions in one cell with a comma between them
  1. Let’s start with reading and referencing excels files as in previous situations with “Read Excel Sheet”
  2. Add “Member Index” to find if ordered detail if found in stock. Next, add “List Length”. If detail from the order will be found, then the value will be 1 if not 0
  3. Add the “Large Than” block and let’s check if the value is larger than 0. Block will give back value True or False. We can add this information next to the ordered detail number to see what is in stock
  4. You can already see that the fourth position “Detail 105” is not in stock, but ok, let’s write this information to excel. Add the “Concatenate” block and give order details and True or False value and write to excel as we did in the previous examples

--

--

--

Writing a blog about learning and exploring new stuff just for fun.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

WCVB NewsCenter 5

Which are the best WordPress Plugins available

Snap, flatpak, appimage (arch update)

Getting Started with Dart on GCP

Video Streaming with JavaScript: the Easy Way

Troubleshooting Cloud Pak for Data using Diagnostics Data Viewer

React Native Environment Setup on Windows 10

Silly But Makes Sense: Dependency Inversion Principle

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
few hours with…

few hours with…

Writing a blog about learning and exploring new stuff just for fun.

More from Medium

Google Sheets vs Excel — Who Comes Out on Top?

Google Sheets vs Excel - Who Comes Out on Top?

Tired of Formatting Pivot Tables in Excel? Build an Add-in in 5 Minutes

Day 9: Drawing a Data Self-Portrait

My Working Order #1