CONCAT Rows if Columns Match?

[rlyle]

I have taken the data from this table on "Sheet 1" and sorted columns C:F to show unique rows only on "Sheet 2"

Now, I need to CONCAT the "Location" column from my table on Sheet 1 in the "Location" Column of Sheet 2 if rows C:F match C:F.. Ex: the first row "48 80 DBL 4-7/8" should have "101, 102, 108, 109, 110, 111" in it.

I was able to get excel to find how many duplicates there are with my "qty" column:

So I'm thinking I could use that to tell it what "Locations" should be CONCAT?

Thank you,
Rob.

 

[Fluff]

You can use the TextJoin function for that How to use the Excel TEXTJOIN function | Exceljet

 

[rlyle]

You can use the TextJoin function for that How to use the Excel TEXTJOIN function | Exceljet

Fluff,
How would I wrap TEXTJOIN into a formula to join only the specific criteria I need though?

 

[Fluff]

Can you post your data from both sheets.

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.

 

[rlyle]

Can you post your data from both sheets.

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.

Cool! Yes, here you go:

test copy.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2
3			INTERIOR DOORS
4
5
6			2-Panel Flat	Solid Core	Primed	Match Existing (see pictures)
7
8			W	H	HANDING	JAMB	QTY	LOCATION	DOOR HARDWARE
9			48	80	DBL	4-7/8	6	NOTE FOR Fluff: '(If @C:F matches interior_doors[[Width]:[Jamb]], TEXTJOIN interior_doors[Location])'	DUMMY	14
10			48	80	DBL	6-7/8	1		PASSAGE	4
11			36	80	R	4-7/8	1		PRIVACY	3
12			28	80	L	4-7/8	1
13			28	80	R	6-7/8	2
14			24	80	L	4-7/8	1
15			24	79	L	4-7/8	1
16			24	80	R	6-7/8	1
17
18
19
20
Dashboard

Cell Formulas
Range	Formula
C6:F6	C6	=IF(ISBLANK('Interior Doors'!B7),"",'Interior Doors'!B7)
C9:F16	C9	=VALUETOTEXT(SORT(UNIQUE(FILTER(interior_doors[[Width]:[Jamb]],interior_doors[Location]<>"")),,-1))
J9	J9	=COUNTIF(interior_doors[Hardware],"DUMMY")*2
J10	J10	=COUNTIF(interior_doors[Hardware],"PASSAGE")
J11	J11	=COUNTIF(interior_doors[Hardware],"PRIVACY")
G9:G20	G9	=IF(ISBLANK(C:C),"",COUNTIFS(interior_doors[Width],C:C,interior_doors[Height],D:D,interior_doors[Handing],E:E,interior_doors[Jamb],F:F))
Dynamic array formulas.

test copy.xlsx
	A	B	C	D	E	F	G	H
1
2
3
4		Inteior Doors
5
6		Style/Model	Construction	Material		Notes
7		2-Panel Flat	Solid Core	Primed	Match Existing (see pictures)
8
9
10		Location	Width	Height	Handing	Jamb	Hardware
11		101	48	80	DBL	4-7/8	DUMMY
12		102	48	80	DBL	4-7/8	DUMMY
13		103	36	80	R	4-7/8	PRIVACY
14		104	28	80	L	4-7/8	PASSAGE
15		105	28	80	R	6-7/8	PRIVACY
16		106	28	80	R	6-7/8	PRIVACY
17		108	48	80	DBL	4-7/8	DUMMY
18		109	48	80	DBL	4-7/8	DUMMY
19		110	48	80	DBL	4-7/8	DUMMY
20		111	48	80	DBL	4-7/8	DUMMY
21		112	24	80	L	4-7/8	PASSAGE
22		113	24	79	L	4-7/8	PASSAGE
23		114	48	80	DBL	6-7/8	DUMMY
24		115	24	80	R	6-7/8	PASSAGE
25
26
27
28
Interior Doors

Cells with Data Validation
Cell	Allow	Criteria
H11:H18	Any value
G11:G25	List	PASSAGE, PRIVACY, DUMMY,

 

[Fluff]

Thanks for that.
How about, you will need to change the C9 formula as shown

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2
3			INTERIOR DOORS
4
5
6			2-Panel Flat	Solid Core	Primed	Match Existing (see pictures)
7
8			W	H	HANDING	JAMB	QTY	LOCATION	DOOR HARDWARE
9			48	80	DBL	4-7/8	6	101, 102, 108, 109, 110, 111	DUMMY	14
10			48	80	DBL	6-7/8	1	114	PASSAGE	4
11			36	80	R	4-7/8	1	103	PRIVACY	3
12			28	80	L	4-7/8	1	104
13			28	80	R	6-7/8	2	105, 106
14			24	80	L	4-7/8	1	112
15			24	79	L	4-7/8	1	113
16			24	80	R	6-7/8	1	115
17
Report

Cell Formulas
Range	Formula
C6:F6	C6	=IF(ISBLANK('Interior Doors'!B7),"",'Interior Doors'!B7)
C9:F16	C9	=SORT(UNIQUE(FILTER(interior_doors[[Width]:[Jamb]],interior_doors[Location]<>"")),,-1)
J9	J9	=COUNTIF(interior_doors[Hardware],"DUMMY")*2
J10	J10	=COUNTIF(interior_doors[Hardware],"PASSAGE")
J11	J11	=COUNTIF(interior_doors[Hardware],"PRIVACY")
H9:H16	H9	=TEXTJOIN(", ",,IF((interior_doors[Width]=C9)*(interior_doors[Height]=D9)*(interior_doors[Handing]=E9)*(interior_doors[Jamb]=F9),interior_doors[Location],""))
G9:G17	G9	=IF(ISBLANK(C9),"",COUNTIFS(interior_doors[Width],C9,interior_doors[Height],D9,interior_doors[Handing],E9,interior_doors[Jamb],F9))
Dynamic array formulas.

 

[rlyle]

Thanks for that.
How about, you will need to change the C9 formula as shown

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2
3			INTERIOR DOORS
4
5
6			2-Panel Flat	Solid Core	Primed	Match Existing (see pictures)
7
8			W	H	HANDING	JAMB	QTY	LOCATION	DOOR HARDWARE
9			48	80	DBL	4-7/8	6	101, 102, 108, 109, 110, 111	DUMMY	14
10			48	80	DBL	6-7/8	1	114	PASSAGE	4
11			36	80	R	4-7/8	1	103	PRIVACY	3
12			28	80	L	4-7/8	1	104
13			28	80	R	6-7/8	2	105, 106
14			24	80	L	4-7/8	1	112
15			24	79	L	4-7/8	1	113
16			24	80	R	6-7/8	1	115
17
Report

Cell Formulas
Range	Formula
C6:F6	C6	=IF(ISBLANK('Interior Doors'!B7),"",'Interior Doors'!B7)
C9:F16	C9	=SORT(UNIQUE(FILTER(interior_doors[[Width]:[Jamb]],interior_doors[Location]<>"")),,-1)
J9	J9	=COUNTIF(interior_doors[Hardware],"DUMMY")*2
J10	J10	=COUNTIF(interior_doors[Hardware],"PASSAGE")
J11	J11	=COUNTIF(interior_doors[Hardware],"PRIVACY")
H9:H16	H9	=TEXTJOIN(", ",,IF((interior_doors[Width]=C9)*(interior_doors[Height]=D9)*(interior_doors[Handing]=E9)*(interior_doors[Jamb]=F9),interior_doors[Location],""))
G9:G17	G9	=IF(ISBLANK(C9),"",COUNTIFS(interior_doors[Width],C9,interior_doors[Height],D9,interior_doors[Handing],E9,interior_doors[Jamb],F9))
Dynamic array formulas.

Dude!!!! Can't wipe the smile off my face! Thank you so much Fluff. Legend.

 

[Fluff]

You're welcome & thanks for the feedback.