Compare data, if, then else if ?

[charmallan]

Hi. Sorry, new to this. Need to find a way to semi process some data. Perhaps easier to demo with a table.
My data is column A has changing values, ie site names is not a constant, but the rest of the text is. I have about 50 of these statements. Each of these statements can be associated with a specific keyword. What i would like to do, is ensure that column A is in the correct format. (Perhaps this can reference a list with set values to match ?) Then i need the data from column a to do something like, if a ="-Business Area - Type - Air Compressors" and Column B contains "air compressor" or Compressor", then Column C = Air compressor. Can this be done ? I'm trying to read up on these if , then and if else statements, but I cant get anything to work. Please help ?

A	B	C	D	E
Site- Area - Type - Air Supply	Air Compressor	Air Compressor
ST - Area - Type - Vents	Vent	Vent
Site- Area - Type - x	Compressor, air	Air Compressor

<tbody>
</tbody>

 

[oldbrewer]

this is not at all clear.
My data is column A has changing values - what does this mean ?
what is in column A the first row infers it is .................Site- Area - Type - Air Supply
is air supply equivalent to air compressor
and is air compressor equivalent to compressor
and if columns A and B have matching equivalents do you want the approved words to go into column C

 

[charmallan]

Oh dear, sorry for the confusion, but i think you are on the right track. Essentially, the site name and the Area part of the text in column A will change. The constant part of the text "type-Air Supply-" should be associated with terms such as "Compressor" or "Air Compressor", which would appear in column B. I need column C to be consistent, so if column A matches Column B, regardless whether column B matched Column A through the term "Compressor" or "Air Compressor", then column C would be the equivalent to "Air Compressor". I dont know how to start an if or statement. It seems pretty simple, yet I cant get it to work. Should it not be something easy like,
IF A="type-Air Supply-" And "B=Air Compressor" Then C=Air Compressor.ELSEIF
A="type-Air Supply-" And "B=Compressor" Then C=Air Compressor
IF NO MATCH then Skip and loop through the rest of the data.

I'm making a mess of this right ?

 

[oldbrewer]

	we are looking for the "correct" techncal term
	let us consider…….air,,,,,,air supply,,,,,,,,air compressor,,,,,,,,,,compressor,,,,,,,,,compressor supply
	if all of these terms are equivalent and the preferred wording is AIR COMPRESSOR
	then is the table below correct
	air	air	air compressor
	air	air supply	air compressor
	air	compressor	air compressor
	compressor supply	air supply	air compressor
	air	banana	INSUFFICIENT INFO

<colgroup><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[charmallan]

yes, that looks right to me. But I've no idea how to compile it into something useful that will actually analyse the data. Have you got an idea for me please ? I dont mind learning, i just don't now where to start.

 

[oldbrewer]

air	air	air compressorrr
air	air supply	air compressorrr
air	compressor	air compressorrr						this is mytable
compressor supply	air supply	air compressorrr
air	banana	NO MATCH
								air	air compressorrr
								air supply	air compressorrr
								banana	fruit
								compressor	air compressorrr
								compressor supply	air compressorrr
	the formula in C1 is
	=IF(VLOOKUP(A1,mytable,2)=VLOOKUP(B1,mytable,2),VLOOKUP(B1,mytable,2),"NO MATCH")
	you will need to build up the lookup table
	with all the words for all the other things
	NOTE WHEN YOU HAVE ADDED THEM, HIGHLIGHT
	THE LOOKUP TABLE AND SORT BY LEFT HAND COLUMN ASCENDING

<colgroup><col><col><col><col span="5"><col><col><col></colgroup><tbody>
</tbody>

 

[oldbrewer]

I am happy to talk about it further off line - if you want to PM me your Email address (DO NOT ADVERTISE YOUR eMAIL ON HERE)