Textjoin records

[FryGirl]

I'm using =TEXTJOIN(", ",TRUE,IF(A5:A11=4,F5:F11,"")) which works fine, but I would have to now transfer this down the spreadsheet to the next cell and then the formula would be =TEXTJOIN(", ",TRUE,IF(A13:A17=4,F13:F17,""))

I'm working with a Work Breakdown Structure and need to join all of the level 4 values.

In column A I have the WBS levels (1, 2, 3, 4). After every level 3, I need to textjoin all of the level 4's until the next level 3.

Sorry, I would post this data using the XLBB tool, but can't access it on this computer.

Can this be done with a formula or will it require VBA? I'm fine either way.

 

[Herakles]

I'm using =TEXTJOIN(", ",TRUE,IF(A5:A11=4,F5:F11,"")) which works fine, but I would have to now transfer this down the spreadsheet to the next cell and then the formula would be =TEXTJOIN(", ",TRUE,IF(A13:A17=4,F13:F17,""))

I'm working with a Work Breakdown Structure and need to join all of the level 4 values.

In column A I have the WBS levels (1, 2, 3, 4). After every level 3, I need to textjoin all of the level 4's until the next level 3.

Sorry, I would post this data using the XLBB tool, but can't access it on this computer.

Can this be done with a formula or will it require VBA? I'm fine either way.

I'm not sure if this will work for you.

I would hide column F and have a new column G to contain a formula.

I have changed the task name to the cell address so that you see that it is pulling in the right data.

Textjoin records.xlsx
	A	B	C	D	E	F	G
1	Level	WBS	Work Centre	Cell	Functions	Task	Task
2	1	1	First Line
3	2	1.1		Second Line
4	3	1.1.1			Third Line		$F$5, $F$6, $F$7, $F$8, $F$9, $F$10, $F$11
5	4	1.1.1.1				$F$5	$F$5
6	4	1.1.1.2				$F$6	$F$6
7	4	1.1.1.3				$F$7	$F$7
8	4	1.1.1.4				$F$8	$F$8
9	4	1.1.1.5				$F$9	$F$9
10	4	1.1.1.6				$F$10	$F$10
11	4	1.1.1.7				$F$11	$F$11
12	3	1.1.2			Third Line		$F$13, $F$14, $F$15, $F$16, $F$17
13	4	1.1.2.1				$F$13	$F$13
14	4	1.1.2.2				$F$14	$F$14
15	4	1.1.2.3				$F$15	$F$15
16	4	1.1.2.4				$F$16	$F$16
17	4	1.1.2.5				$F$17	$F$17
18	3	1.1.3			Third Line		$F$19, $F$20, $F$21, $F$22
19	4	1.1.3.1				$F$19	$F$19
20	4	1.1.3.2				$F$20	$F$20
21	4	1.1.3.3				$F$21	$F$21
22	4	1.1.3.4				$F$22	$F$22
Sheet1

Cell Formulas
Range	Formula
F5:F11,F19:F22,F13:F17	F5	=ADDRESS(ROW(),COLUMN())
G4:G22	G4	=IF($A4=3,TEXTJOIN(", ",TRUE,INDIRECT("$F" & ROW() & ":$F" & ROW()+COUNTIFS($B4:$B22,$B4 & "*",$A4:$A22,4))),$F4)

 

[FryGirl]

This works great for my needs. Thank you.

 

[Herakles]

This works great for my needs. Thank you.

I'm glad that it works for you. You just need to change the ranges it refers to. I wanted to avoid using VBA.

If you can use XL2BB when posting data it wil save people having to type it out.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Alternatively, just cut and paste it into the message.

 

[MARK858]

Alternatively, just cut and paste it into the message.

I think you probably meant copy rather than cut

 

[Herakles]

I think you probably meant copy rather than cut

I certainly did. Thanks.