Need formula - Urgent

[jeetusaini85]

Hello there,

Need your help to resolve my query. I need a formula for given below criteria:

I have two column, in 1st column i have some firm name and in 2nd there are status of the names like pending, received etc. Now the problem is, in 1st column firm names are in multiple rows with status. I need a formula in which :

If all the status of a firm is "Received" it should mark "Received" in front of a single name of the firm and if there is any other status with "Received" it should mark "Pending". Like:

Column A Column B Column C
Firm name Status New Status
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received Received
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received -

ABC Pvt. Ltd. - Pending -
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received Pending
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received -
ABC Pvt. Ltd. - Received -


Thanks,

 

[Pup Denab]

HI
If you sort the data, first by firm name, then status, a simple vlookup in column C will give you Pending or Received
=VLOOKUP(A1,A:B,2,0)

 

[jeetusaini85]

Hey Pup, thanks for reply.

Your formula shows only "Received" status in front of the all cases (firms), but i need that if there are all status of a single firm is "Received" then it should show "Received" and if there is any status apart form "Received" of a single firm then it should show is "Pending".

 

[Pup Denab]

ABC Pvt. Ltd. - Received - Received Received
ABC Pvt. Ltd. - Received - Received
ABC Pvt. Ltd. - Received - Received
ABC Pvt. Ltd. - Received - Received
ABC Pvt. Ltd. - Received - Received
ABC Pvt. Ltd. - Received - Received
ABC Pvt. Ltd. - Received - Received
ABC Pvt. Ltd. - Received - Received
ABC Pvt. Ltd. - Received - Received

CABC Pvt. Ltd. - Pending - Pending Pending
CABC Pvt. Ltd. - Received - Pending
CABC Pvt. Ltd. - Received - Pending
CABC Pvt. Ltd. - Received - Pending
CABC Pvt. Ltd. - Received - Pending
CABC Pvt. Ltd. - Received - Pending
CABC Pvt. Ltd. - Received - Pending
CABC Pvt. Ltd. - Received - Pending
CABC Pvt. Ltd. - Received - Pending




HI again
Couple of questions, did you sort the data ?, are all the company names the same, if so, can their be separated by date,
above is the results of the formula i gave you this morning, but this will only work if you sort the data column C
The new formula in column D, will hide any results, son you only see the first result, this is why you need to sort the data (place this in cell D2)
=IF(B2="","",IF(A2=A1,"",VLOOKUP(A2,A:B,2,0)))

however if this is not what you need, please post a larger sample data, with more criteria, then I'm sure we can help

 

[jeetusaini85]

Hi Pup,

I do not want to sort the data. please check below the image of sample data, maybe this will help you to understand what i want:

Unrendered image removed.

Thanks

 

[jeetusaini85]

Sorry, trying to upload an image...

 

[jeetusaini85]

[TABLE="class: grid, width: 509"]
<tbody>[TR]
[TD]Sr. No.
[/TD]
[TD]Firm Name[/TD]
[TD]Status[/TD]
[TD]Required Status[/TD]
[TD]Remarks[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ABC Pvt. Ltd.[/TD]
[TD]Received
[/TD]
[TD]Received[/TD]
[TD]If all the status of a particular firm (B2:B12) are "Received" then it should show "Received"[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ABC Pvt. Ltd.[/TD]
[TD]Received
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ABC Pvt. Ltd.[/TD]
[TD]Received
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]ABC Pvt. Ltd.[/TD]
[TD]Received
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]ABC Pvt. Ltd.[/TD]
[TD]Received[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]ABC Pvt. Ltd.[/TD]
[TD]Received
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]ABC Pvt. Ltd.[/TD]
[TD]Received[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]ABC Pvt. Ltd.[/TD]
[TD]Received[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]ABC Pvt. Ltd.[/TD]
[TD]Received[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]ABC Pvt. Ltd.[/TD]
[TD]Received[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]ABC Pvt. Ltd.[/TD]
[TD]Received[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Pending[/TD]
[TD]Pending[/TD]
[TD]If there is any single status show apart from "Received" of a particular firm (B13:B23) then it should show "Pending"[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Received[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Received[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Received[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Received[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Received[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Received[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Received[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Received[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Received[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Received[/TD]
[/TR]
</tbody>[/TABLE]


Thanks

 

[moxeve]

=IF(B1="Received", "Received",IF(B1="Pending", "Pending","False"))

Type this formula,

 

[jeetusaini85]

Hi Moxeve,

Thanks but this is not what i want, Firm names can be same in multiple rows. Basically this is required for a MIS in which there are lots of type of docs of a particular firm and if they all are received then it will mark as "Received" and if any single docs is pending and others all received it will mark as "Pending".

In column A there is Firm name, in column B there is docs received status which enter manually, In column C required formula to check the above criteria.

Thanks

 

[cyrilbrd]

Hi,
not sure if this would be acceptable but you could create a summary in C1 such as:
[TABLE="width: 240"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Remarks[/TD]
[TD]Pending[/TD]
[TD]Received[/TD]
[/TR]
[TR]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Pending[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC Pvt. Ltd.[/TD]
[TD][/TD]
[TD]Received[/TD]
[/TR]
</tbody>[/TABLE]

formula in D1 is =IFERROR(INDEX($B$2:$B$23,MATCH(1,(D$1=$B$2:$B$23)*($C2=$A$2:$A$23),0)),"") Ctrl + Shift + Enter not just enter on a PC or Command + Return on a MAC.
formula in E1 is =IF($D2=D$1,"",IFERROR(INDEX($B$2:$B$23,MATCH(1,(E$1=$B$2:$B$23)*($C2=$A$2:$A$23),0)),"")) Ctrl + Shift + Enter not just enter on a PC or Command + Return on a MAC.