Pulling values from a concatenated string based on a set of conditions

Hi I posted dummy data showing Division, month, closures, and BK Number. What I am trying to do is the following:

If Division is "Midwest", Month is "01" and closures is "4" then show 4 bk numbers. If Division is "Midwest", Month is "02" and closures is "2" then show 2 bk numbers excluding those that were already used for month 01.

Dummy Data.xlsx

Help

Accepted answers

DataNath

If you try and open the .yxzp then it should unzip the individual components, as I've had to package it to include the macro I built as it's a dependency.

Failing that, you can just plug the macro in yourself if that isn't working. Just right click the canvas and Insert > Macros and navigate to where it's saved.

Individual workbook and macro attached.

BK Numbers by region and month.yxmd

Concat BK check.yxmc

All comments

DataNath

Hey @christopherKiener12, was just about to reply to your other thread. This is what I've got so far. I don't have a load of time to look through and sense check it so I'll leave that to you. From a quick glance it looks to perform what you've asked.

BK Numbers by region and month.yxzp

IraWatt

Hey @christopherKiener12,

What is the logic we are following here? Is it the number of closures show that many BK numbers except for the ones in the previous month?

I had a look and there are no rows with Division is "Midwest", Month is "02" and closures is "2"

christopherKiener12

Hey, this is perfect! Is there any way you can share this as a yxmd file for some reason it is not opening for me.

Luke_C

Hi @christopherKiener12

I think this is a duplicate thread (this isn't a small ask so be patient). Here's a solution, I made a batch macro (to process each division) with an iterative macro to allocate the BK numbers.

BK Split.yxzp

DataNath

If you try and open the .yxzp then it should unzip the individual components, as I've had to package it to include the macro I built as it's a dependency.

Failing that, you can just plug the macro in yourself if that isn't working. Just right click the canvas and Insert > Macros and navigate to where it's saved.

Individual workbook and macro attached.

BK Numbers by region and month.yxmd

Concat BK check.yxmc

Luke_C

@DataNath Clever with the record ID use! I went the iterative approach

DataNath

Cheers @Luke_C - I was thinking of taking an iterative approach within the batch but didn't have a load of time. Nice to see the logic behind it and always love different ways of reaching an end goal in Alteryx!

@christopherKiener12 I have actually just noticed an issue and made a quick tweak - I was filtering out 0 closures early in the macro and so in theory, this would drop BKs which could otherwise be used in future months. I've filtered out later just before the BKs are assigned which should remedy this. Updated versions attached. Also, I'd recommend taking a look over Luke's response and marking it as a solution - this wasn't a quick task and they've also delivered what you asked!

BK Numbers by region and month v2.yxmd

Concat BK check v2.yxmc

Quick Links

This months top contributors

AkimasaKajitani 388

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335