SUM(IF(FREQUENCY(IF(A$2:A$500=E2,MATCH? or VBA?

[StormyGurl]

To All & Anyone:

I have learned quite a lot from this forum and looking forward to learning more. I am looking for some help to formulate text based on 3 columns of muliple criteria.

I am working with duplicated PO Numbers column A, with line item Numbers column Band Line Item Status is column C.

An individual list of PO Numbers are created in Column E by:

=IFERROR(INDEX($A$2:$A$154, MATCH(0,IF(ISBLANK($A$2:$A$154),1,COUNTIF($E$1:E1, $A$2:$A$154)), 0)),"")

I want results in column F beside the column E so I can status 3 creierias for the PO Numbers.

I am looking for a formula, Starting in (F2) that will say:
"Complete" if all rows with the same PO Number with unique Line Items are "Received" or combined with "Cancelled" line items, in Column C
"Pending" if all rows in with the same PO number & unique Line Items are "Pending" column "C"
"Pending" if any rows in with the same PO number & unique Line Items are "Received" or Cancelled" in column "C"
"Cancelled" if all rows in with the same PO number & unique Line Items are "Cancelled" column "C"

Results wanted show in column G.

Not sure if I am explaining everything.

Unfortunatelry I get my data in a non-systematic/sturcture in cvs format.

POSS.JPG

[StormyGurl]

This is my first post. I could not attach a file.

[FlameRetired]

Hello StormyGurl. Welcome to the forum.

RE: Attachments. See if this helps.


To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

be sure to desensitize the data

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

[Bo_Ry]

Please try at F2

=IF(OR(INDEX(IF(E2=$A$2:$A$154,$C$2:$C$154)="pending",)),"Pending",IF(OR(INDEX(IF(E2=$A$2:$A$154,$C$2:$C$154)="received",)),"Complete","Cancelled"))

I don't get this part
"Pending" if any rows in with the same PO number & unique Line Items are "Received" or Cancelled" in column "C"

Is this mean only 1 "Completed" at G11 and others "Completed" are "Pending", but is against first rule.

[StormyGurl]

Thanks for the file attaching tip, FlameRetired.

I appreciate any response, especially when I misexplained myself. Sorry, Bo_Ry.

Here is my correction:

"Complete" if all rows with the same PO Number with unique Line Items are "Received" or combined with "Cancelled" line items, in Column C
"Pending" if all rows in with the same PO number & unique Line Items are "Pending" column "C"

"Pending" if any rows in with the same PO number & unique Line Items are "Received" or Cancelled" and has “Pending in column "C"

"Cancelled" if all rows in with the same PO number & unique Line Items are "Cancelled" column "C"





Please review the results of the formula in Column F


Thank you, both!!!!!!!!!!

[Bo_Ry]

Sorry, I forgot to mention that need to press Ctrl+Shift+enter when enter the formula.

Please try again at F2

Please Login or Register  to view this content.

[StormyGurl]

Awesome!!!!!!!!!!!!!!!!

Thank you very much, Bo_Ry.