• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

conditions

A

ahhhmed

Member
Hello allk I need your help:

I have columns A, B, C, D, E

There are products in Col. A,

there are prices in either B or C

I want all the items whose prices are in B to be listed in D

I want all the items whose prices are in C to be listed in E


Any ideas?
 
Why not simply copy columns B:C to D:E? Is there something besides prices in the columns?

Maybe this in col D:

=IF(ISNUMBER(B2),B2,"")

Copy this over to col E, then as far down as needed.
 
To put it more clearly:


I have list of pupils in A, some are passing, and their marks are in (B), and some are failing and their grades are in (c).


I want to list the passing pupils in (d) with no gaps between them.

I want to list the failing pupils in (e) with no gaps between them.


That's all.
 
This is an array formula (use Ctrl+Shift+Enter to confirm). In D2:

=IF(ROWS(D$1:D1)>COUNT(B:B),"",INDEX($A:$A,SMALL(IF(B$2:B$100<>"",ROW(B$2:B$100)),ROW(D1))))

Adjust the B2:B100 range size as necessary, then copy over to col e and down as far as you would like. Unused formula cells will display "blanks".
 
Thanx Srinidhi,

I'll start it over:

I have columns A, B, C, D and E

All pupils' names are in A

B and C contain marks: Column B for the passing pupils and C are for the failing pupils

I need Excel 2007 to group all the passing pupils in D and all he failing pupils in E.

I want the names in D and E with no empty rows among them, ie in one running list each.
 
ahhhmed,

Did the array formula not work? Granted, if the marks are words/letters, you'll need to change the COUNT function to COUNTA.
 
Luke M,

Will you please explain me how the array formula works and what it does? because I did not get the desired results using it. Thanks dear friend.
 
After copying the formula into cell D2, hold down Ctrl+Shift, then press Enter to confirm the formula. If done correctly, you should now see some curly brackets around the formula.

You can then copy it to E2, and then down as far as you think should be needed to display all the records.


The formula uses an IF function to determine all the rows that meet the criteria you're looking for (grade in col B/C). It then takes those row numbers and uses them with the INDEX function to return the names of the students that correlate. Once all the records have been displayed, the formula evaluates to "".


What results are you getting?
 
Thanks luke,

I have uploaded a sample file. please download it and see what can be done.


http://www.2shared.com/file/S0rzCwWM/conditions.html
 
Hi ahhhmed,


Do you care if the data is sorted or do you need to maintain the order in which the data is presented in each column.


As an example, if the values in Column B are:

100

80

<blank>

90

90


Would it be acceptable for column D to show?

100

90

90

80


Also, what about duplicate values? In the example above, would you want 90 to show twice or just once?
 
Whoops - finally got a look at your sheet (firewall at work blocked it). I'll get back to you with an answer soon
 
Another question :)


Is this really something that needs to be automated?


If not, the following works:

1) Turn on an auto-filter

2) Filter on column B <> blank

3) Highlight the names in column A; copy & paste

4) Remove filter on column B

5) Filter on column C <> blank

6) Highlight the names in column A; copy & paste


This is a pretty quick manual solution that should work effectively. What do you think?
 
If you don't mind having helper columns or the order of the results, here's an "automated" solution:


https://skydrive.live.com/redir.aspx?cid=88e8d8d4c7be2057&resid=88E8D8D4C7BE2057!103


How it works:

- Column I is a helper column for the passing grades. for each entry in passing grades, it appends ".X" where "X" is the instance of this score. This helps us delineate, for example, between two people who scored 11

- Column D then (in order of highest score to lowest) lists the students with entries in the passing grade column. Large(H:H, ROW()-1) grabs the Nth largest item in the helper column. We then use Match to determine which item in the "PassHelper" list is the Nth largest item and then use offset on the names array to grab the name. We repeat this for all valid items (using IF(ROW()-1<=COUNT(C:C)..., "") to stop once we've exceeded the number of passing grades).


Columns I and E use the same concept for failing grades
 
You must log in or register to reply here.
Back
Top