sample.PNG
Hi!
Let me ask for help. I would like to create a function based on IF statement (or possibly other) that returns categories such in red (in attachment) based on multiple conditions like shown below:
Please Login or Register to view this content.
Categories would be defined by populated cells.
The # of columns and # of categories would be variable.
Personally I'd build a Vlookup table that concatenates the various IF condition tests into a single cell, and in the adjacent column cell enter the result required, e.g.
ABC
So for instance in F2 enter "USADenvermildmedium" and in G2 "ABC
repeat for other conditions and in A2 enter
Formula:
Please Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Thanks Richard. The Only roadblock I hit with such VlookUp solution is when there are blank cells, which means for example "DEF" would be Londonmildhigh so country can be "any" in dataset.
By that means EnglandLondonmildhigh <> Londonmildhigh.
In that case make sure that you build in a space, or any character for that matter if a space exists. e.g.
So to create the first column of the lookup table enter
Formula:Please Login or Register to view this content.
assuming the 'rules matrix is in B1:Enn
Then the vlookup formula similarly becomes
Formula:Please Login or Register to view this content.
Hi Richard. Thanks. Issue is rules matrix is not fixed to B:E. Fields, number of fields and their order can be various for each user as they have custom categories. However field names in lookup reference are identical to f. names in data list. I first put the sample reference into formatted table (dynamic named range) which eases the vlookup for variable # of rows. Now the concatenation part is tricky. Probably a loop that goes through row and joins non-blank cells. Next to recognize which cell - fields were joined at every row for vlookup to find match. Unfortunately, I am clueless to put this into practise.
Sample.xlsm
In this file I managed to concatenate both values for categories and their respective field names, sheet LookUp reference.
However I cannot figure out how to concatenate / create a looked-up value on Data sheet, to match and find
corresponding Category on Lookup reference sheet.
Last edited by pulsar777; 11-29-2015 at 08:07 AM.
Sorry but your latest workbook isn't clear.
You imply that the permutation of fields varies but your original code was attempting to create a specific rule.
If the column B values on the lookup sheet are correct we need to understand the though process and calculation you have made in your mind that allowed you to get these values. If you can explain these 'rules' then coming up with an answer would be a bit easier.
Sample.xlsm
I adjusted layout to better imagine the logic.
By example:
ABC:
=Vlookup(data! ITEM & SUBGROUP & LINE_CODE & GROUP, lookup_ref! F:G, 2, 0)
DEF:
=Vlookup(data! ITEM & LINE_CODE & GROUP, lookup_ref! F:G, 2, 0)
- on data sheet, 0477 SUBGROUP instead of blank, but Vlookup = TRUE
> blank in Lookup_ref = any value on Data sheet (e.g SUBGROUP)
Following means there won't be 1 rule-based Vlookup but mechanism that looks up against all combinations on lookup_ref sheet.
Moreover, every user can have different combinations (category definitions) like COUNTRY & DISTANCE, GROUP & ITEM.
Where does the "ABC" or "DEF" come from in the original data?
The user defines "ABC", "DEF" etc manually in L. reference.
Then, data list has computed column, in which "ABC" is retrieved / looked up from that reference.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks