UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Exclude Values From Textjoin, Any Version    
 
   
Baynyn
post May 23 2019, 11:02 AM
Post#1



Posts: 6
Joined: 13-April 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)
Attached File  Test1.zip ( 7.35K )Number of downloads: 4
 
Go to the top of the page
 
Baynyn
post May 24 2019, 08:42 AM
Post#2



Posts: 6
Joined: 13-April 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)
Attached File  Test2.zip ( 9.25K )Number of downloads: 1
 
Go to the top of the page
 
Bullschmidt
post May 28 2019, 05:18 PM
Post#3



Posts: 12
Joined: 8-May 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
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th June 2019 - 04:31 PM