+ Reply to Thread
Results 1 to 6 of 6

Textjoin function based on changing row number

  1. #1
    Registered User
    Join Date
    05-20-2019
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365 Version 16.24
    Posts
    4

    Textjoin function based on changing row number

    I am trying to create a text join from this raw data source.

    I want to create a 'description' field in another sheet that 'textjoin' the description from Column G.

    So it needs to textjoin G2 - G8 for the first title, then based on where the second title is in column B, Text join G9 - G15.

    This is hard because there is a different number of rows for each description based off the title in column b.

    Is there a way to textjoin rows with key BTRTN-1573 or blank, until BTRTN-1569, then start new text join in the row below?

    So . the description would be for the first one: "As a member of the team, I want to do something, So that I can do something else, Success criteria:, Completion of the formula, No dependence on others"
    And the the second one: "As a member of another team, I want to do something else, So that I can do something again, Success criteria:, Completion of a different formula, No independence on others"

    Currently all I can figure out is how to do one at a time with this formula:
    =TEXTJOIN(",",TRUE,G2:G8)

    Thinking something like:
    =IF(B2="",=TEXTJOIN(",",TRUE,G2:G8),"")
    Attached Images Attached Images

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Textjoin function based on changing row number

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    05-20-2019
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365 Version 16.24
    Posts
    4

    Re: Textjoin function based on changing row number

    Attached - As I was doing this realised that there are a few other things that are going to be hard.

    As the raw data source is updated with the description rows changing each time for the 'key', how will the second sheet stay up to date?
    Is there a way to set the formula so it is searching for a new key then picks up the required description for just that key?
    Attached Files Attached Files

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

    Re: Textjoin function based on changing row number

    Please try at After
    A2
    =IFERROR(AGGREGATE(15,6,ROW($A$2:$A$99)/('Before - Raw Jira Data'!$B$2:$B$99<>0),ROWS(A$2:A2)),"")

    B2:E2
    =IF($A2="","",INDEX('Before - Raw Jira Data'!B:B,$A2))

    F2
    =IF(A2="","",TEXTJOIN(",",,INDEX('Before - Raw Jira Data'!$G:$G,A2):INDEX('Before - Raw Jira Data'!$G:$G,IFERROR(A3-1,A2+9))))

    G2:H2
    =IF($A2="","",INDEX('Before - Raw Jira Data'!H:H,$A2))
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-20-2019
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365 Version 16.24
    Posts
    4

    Re: Textjoin function based on changing row number

    Amazing! That works the way I was hoping, but how would this formula be continued down the page? I tried to copy the formula on the 'after- output data' page onto rows below with new data on the 'raw data' page and it didn't work.

    The raw data sheet I actually have is 400 rows of raw data and should go into around 100 rows on the new sheet.

  6. #6
    Registered User
    Join Date
    05-20-2019
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365 Version 16.24
    Posts
    4

    Wink Re: Textjoin function based on changing row number

    Figured it out - thanks for your help

+ 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. Textjoin and if function need help
    By bitozi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-23-2019, 06:35 AM
  2. Error Using TEXTJOIN Function in Array Formula to List Non-empty Strings
    By MikeA01730 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-18-2019, 01:24 AM
  3. CONCAT/TEXTJOIN on Variable Number of Cells
    By OlYeller21 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-10-2019, 06:29 AM
  4. Use two conditions in textjoin function in excel 2013.
    By magfas in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-15-2018, 04:42 PM
  5. Function similar to textjoin
    By Allerdrengen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-07-2018, 06:54 AM
  6. [SOLVED] TextJoin formula with MID function
    By Brsth in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-26-2018, 11:00 AM
  7. TEXTJOIN function
    By Tony Valko in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-13-2016, 06:48 PM

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