Multiple criteria condition forumla

[Li_zzza]

Hi everyone,

I'm hoping someone here can help me (after spending hours of researching, I cannot find the solution to this).

I am working on a project around tracking certain items being sent out to certain providers and monitoring if we get anything back from the providers. However, the items are being sent out to multiple providers and sometimes one provider. I need a formula or a condition that calculates if everything is returned by the providers or provider it will say "complete" under the received all column.

Example:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item[/TD]
[TD]Provider 1[/TD]
[TD]Provider 2[/TD]
[TD]Provider 3[/TD]
[TD]Received all?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sock[/TD]
[TD]Received[/TD]
[TD]Sent[/TD]
[TD]Received[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Shirts[/TD]
[TD][/TD]
[TD]Received[/TD]
[TD]Received[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Computers[/TD]
[TD]Received[/TD]
[TD][/TD]
[TD]Sent[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Laptops[/TD]
[TD]Received[/TD]
[TD]Received[/TD]
[TD]Received[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Shoes[/TD]
[TD]Received[/TD]
[TD][/TD]
[TD]Received[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Currently, I have the formula in E2 as =If(and(B2="Received",D2="Received"),"Complete","") but it disregards cell C2. Is there a way to add C2 into the formula, but disregard if it is empty or says the word sent?

 

[Li_zzza]

Yes it does, but I was hoping it will ignore any text in between as well. In between the dates, there are some comments for notes for the product.

Shoes Received 23/10/2017 Good Sent 4/10/2017 Received 25/10/2017...etc.

I appreciate all of the help & sorry for making this complicated.

 

[JackDanIce]

Unless you explain your set up exactly, without ability to see your PC monitor, it's just repeated guesswork at what you need when you're not providing the full picture.

Example, you're showing the stuff above with "comments for notes for the product" - what cells is this data in? Did you include this in your initial example, if not why not?

Excel assumes data types, based on the data entered into cells. You can have types like date, numbers, strings, boolean and they can all be processed and evaluated differently within formula.

But without your problem properly explained with sufficient but succinct detail, such that a blind person, unable to see your screen, can still understand your problem and provide solution, it's going to be lots of trial and errors as you drip feed information about your set up. Just delays getting to a working solution I'm afraid.

 

[Murphy123]

Yes it does, but I was hoping it will ignore any text in between as well. In between the dates, there are some comments for notes for the product.

Shoes Received 23/10/2017 Good Sent 4/10/2017 Received 25/10/2017...etc.

I appreciate all of the help & sorry for making this complicated.

Hi
Is your problem spelling ?
You seem to have received and recieved in your sample data.

Regards
Murph123

 

[JackDanIce]

Thanks @Murphy123, I noticed the change from Received to Recieved but forgot to mention to check that too, hopefully that fixes the problem!

 

[Li_zzza]

Thank you for replying so quickly, however, no, I have checked and it's not a problem with the spelling. The sample data I gave is essentially a smaller version of a complicated workbook. Essentially, we are trying to keep track of what is being sent out and what has come back, however, there can be one or multiple providers for each item.

So for example, there are 5 providers that the items can be sent to. We need to remittance from each provider that the item was sent out to, to mark as completed for that item. However, there are a number of columns in between these providers to help us keep track of dates, comments, descriptions, etc. I am looking for a formula that will tell me if we were to send the items out to x-number of providers, and once 'Sent' is changed to 'Received', that it will mark the item as 'Completed'. The formula needs to work the same where we, for example, only send the item to 1 provider instead of 5 or send the items to all 5 providers.


For example (I only created 3 providers, but imagine, this goes on for 5 providers with the same columns)
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]Items[/TD]
[TD]Transaction completed? Where the formula will go[/TD]
[TD]Provider 1: Item Number[/TD]
[TD]Provider 1: Status[/TD]
[TD]Proivder 1: Date Sent[/TD]
[TD]Provider 1: Countdown date[/TD]
[TD]Proivder 1: Rejection date[/TD]
[TD]Provider 1: Comments [/TD]
[TD]Provider 2: Item number[/TD]
[TD]Provider 2: Status[/TD]
[TD]Provider 2: Date Sent[/TD]
[TD]Provider 2: Countdown Date [/TD]
[TD]Provider 2: Rejection date[/TD]
[TD]Provider 2: Comments [/TD]
[TD]Provider 3:Item Number[/TD]
[TD]Provider 3: Status[/TD]
[TD]Provider 3: Date sent[/TD]
[/TR]
[TR]
[TD]Pens[/TD]
[TD][/TD]
[TD]1454531[/TD]
[TD]Sent[/TD]
[TD]24/10/2017[/TD]
[TD]11/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Recieved [/TD]
[TD]09/10/2017[/TD]
[TD]29/09/2017[/TD]
[TD][/TD]
[TD]Still waiting on provider 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shoes[/TD]
[TD]Completed[/TD]
[TD]415451
[/TD]
[TD]Recieved [/TD]
[TD]09/09/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD]Shipping complication[/TD]
[TD]446515[/TD]
[TD]Received [/TD]
[TD]11/10/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1541
3102[/TD]
[TD]Recieved [/TD]
[TD]09/08/2017[/TD]
[/TR]
[TR]
[TD]Shirts[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1632320[/TD]
[TD]Sent[/TD]
[TD]11/06/2017[/TD]
[TD]08/08/2017[/TD]
[TD]09/08/2017[/TD]
[TD]Rejected - Need to action[/TD]
[TD]112323[/TD]
[TD]Recieved[/TD]
[TD]08/08/2017[/TD]
[/TR]
</tbody>[/TABLE]

 

[JackDanIce]

Are there always 6 columns per provider?

 

[Murphy123]

Thank you for replying so quickly, however, no, I have checked and it's not a problem with the spelling. The sample data I gave is essentially a smaller version of a complicated workbook. Essentially, we are trying to keep track of what is being sent out and what has come back, however, there can be one or multiple providers for each item.

So for example, there are 5 providers that the items can be sent to. We need to remittance from each provider that the item was sent out to, to mark as completed for that item. However, there are a number of columns in between these providers to help us keep track of dates, comments, descriptions, etc. I am looking for a formula that will tell me if we were to send the items out to x-number of providers, and once 'Sent' is changed to 'Received', that it will mark the item as 'Completed'. The formula needs to work the same where we, for example, only send the item to 1 provider instead of 5 or send the items to all 5 providers.


For example (I only created 3 providers, but imagine, this goes on for 5 providers with the same columns)
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]Items[/TD]
[TD]Transaction completed? Where the formula will go[/TD]
[TD]Provider 1: Item Number[/TD]
[TD]Provider 1: Status[/TD]
[TD]Proivder 1: Date Sent[/TD]
[TD]Provider 1: Countdown date[/TD]
[TD]Proivder 1: Rejection date[/TD]
[TD]Provider 1: Comments[/TD]
[TD]Provider 2: Item number[/TD]
[TD]Provider 2: Status[/TD]
[TD]Provider 2: Date Sent[/TD]
[TD]Provider 2: Countdown Date[/TD]
[TD]Provider 2: Rejection date[/TD]
[TD]Provider 2: Comments[/TD]
[TD]Provider 3:Item Number[/TD]
[TD]Provider 3: Status[/TD]
[TD]Provider 3: Date sent[/TD]
[/TR]
[TR]
[TD]Pens[/TD]
[TD][/TD]
[TD]1454531[/TD]
[TD]Sent[/TD]
[TD]24/10/2017[/TD]
[TD]11/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Recieved[/TD]
[TD]09/10/2017[/TD]
[TD]29/09/2017[/TD]
[TD][/TD]
[TD]Still waiting on provider 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shoes[/TD]
[TD]Completed[/TD]
[TD]415451[/TD]
[TD]Recieved[/TD]
[TD]09/09/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD]Shipping complication[/TD]
[TD]446515[/TD]
[TD]Received[/TD]
[TD]11/10/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1541
3102[/TD]
[TD]Recieved[/TD]
[TD]09/08/2017[/TD]
[/TR]
[TR]
[TD]Shirts[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1632320[/TD]
[TD]Sent[/TD]
[TD]11/06/2017[/TD]
[TD]08/08/2017[/TD]
[TD]09/08/2017[/TD]
[TD]Rejected - Need to action[/TD]
[TD]112323[/TD]
[TD]Recieved[/TD]
[TD]08/08/2017[/TD]
[/TR]
</tbody>[/TABLE]

Hi
Is there anywhere you record the number of providers you use, does it depend on the item or does it vary on some other criteria if you could insert a column to record it.

Regards
Murphy123

 

[Li_zzza]

@JackDanIce There are always 11 columns in between the providers
@Murphy123 The item get sent to what kind of provider will accept them. Sometimes more than 1 provider will accept the item, therefore we will send it to both places. We have in total 5 providers but over hundreds of items

 

[Murphy123]

@JackDanIce There are always 11 columns in between the providers
@Murphy123 The item get sent to what kind of provider will accept them. Sometimes more than 1 provider will accept the item, therefore we will send it to both places. We have in total 5 providers but over hundreds of items

Hi

I assumed 11 columns per provider so if it is 12 per provider then you will have to extend the ranges(red text)
put the formula in cell B2 and copy down

<colgroup width="162"></colgroup> <tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: left"] =IF(AND(COUNTIF(C2:BE2,"Sent")=0,COUNTIF(C2:BE2,"Received")>0),"Complete","")

You can have Sent or Received elsewhere on the row of data other than the status cells
but they must have at least one other character in the cell, they must be strictly Sent or Received in the status cells.

Regards
Murphy123

[/TD]

</tbody>