+ Reply to Thread
Results 1 to 12 of 12

Building A DYNAMIC FORMULA (to Simplify Relations) [SUBSTITUTE, LEN,..]

  1. #1
    Registered User
    Join Date
    04-29-2022
    Location
    Taipei, Taiwan
    MS-Off Ver
    MS 365
    Posts
    10

    Question Building A DYNAMIC FORMULA (to Simplify Relations) [SUBSTITUTE, LEN,..]

    Hi everyone,

    My Data in Column A has linked pairs (e.g [C-D]). The amount of pairs per row varies between 1 and 10 pairs:

    In [A1]: [A-B, C-D, A-E], data which I want to simplify into : -> [A-B-E, C-D]
    In [A2]: [C-D, G-A, G-B, F-E] -> [C-D, G-A,B, F-E]
    In [A3]: [A-B, B-F, E-F, H-I] -> [A-B-F-E, H-I]
    In [A4]: [F-G, H-G] -> [F-G-H]
    In [A5]: ...


    Can I bundle those pairs, and show groups of relations instead (e.g [A-B, B-F, E-F, H-I] -> [A-B-F-E, H-I])
    Can anyone inform me how I can tackle this?

    See excel-attachment for a similar, more detailed example..

    Thank you in advance,
    With friendly greetings,
    N.
    Last edited by Nic001; 05-05-2022 at 03:37 AM. Reason: Additional information

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Building A DYNAMIC FORMULA (to Simplify Relations) [SUBSTITUTE, LEN,..]

    This requires MS365 update with LAMBDA helper functions

    https://techcommunity.microsoft.com/...a/ba-p/3073293

    Please try

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-29-2022
    Location
    Taipei, Taiwan
    MS-Off Ver
    MS 365
    Posts
    10

    Re: Building A DYNAMIC FORMULA (to Simplify Relations) [SUBSTITUTE, LEN,..]

    Hi B.,

    Thank you for your Lambda function. This certainly IS the way forward.
    I am now trying to make sense of what you did here, how it works... [Alt+t+u+f, the Evaluate Formula]

    I did see imperfections in your attachment (12.2 KB):
    [C10] Dirk-Casper-Hilde, Fiona-Gerard-Isabel-Eva-Hilde) -> The formula did not link this as one group
    [C11] Boris-Anna, Isabel-Anna, Eva-Hilde, Dirk-Casper -> The function missed to combine Boris-Anna, Isabel-Anna into Boris-Anna-Isabel).

    I now can probably try to figure out how to improve and make this 100%.
    Thank you. Amazing how you came up with that, and so quick as well.

    With friendly greetings,
    N.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Building A DYNAMIC FORMULA (to Simplify Relations) [SUBSTITUTE, LEN,..]

    Massive formula

    Please Login or Register  to view this content.
    or with new TEXTSPLIT only available for Office insider
    https://techcommunity.microsoft.com/...s/ba-p/3186066

    Please Login or Register  to view this content.
    Last edited by Bo_Ry; 04-29-2022 at 01:30 PM.

  5. #5
    Registered User
    Join Date
    04-29-2022
    Location
    Taipei, Taiwan
    MS-Off Ver
    MS 365
    Posts
    10

    Re: Building A DYNAMIC FORMULA (to Simplify Relations) [SUBSTITUTE, LEN,..]

    Hi B.,

    The massive formula works perfectly fine... when adding 2 missing closing parentheses. ))
    Eyebrow-raising and wonderful to see how evaluates. ^^

    This problem is [SOLVED].
    Thank you.

    With friendly greetings,
    N.

  6. #6
    Registered User
    Join Date
    04-29-2022
    Location
    Taipei, Taiwan
    MS-Off Ver
    MS 365
    Posts
    10

    Re: Building A DYNAMIC FORMULA (to Simplify Relations) [SUBSTITUTE, LEN,..]

    Hi,


    (Un)Fortunately I stumbled upon an example that returns an imperfection:

    Dirk-Anna, Dirk-Boris, Casper-Anna, Fiona-Casper -> returns: Dirk-Anna-Boris, Fiona-Casper-Anna (The formula did not link this as one group)

    So this problem is [not Solved] yet. Can anyone come up with a 100% solution?


    With friendly greetings,
    N.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,351

    Re: Building A DYNAMIC FORMULA (to Simplify Relations) [SUBSTITUTE, LEN,..]

    Please provide an updated workbook highlighting the issue. Are there any other possible combinations you have not told us about?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Registered User
    Join Date
    04-29-2022
    Location
    Taipei, Taiwan
    MS-Off Ver
    MS 365
    Posts
    10

    Re: Building A DYNAMIC FORMULA (to Simplify Relations) [SUBSTITUTE, LEN,..]

    Hi A,


    The updated workbook attached here contains about a dozen 'simplified' examples.
    (My personal workbook contains 1000+ rows of these 'couplets').


    With friendly greetings,
    N.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,351

    Re: Building A DYNAMIC FORMULA (to Simplify Relations) [SUBSTITUTE, LEN,..]

    I think you probably need to tell us what is the MAXIMUM number of individual names that could/can end up in a family group. Is it FOUR or could it be more? If more, how many more? What's the maximum number of pairs that can/could exist in any cell that wuld need processing by the formula?

  10. #10
    Registered User
    Join Date
    04-29-2022
    Location
    Taipei, Taiwan
    MS-Off Ver
    MS 365
    Posts
    10

    Re: Building A DYNAMIC FORMULA (to Simplify Relations) [SUBSTITUTE, LEN,..]

    Hi A.,


    You are right. That would make the issue more clear.

    The maximum amount of individual names is 9 (A,B,C,D,E,F,G,H,I)

    The theoretical maximum amount of couplets in a single row is 13. (practically, on average, there are about 4 couplets, sometimes there are up to 6 couplets in a single row, rarely there are more then 8, but the theoretical maximum is 13)


    With friendly greetings,
    N.

  11. #11
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Building A DYNAMIC FORMULA (to Simplify Relations) [SUBSTITUTE, LEN,..]

    Please try

    Please Login or Register  to view this content.
    Last edited by Bo_Ry; 05-05-2022 at 07:49 AM.

  12. #12
    Registered User
    Join Date
    04-29-2022
    Location
    Taipei, Taiwan
    MS-Off Ver
    MS 365
    Posts
    10

    Re: Building A DYNAMIC FORMULA (to Simplify Relations) [SUBSTITUTE, LEN,..]

    Hi B.,


    Thank you, it does work.
    Functions used in your formula: LET, SORT, UNIQUE, FILTERXML, SUBSTITUTE, LAMBDA, ISNUMBER, TEXTJOIN, TRIM, FILTER

    Amazing. Thank you.

    The problem is [SOLVED].


    With friendly greetings,
    N.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Dynamic dropdown help - Indirect/Substitute function
    By Ralane in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-20-2019, 04:01 AM
  2. [SOLVED] How to simplify SUBSTITUTE formula for multiple substitution?
    By london7871 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-27-2017, 10:56 PM
  3. [SOLVED] INDIRECT SUBSTITUTE refering to a dynamic list
    By Frankie_The_Flyer in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-28-2017, 07:24 PM
  4. [SOLVED] INDIRECT SUBSTITUTE Referin to a dynamic list
    By Frankie_The_Flyer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-25-2017, 05:22 AM
  5. [SOLVED] Creating a formula that finds single values dependent on multi-variable relations
    By Bondras in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-02-2016, 03:01 PM
  6. [SOLVED] Simplify macro, make it dynamic
    By Rusty315 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-09-2014, 01:14 AM
  7. [SOLVED] Help with building a dynamic table and chart
    By thelegazy in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 07-12-2013, 11:59 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1