Formula or Filtering Help?

Question

Hi,

I have duplicate rows where the id_field is the only difference between rows indicated by the final number in the string.

I have uploaded some dummy data to demonstrate what I am trying to do. I wish to change Sheet 1 into Sheet 2, but I'm not sure how to accomplish it.

I want to keep the original data with all the columns filled in, but also have it where only 1 field is marked. It's to create a file that I can input into another programme which will give me a set of results based on the fields that are marked. So I want to be able to see what it's like with a single input and all together, if that makes sense..

Thanks in advance for anyone that can enlighten me how to tackle this issue.

Book1.xlsx

BrandonB · Answer

I figured that the result would look more like this, but wasn't sure if its what you were going for. It basically looks at every combination of items where the Yes values can add up to the total number of toys. Workflow is attached.

Combinations.yxmd

BrandonB · Answer

@mike17 can you elaborate on what you mean by "but also have it where only 1 field is marked."

From the data I think I see how you are saying that an id_field with the same starting numbers but a different end number should be grouped together. For example

295020000129502000022950200003

These are all starting with the same beginning numbers but have 1, 2, and 3 at the end. What I'm not quite following is what logic you are using to determine if "Yes" should be turned into "None". Are you looking to see if you have 2 toys indicated for example and a "Yes" indicated on two columns in sheet one, that you want to show it could be all of the first Yes column, all of the second Yes column, or a Yes in both the first and second column because these are the different combinations that you could make out of a 2 toy set?