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

The microservice metamorphosis

How I learn new things as a Software Engineer

Learning Python — List

Making Instagram.com faster: Part 1

It Is Not Working

Ventilating Your Home Router-The Smart Way

Usage Guide:Quickly deploy an intelligent data platform with the One-stop AI development and…

Handling Downtime in Payments

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

Top Picks from Power Platform for 2022 First Release Wave

Troubleshooting using environment variables in Power Apps and Automate packed in Solution

Credit Checker: Best app to obtain instant credit monitoring service inside Salesforce

Provecho Vegan Cookbook Review