Locate 1st column that does not contain A, B or C

[Glen_Marshall]

Hi all,

I have a spread sheet with c.20k rows of data with 8 columns in the following format:

Comp1 Cost1 Comp2 Cost2 Comp3 Cost3 Comp4 Cost4

Under the Comp columns there are multiple (c.100) codes.

I'm trying to locate (for each line) the 1st 'Comp' column that does not contain U7,UN or UI. Once I can locate this column then I can extract the Cost associated to that Comp.

Bit of background, companies U7, UN and UI will soon not be available so I want to locate the next cost

Hope this makes sense, I've tried various options but cannot find anything to find where it's not equal to a set of characters.

All help / advice gratefully received.

Regards

Glen

 

[Peter_SSs]

Re: How to locate 1st column that does not contain A, B or C

Assuming the 'Comp' values are text and the 'Cost' values are numerical, would this work for you?
If not,please give added requirements.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Comp1	Cost1	Comp2	Cost2	Comp3	Cost3	Comp4	Cost4		Comp	Cost
2	AB	3	U7	4	UN	2	CD	6		AB	3
3	U7	8	UI	22	UN	3
4	U7	9	UI	23	UN	4	XZ	3		XZ	3
5	UI	5	XXX	100	ABC	99				XXX	100
Locate cost

Cell Formulas
Range	Formula
J2	=IFERROR(INDEX(A2:H2,AGGREGATE(15,6,(COLUMN(A2:H2)-COLUMN(A2)+1)/((ISTEXT(A2:H2))*(ISERROR(FIND(A2:H2,"U7UNUI")))),1)),"")
K2	=IF(J2="","",INDEX(B2:H2,MATCH(J2,A2:G2,0)))

 

[Glen_Marshall]

Re: How to locate 1st column that does not contain A, B or C

Thank you so much, 100% spot on!!!

Looks like I have a new function to learn of Aggregate, thanks again!!!

 

[Peter_SSs]

Re: How to locate 1st column that does not contain A, B or C

Thank you so much, 100% spot on!!!

Good news. Thanks for letting us know.