Formula to read text then delete from table (Substitute?)

[JTL9161]

Hello,

I have a 3 column (10 rows) table with various and duplicated text. On the same spreadsheet I have a IF statement that based on a "Yes" or "No" it fills in data

in F1 I have =IF E1="YES","ABC","XYZ". So F1 will either fill with "ABC" or "XYZ"

What I am trying to happen is when either "ABC" or "XYZ" fills F1 I need all instances of that text deleted from the 3 column 10 row table.

Thank you,
James

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F
1	ABC	QRS	ABC			ABC
2	123	XYZ	DEF
3	789	ABC	XYZ
4
5
6		QRS
7	123	XYZ	DEF
8	789		XYZ
Main

Cell Formulas
Range	Formula
A6:C8	A6	=SUBSTITUTE(A1:C3,F1,"")
Dynamic array formulas.

 

[JTL9161]

Doh! That was easy. Thanks

 

[Fluff]

You're welcome & thanks for the feedback.

 

[JTL9161]

Doing something wrong. Getting Spill Error. I selected A6:C8 and entered the formula =SUBSTITUTE(A1:C3,F1,"") in cell A6 and this is what I get.

 

[Fluff]

Clear those cells & just put the formula in A6

 

[JTL9161]

So I got the above to work and I thought my next task would be a simple revision but I was wrong. How do you make the formula work for multiple entries in column F? Meaning col F has 10-12 different data going down it and I need any data in the table matching any of the data in column F to be deleted.

Thanks again,
James

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F
1	ABC	QRS	ABC			ABC
2	123	XYZ	DEF			xyz
3	789	ABC	XYZ
4
5
6		QRS
7	123		DEF
8	789
9
Main

Cell Formulas
Range	Formula
A6:C8	A6	=IF(ISNUMBER(MATCH(A1:C3,F1:F2,0)),"",A1:C3)
Dynamic array formulas.

 

[JTL9161]

Again BINGO! Thanks.

 

[Fluff]

You're welcome.