• 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.

Excel formula multiple criterias

R

Rodrigues

Member
All
I'm working on a file where reports dates, orders and products, and what I need is that, a formula to look at the date and display results in two separate columns, one for the order and other for the products, attached is a sample file.
Conditions:
If an order number finds more than one product, display the product on a separated column with products separated by comma;
If multiple orders are found then each one needs to be shown separately with respective products.

Ignore duplicates.

Many thanks in advance.
R
 

Attachments

Try………….

1] Helper N8, formula copy down :

=IF(COUNTIFS(D$8:D8,D8,F$8:F8,F8,H$8:H8,H8)=1,D8&" "&IFERROR(INDEX(N9:N$39,INDEX(MATCH(F8&H8,F9:F$39&H9:H$39,),)),""),"")

2] Output Table "Order no" R15 array formula copy down :

=IFERROR(INDEX(H$8:H$39,SMALL(IF(FREQUENCY(IF((F$8:F$39=Q15)*(H$8:H$39<>""),MATCH(H$8:H$39,H$8:H$39,0)),ROW(A$1:A$32)),ROW(A$1:A$32)),COUNTIF(Q$15:Q15,Q15))),"")

p.s. array formula to be confirm entered with CTRL+SHIFT+ENTER 3 keystrokes together.

3] Output Table "Code no" S15 formula copy down :

=IFERROR(INDEX(N$8:N$39,INDEX(MATCH(Q15&R15,F$8:F$39&H$8:H$39,),)),"")

Regards
Bosco
 

Attachments

You must log in or register to reply here.
Back
Top