Create a list based on whether a condition was met

[dkrzysik]

I am attempting to create a list from another list in excel based on whether a specific condition was met or not. Basically, column A has a person's name, column B has their amount, column C has their goal, and column D states "met" or "not met". I want to create a separate list that lists the individuals who show up as "not met". I'm guessing this is a relatively simple fix but I have been toying for a few hours. Thanks for your help!

[Pete_UK]

Assuming your data starts on row 2, with a header row above, put this formula in E2:

Please Login or Register  to view this content.

then copy this down to the bottom of your data. Then in G2 you can have this formula:

Please Login or Register  to view this content.

which you can then copy down as far as you need to in order to get your list of individuals who have "met" the criteria.

Hope this helps.

Pete

EDIT: For a list of individuals who have "not met" the criteria, just change the first formula to this:

Please Login or Register  to view this content.

Hope this helps

Pete

[Fotis1991]

Assuming that your data start in row 2(first row is Headers) then in a cell(Let's say H2), put this Array(confirm this using control+shift+enter-not just enter) formula.

Please Login or Register  to view this content.

Copy down and across.

[Tony Valko]

Here's my chance to see how the file attachment tool works...

dkrzysik.xls

Description...

Names in column A
Status in column D

Select a Status from the drop down in cell G1.

Enter this array formula** in F2 and copy down until you get blanks:

=LOOKUP("zzz",CHOOSE({1,2},"",INDEX(A:A,SMALL(IF(D$2:D$15=G$1,ROW(D$2:D$15)),ROWS(F$2:F2)))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[Tony Valko]

Here's my chance to see how the file attachment tool works...

Attachment 203333

Hmmm...

Why isn't my attachment formatted to look like the attachment in this post:

http://www.excelforum.com/excel-form...339&viewfull=1

[FDibbins]

Tony, your attachement in post #4 looks, and works, just fine

[Tony Valko]

I got a PM from another member and they explained the difference in how the attachment can appear.

Thanks!

[Pete_UK]

Hmmm...

Why isn't my attachment formatted to look like the attachment in this post:

Biff,

(it seems strange having people refer to you as Tony)

If you want your attachment to appear at the bottom of your post then when you click on Go Advanced, scroll down to Manage Attachments rather than use the paperclip icon, then Add Files, Select Files (and navigate to your file), then Done, then Submit Post.

Hope this helps.

Pete

[limebaish]

Great post Tony Can I go one step further and ask you how you would do this:

Make a column next to Name and call it Age. Enter Age values for each one. Now how could you make the list match the criteria that you have already stated but pull in the Age alongside the name?

Ideally the end result would give you a list of names with the age of e.g. Name 1 with it.

Many thanks to anyone who can solve this one!

[Pete_UK]

@limebaish

Please read the Forum Rules at the top of the screen. You should not ask a question in someone else's thread - start your own instead.

Pete

[Tony Valko]

OK, got it Pete.

Thanks!

[limebaish]

Sorry - I will do as you have suggested. Thanks

[dkrzysik]

THanks everyone! Toni, I ended up using your attachment. Works great!

[Tony Valko]

You're welcome. Thanks for the feedback!

[Fotis1991]

.....................