Forum Discussion
Categorization of a large, dynamic data set
I have a challenge which I can't solve. Already tried multiple ways - makearray, byrow, etc. Nothing worked so far. Here is the challenge:
I have a large set of data (>20000 rows, dynamic). It holds a.o. segments and size of segment (a number). Let's assume segment is in array A1# and size of segment is in array B1#. In a different table (~20 rows), all the unique segments are listed (named "Segments"). Next to this segment list, I have organized data for 3 categories (small/medium/large) for each segment. Each segment will have its own data for what is defined as small/medium/large. As such, I have 3 columns next to the segment name that holds number values for size thresholds.
In the large table, I now want to categorize each segment. Every segment (A1#) has a size (B1#) which corresponds to a category (small/medium/large) -> I.e. it needs a lookup in the category table.
I would want the formula to result in a spilled array - given the data is dynamic in nature.
Thanks for your ideas!
Here's my suggestion for a small dynamic sample database. If this works as intended then it shouldn't be difficult adapting it to a large database.
- Amazing. This worked out just perfect! I wasn't aware of the MAP function - nice learning! Thanks for the help offered!
I have created 2 columns with same result. one with Formulas and second with Power Query.
Please check the attached file.
- Thanks a lot! Also this way it worked out quite nice! Thanks for your help!!
