Results 1 to 4 of 4

Thread: Formula to concatenate text based on criteria and < than 145 characters
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking Formula to concatenate text based on criteria and < than 145 characters

    Hi

    I have created a "monstrosity" of a formula to return text from another worksheet contingent upon the criteria in column A or D of the 'Transactions for Recode' worksheet and returns text to a maximum of 145 characters.
    The formula is way too large and takes some time to calculate. I was hoping that someone could review this formula and assist me to reduce it's size or make it calculate much quicker?

    Below is a screenshot of the formula described above.

    =IF(LEN(IF('Transactions for Recode'!B31="Adjustment","Recode - "&'Transactions for Recode'!E31,IF('Transactions for Recode'!O31="",IF('Transactions for Recode'!D31="INVENTORY","Recode - "&'Transactions for Recode'!M31,"Recode - "&'Transactions for Recode'!K31&" - "&'Transactions for Recode'!L31),IF('Transactions for Recode'!D31="INVENTORY","Recode - "&'Transactions for Recode'!P31&" - "&'Transactions for Recode'!N31&" - "&'Transactions for Recode'!M31,"Recode - "&'Transactions for Recode'!P31&" - "&'Transactions for Recode'!N31&" - "&'Transactions for Recode'!K31))))>25,LEFT(IF('Transactions for Recode'!B31="Adjustment","Recode - "&'Transactions for Recode'!E31,IF('Transactions for Recode'!O31="",IF('Transactions for Recode'!D31="INVENTORY","Recode - "&'Transactions for Recode'!M31,"Recode - "&'Transactions for Recode'!K31&" - "&'Transactions for Recode'!L31),IF('Transactions for Recode'!D31="INVENTORY","Recode - "&'Transactions for Recode'!P31&" - "&'Transactions for Recode'!N31&" - "&'Transactions for Recode'!M31,"Recode - "&'Transactions for Recode'!P31&" - "&'Transactions for Recode'!N31&" - "&'Transactions for Recode'!K31))),145),IF('Transactions for Recode'!B31="Adjustment","Recode - "&'Transactions for Recode'!E31,IF('Transactions for Recode'!O31="",IF('Transactions for Recode'!D31="INVENTORY","Recode - "&'Transactions for Recode'!M31,"Recode - "&'Transactions for Recode'!K31&" - "&'Transactions for Recode'!L31),IF('Transactions for Recode'!D31="INVENTORY","Recode - "&'Transactions for Recode'!P31&" - "&'Transactions for Recode'!N31&" - "&'Transactions for Recode'!M31,"Recode - "&'Transactions for Recode'!P31&" - "&'Transactions for Recode'!N31&" - "&'Transactions for Recode'!K31))))

    Any assistance would be greatly appreciated.

    Regards,
    Damo100


  2. #2
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Formula to concatenate text based on criteria and < than 145 characters

    what's the criteria in simple English?

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to concatenate text based on criteria and < than 145 characters

    Hi AlanY

    If the reference in column B of the 'Transactions for Recode' worksheet = 'Adjustment', concatenate several different cells
    If the reference in column O = "" and the reference in column D = "Inventory", then concatenate a number of different cells
    OR if the reference in column O = something other than zero and
    the reference in column D = "Inventory", then concatenate a different set of cells
    All of this within the context of returning a maximum of up to 145 characters in total.

    Hope that makes sense?

    Regards,
    Damo100

  4. #4
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Formula to concatenate text based on criteria and < than 145 characters

    what I would do is to make use of helper columns to concatenate the cells based on the criteria and use the if() function to pick it out.
    btw, what are you going to do if it's > 145 characters?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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