Return Column Name.

[pizpower]

Hello,

I'd like to to automate few things, where I need to match the Main/Red ID's and get the Cab which will be in the 1st cell of each column, is this possible?

Settings Sheet Example:

Main ID's	Red ID's	Cab 1	Cab 2
1246​	1248​	1244​	761​
1242​	1244​	1242​	762​
558​	580​		600​
761​	762​
1328​	1329​
1140​	1141​
1236​	1238​

Sheet 2 Example:

Patch Details A	Patch Details B	Cabinet ID A	Cabinet ID B
ID1242 F67-68	ID761 F1-2	​	​
ID1244 F59-60	ID762 F1-2	​	​

In sheet 2 I'd like to return 2 results in a single cell, example:

Patch Details A	Patch Details B	Cabinet ID A	Cabinet ID B
ID1242 F67-68	ID761 F1-2	Main,Cab 1​	Main,Cab 2​

or seperate

Patch Details A	Patch Details B	Cabinet ID A	ID A	Cabinet ID B	ID B
ID1242 F67-68	ID761 F1-2	Cab 1​	Main	Cab 2​	Main

Thanks in advanced.
David

 

[pizpower]

so far for the part of the main/red i managed to make it work like this but i believe this can be improved, not an expert in excel
=IFNA(IFS(SUMPRODUCT(IFERROR(SEARCH(FILTER('ODF Location Settings'!$A$2:$A$5000,'ODF Location Settings'!$A$2:$A$5000<>""),F2),""))>0,"Main ID",SUMPRODUCT(IFERROR(SEARCH(FILTER('ODF Location Settings'!$B$2:$B$5000,'ODF Location Settings'!$B$2:$B$5000<>""),F2),""))>0,"Red ID"),"Not Assigned")

 

[pizpower]

*bump*

 

[pizpower]

nothing?

 

[JGordon11]

Book1
	A	B	C	D	E	F	G	H	I	J
1	Table1
2	Main ID's	Red ID's	Cab 1	Cab 2		Patch Details A	Patch Details B	Cabinet ID A	Cabinet ID B
3	1246	1248	1244	761		ID1242 F67-68	ID761 F1-2	Main, Cab 1	Main, Cab 2
4	1242	1244	1242	762		ID1244 F59-60	ID762 F1-2	Red, Cab 1	Red, Cab 2
5	558	580		600		ID580 xyz	ID777 xyz	Red
6	761	762				ID1236 abc	ID600 abc	Main	Cab 2
7	1328	1329
8	1140	1141
9	1236	1238
10
Sheet1

Cell Formulas
Range	Formula
H3:H6	H3	=SUBSTITUTE(LET(t, TEXTBEFORE([@[Patch Details A]]," "), id, --RIGHT(t,LEN(t)-2), TEXTJOIN(", ", 1, IF(MMULT(SEQUENCE(1,ROWS(Table1),1,0), --(Table1=id)), Table1[#Headers], ""))), " ID's", "")
I3:I6	I3	=SUBSTITUTE(LET(t, TEXTBEFORE([@[Patch Details B]]," "), id, --RIGHT(t,LEN(t)-2), TEXTJOIN(", ", 1, IF(MMULT(SEQUENCE(1,ROWS(Table1),1,0), --(Table1=id)), Table1[#Headers], ""))), " ID's", "")

 

[Cubist]

Book3
	A	B	C	D	E	F	G	H	I
1	Table1
2	Main ID's	Red ID's	Cab 1	Cab 2		Patch Details A	Patch Details B	Cabinet ID A	Cabinet ID B
3	1246	1248	1244	761		ID1242 F67-68	ID761 F1-2	Main , Cab 1	Cab 2, Main
4	1242	1244	1242	762		ID1244 F59-60	ID762 F1-2	Cab 1, Red	Cab 2, Red
5	558	580		600		ID580 xyz	ID777 xyz	Red
6	761	762				ID1236 abc	ID600 abc	Main	Cab 2
7	1328	1329
8	1140	1141
9	1236	1238
Sheet5

Cell Formulas
Range	Formula
H3:I6	H3	=TEXTJOIN(", ",,IF(--TEXTAFTER(TEXTBEFORE(F3," "),"ID")=--$A$3:$D$9,TEXTBEFORE($A$2:$D$2,"ID",,,,$A$2:$D$2),""))

 

[pizpower]

Aaa thanks will try it later looks exactly what I need.

Thanks both for your help.

 

[pizpower]

Book3
	A	B	C	D	E	F	G	H	I
1	Table1
2	Main ID's	Red ID's	Cab 1	Cab 2		Patch Details A	Patch Details B	Cabinet ID A	Cabinet ID B
3	1246	1248	1244	761		ID1242 F67-68	ID761 F1-2	Main , Cab 1	Cab 2, Main
4	1242	1244	1242	762		ID1244 F59-60	ID762 F1-2	Cab 1, Red	Cab 2, Red
5	558	580		600		ID580 xyz	ID777 xyz	Red
6	761	762				ID1236 abc	ID600 abc	Main	Cab 2
7	1328	1329
8	1140	1141
9	1236	1238
Sheet5

Cell Formulas
Range	Formula
H3:I6	H3	=TEXTJOIN(", ",,IF(--TEXTAFTER(TEXTBEFORE(F3," "),"ID")=--$A$3:$D$9,TEXTBEFORE($A$2:$D$2,"ID",,,,$A$2:$D$2),""))

my only issue with this is that for some reason am having some results swapped.

Also what i noticed the word ID, is not being added, for me it doesn't affect me, just trying to figure out the formula :D
Other then that it works fine.

 

[Cubist]

I removed the "ID" because you didn't show them in the OP. Try this.

Book1
	A	B	C	D	E	F	G	H	I
1	Table1
2	Main ID's	Red ID's	Cab 1	Cab 2		Patch Details A	Patch Details B	Cabinet ID A	Cabinet ID B
3	1246	1248	1244	761		ID1242 F67-68	ID761 F1-2	Main ID's, Cab 1	Main ID's, Cab 2
4	1242	1244	1242	762		ID1244 F59-60	ID762 F1-2	Red ID's, Cab 1	Red ID's, Cab 2
5	558	580		600		ID580 xyz	ID777 xyz	Red ID's
6	761	762				ID1236 abc	ID600 abc	Main ID's	Cab 2
7	1328	1329
8	1140	1141
9	1236	1238
Sheet3

Cell Formulas
Range	Formula
H3:I6	H3	=TEXTJOIN(", ",,TOROW(IF(--TEXTAFTER(TEXTBEFORE(F3," "),"ID")=--$A$3:$D$9,$A$2:$D$2,""),,1))

 

[pizpower]

Book1
	A	B	C	D	E	F	G	H	I	J
1	Table1
2	Main ID's	Red ID's	Cab 1	Cab 2		Patch Details A	Patch Details B	Cabinet ID A	Cabinet ID B
3	1246	1248	1244	761		ID1242 F67-68	ID761 F1-2	Main, Cab 1	Main, Cab 2
4	1242	1244	1242	762		ID1244 F59-60	ID762 F1-2	Red, Cab 1	Red, Cab 2
5	558	580		600		ID580 xyz	ID777 xyz	Red
6	761	762				ID1236 abc	ID600 abc	Main	Cab 2
7	1328	1329
8	1140	1141
9	1236	1238
10
Sheet1

Cell Formulas
Range	Formula
H3:H6	H3	=SUBSTITUTE(LET(t, TEXTBEFORE([@[Patch Details A]]," "), id, --RIGHT(t,LEN(t)-2), TEXTJOIN(", ", 1, IF(MMULT(SEQUENCE(1,ROWS(Table1),1,0), --(Table1=id)), Table1[#Headers], ""))), " ID's", "")
I3:I6	I3	=SUBSTITUTE(LET(t, TEXTBEFORE([@[Patch Details B]]," "), id, --RIGHT(t,LEN(t)-2), TEXTJOIN(", ", 1, IF(MMULT(SEQUENCE(1,ROWS(Table1),1,0), --(Table1=id)), Table1[#Headers], ""))), " ID's", "")

This works perfect! Thanks :D