Extract all matching rows with "Active" from a database

**smalldoll** · 07-17-2009, 01:26 PM

Heres what I need:

On the 'values' worksheet (first one), a formula in cell M2 that will search the worksheet 'oc_users' (second one) for any row containing "Active" in column G, these rows will return the corresponding email address in column A of the same worksheet. Skipping "Inactive" rows.

End result: anyone with an active status in oc_users will have their email populated in the 'values' worksheet.

Thanks in advance to anyone who can help!

help.xls

**NBVC** · 07-17-2009, 01:34 PM

Try this formula:

Please Login or Register  to view this content.

edit the ranges to suit your data and then confirm with CTRL+SHIFT+ENTER not just ENTER so that { } brackets appear, then copy down as far as you want.

**smalldoll** · 07-17-2009, 02:10 PM

This formula works beautifully in my sample - thank you. When I try and apply it to my real workbook it returns a blank cell. I'm certain this is happening because the active / inactive column has a formula in it which populates it with either active or inactive - how can I adjust your formula to consider the value instead of the formula in column G?

**NBVC** · 07-17-2009, 02:17 PM

It should still work, make sure, though that the formula result is not returning Active with an extra space or something to not make it an exact match.

**smalldoll** · 07-17-2009, 02:28 PM

Double checked and the formula result value to return is "Active" or "Inactive" - no spaces. So nothing to add?

**NBVC** · 07-17-2009, 02:46 PM

Show us your workbook? Or your formula and the ranges your are trying to work with.

Also, you made sure to confirm with CTRL+SHIFT+ENTER to get the { } brackets?

**smalldoll** · 07-20-2009, 10:31 AM

I would but it's 18.5Mb so that's too much to upload which is why I made a small version previously uploaded here. Put it this way, when I manually enter the word Inactive in ref cell the formula works - that's how I know it's not reading the value.

**NBVC** · 07-20-2009, 01:49 PM

There has to be something wrong with the returned "Active" word from the formula as it should still work.

Does this work?

Please Login or Register  to view this content.

confirmed with CSE key combination

**smalldoll** · 07-21-2009, 05:54 PM

It works but I cant seem to copy the formula down the column properly for each row

**NBVC** · 07-23-2009, 10:00 AM

If that worked then you should first check that the formula that returned the string "Active" does not have "Active " or " Active" instead (ie. extra trailing or leading space) and fix that. The use the original formula.

To copy the formula down, after first confirming it with the CSE key combination at the beginning, activate the cell with the formula, and click and drag down the little black square at bottom right corner of the cell.