My Assistant
Custom Search

Exclude Values From Textjoin, Any Version 
May 23 2019, 11:02 AM Post#1  
Posts: 6 Joined: 13April 11  Good afternoon all, I am trying to create a TEXTJOIN formula that will ignore cells based on certain criteria. I've attached a sample. Based off of the value in the "Indent" column, I want to use a TEXTJOIN (or CONCAT or any other function that would work) to combine the text of only the last instance of each of the previous indents. The results of the formula I'm trying to work out are in column 3. The formula in column 4 identifies the row number of the last instance of the previous indent, which I think should be useful. My trouble is translating the results of column 4 into the text I need. Is there a way to exclude values from a TEXTJOIN function so that it only combines text from the most recent previous indents? When I nest an IF function in the text1 argument of the TEXTJOIN function, I can't limit it properly. Any help or advice is greatly appreciated! Attached File(s) 
May 24 2019, 08:42 AM Post#2  
Posts: 6 Joined: 13April 11  I found a solution, albeit an inelegant one. I copied the formula from column 4 into columns 5, 6, and 7, and changed the nested IF function in each to reflect the corresponding level of previous indent. I then created this formula in column 8: =IF(AND(G2=0,F2=0,E2=0,D2=0),B2,IF(AND(G2=0,F2=0,E2=0),INDEX($B$1:B2,D2)&" > "&B2,IF(AND(G2=0,F2=0),INDEX($B$1:B2,E2)&" > "&INDEX($B$1:B2,D2)&" > "&B2,IF(G2=0,INDEX($B$1:B2,F2)&" > "&INDEX($B$1:B2,E2)&" > "&INDEX($B$1:B2,D2)&" > "&B2,INDEX($B$1:B2,G2)&" > "&INDEX($B$1:B2,F2)&" > "&INDEX($B$1:B2,E2)&" > "&INDEX($B$1:B2,D2)&" > "&B2)))) Like I said, it's not pretty, but it got the job done. I'm sure there is a way to code this, but without knowing VBA, this is the best I could do. Attached File(s) 
May 28 2019, 05:18 PM Post#3  
Posts: 12 Joined: 8May 19  Way to go and that has to be about the longest, most involved Excel cell formula I've seen in awhile! This post has been edited by Bullschmidt: May 28 2019, 05:19 PM 
Custom Search

Search Top LoFi  24th June 2019  04:31 PM 