Moving fields that match a certain criteria....HELP!!

Posted by Danielle on May 04, 2001 7:19 AM

Hi everyone,

Does anyone know of a quick way of doing the following :

If any fields in column G equal "xxx" or "xxx" or "xxx" (where x is a word) then move them across to column F.

It would save me sooooo much time if anyone knew of a way to do this automatically.

Thanks again for your help!!

danielle

Posted by Aladin Akyurek on May 04, 2001 8:06 AM

Danielle,

If I understood you correctly and you don't mind using a formula, try in F1:

=IF(ISTEXT(G1),IF(LEN(G1)>=1, G1,"")

Copy down this as far as needed.

Aladin

Posted by Kevin James on May 04, 2001 8:29 AM

clarification please

Are you saying that you only have three criteria? If so, I have an easy solution for you.

Posted by danielle on May 04, 2001 8:52 AM

Re: clarification please

Actually, I probably have around 5 but 3 would definately be a start!!

danielle

Posted by Kevin James on May 04, 2001 9:16 AM

Explanation here, but sample available

danielle:

On sheet1, lets say column A is your raw data and column B will contain the filtered list based on your criteria list.
On sheet2, column A is your criteria list

Given that Row1 is usually a heading row and row 2 is your first data row, here is the formula for Sheet1, cell B2 which can be copied down for the duration of your raw data.

=VLOOKUP(A2,Sheet2!A2:A10,1,FALSE)

What will result is a list of matching data. Where the data doesn't match, you'll get "#N/A". You can then use Copy/PasteSpecial Values to change column B in text only.

Kevin