Dynamic range

[Hamder83]

Hi 

How do I solve this:

lets say I have a post code mapping files.
1 - 100 - Zone 1 
101 - 200 - Zone 2 and so on.

Then I wanna add my input data that has a postalcode 75 - then it add's Zone 1 to that row.

Without having to do a join and then a filter... because i have millions of input rows and hundreds of mapping rows.

?

[apathetichell]

your data didn't post - you'd have to use it as a text input or use the export workflow to have your data post with your workflow - having said that, perhaps a TILE tool set to manual option - where you can then use your zone cutoffs in the manual option...

[DawnDuong]

Hi @Hamder83 

If your boundary conditions are fixed and known, you can used a Formula tool and if then elseif else endid syntax to get the additional column.

do you need the upper and lower boundaries to be dynamic inputs?

dawn 

[Hamder83]

 I will take a look into that, thanks 🙂 

[Hamder83]

Hi @DawnDuong , 

I would pref for it to be dynamic, since we are using a similar methodoligy for several mappings, weight and so on.
Which is not always equal in our input to our mapping tables.

So I was hoping to find something that was not hard coded into a formular.

[DawnDuong]

hi @Hamder83 

if you want the upper and lower boundaries to be set by a mapping file (ie not hardcoded in formula), you can consider an iterative macro that takes 2 inputs:

1) fixed input is your mapping range table (sorted in ascending order)

2) iterative input is your data with millions of rows (sorted in ascending order)

set iteration number = number of records in 1.

set the loop as follow:

A) for the 1st run, take the 1st row of the mapping table. For the 2nd run, take the 2nd row and so on.

B) append A to the data tables. This does not change the number of records.

C) those records that falls into the range (compare data vs the new appended field) > output 

D) those that dont then loop back to iterate.

You should be able to get the results in 1-2 min.

dawn 

[Hamder83]

Hi @DawnDuong 

If I ask nicely, would you mind trying to create a sample workflow? I have a hard time understand how to do this.

but if it acuatly works then it would make a several of my processes a lot more lean.

Thank you 

[DawnDuong · Accepted solution]

hi @Hamder83 

I do a simple mock-up workflow here. You can make tweaks to the tools upstreams of the filter if your exact data inputs are different. But the idea is the same as the schema I explained in the earlier reply.

Hope it helps. 

Dawn.

[Hamder83]

Hi @DawnDuong 

This is super smart, i have never seen the itteration function before. Ill try and play around with it, but that clearly works so i'll accept as solution 🙂 

Thank you very much

[DawnDuong]

Hi @Hamder83

glad that it works for you.

i learnt how to do macros by seeing the interactive lessons and the recorded videos. Highly recommend that you check them out if you work involves a lot of repetitive excel tasks.

Dawn