count itmes in a column that is populated with multiple data

Hi Chris,

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

HI Andrée,

First I would like to wish you a Happy Easter Monday...

Thanks for the quick reaction, I really wasn't expcting this during the Easter weekend ... big thumbs up for that.

I shared a sheet called sample with you, but I also uploaded a screen shot of the file.

Thanks in advance and cheers

Chris

counting probabilities.PNG

Thanks and I wish you the same!

Happy to help!

I'll take a look and get back to you!

Best,

Andrée

Debbie Sawyer

Hi Guys

I thought this was an interesting scenario so I thought I'd also have a play. I managed to get to get the right answers, but there are a few hidden columns! (1 hidden column per machine type) but if that is feasible, then this works!

Happy to share the sheet and explain the formulas if required

Kind regards

Debbie Sawyer Consultant & Training Manager

debbie.sawyer@smarterbusinessprocesses.com

Machine Counting.jpg

Debbie Sawyer

Looking at your second requirement (>70%) this is going to be tricky as the Probabilities value is treating the number as text, therefore isolating the number from the text string then treating it as a numeric will take some thinking.

Isolating the number shouldn't be too hard if the numbers are always wrapped in parenthesis using a MID/FIND formula. Converting that into a useable number would be as simple as wrapping the formula in a VALUE function.

=VALUE(MID(Probabilities@row, FIND("(", Probabilities@row) + 1, FIND(")", Probabilities@row) - FIND("(", Probabilities@row) - 1))

The above will give you the first number. Numbers after that, you will need to start taking advantage of the start_position field built into the FIND function. To find the second number, you would use the above formula, but in each of the FIND functions, you would use

FIND(";", Probabilities@row)

in the start_position field.

=VALUE(MID(Probabilities@row, FIND("(", Probabilities@row, FIND(";", Probabilities@row)) + 1, FIND(")", Probabilities@row, FIND(";", Probabilities@row)) - FIND("(", Probabilities@row, FIND(";", Probabilities@row)) - 1))

Values after that only seem to get more complicated because of nesting FIND functions within start_position fields, but if you take your time and work on one piece at a time, it really should be more tedious that complicated.

The key is to remember that FIND returns a numeric value based on the position of the "text" within the text string of the cell.

Debbie Sawyer

Thank you Paul!

What a formula.. I like it!

I saw that you've got excellent answers from others!

Let me know if I can help with anything else!

Best,

Andrée

Happy to help. It's a little something I've come up with for parsing collected data that has a delimiter. I know that L@123 has another parsing system buried somewhere here in the Community too. If I come across it again, I'll try to remember to post a link here.

Hi Andrée,

I just came back from a business trip and saw your answer, and the other ones as well.

Thanks anyways ...

Cheers

Happy to (try and) help!

Best,

Andrée

Hi Debby,

Thanks for your contribution ... Would you be so kind to explain how you solved it ?

Thanks again ...

Hi Paul,

Thanks for this amazing formula ... you really helped me out!

@you and Debbie: where did you learn all this? Are there books or videos with these great ideas?

Thanks for helping!

Cheers

Chris