Need formula - Urgent

J

jeetusaini85

Board Regular
Joined
Aug 9, 2013
Messages
131
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,
 
Sort by date Sort by votes
Or to follow your format:
In a1
[TABLE="width: 372"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Sr. No.[/TD]
[TD]Firm Name[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]ABC Pvt. Ltd.[/TD]
[TD]Received[/TD]
[TD]Received[/TD]
[/TR]
[TR]
[TD]ABC Pvt. Ltd.[/TD]
[TD]Received[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ABC Pvt. Ltd.[/TD]
[TD]Received[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ABC Pvt. Ltd.[/TD]
[TD]Received[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ABC Pvt. Ltd.[/TD]
[TD]Received[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ABC Pvt. Ltd.[/TD]
[TD]Received[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ABC Pvt. Ltd.[/TD]
[TD]Received[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ABC Pvt. Ltd.[/TD]
[TD]Received[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ABC Pvt. Ltd.[/TD]
[TD]Received[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ABC Pvt. Ltd.[/TD]
[TD]Received[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ABC Pvt. Ltd.[/TD]
[TD]Received[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Pending[/TD]
[TD]Pending[/TD]
[/TR]
[TR]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Received[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Received[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Received[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Received[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Received[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Received[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Received[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Received[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Received[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XYZ Pvt. Ltd.[/TD]
[TD]Received[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Formula in C2 is =IF(A2=A1,"",IF(IFERROR(INDEX($B$2:$B$23,MATCH(1,("Pending"=$B$2:$B$23)*($A2=$A$2:$A$23),0)),"")="Pending","Pending",IFERROR(INDEX($B$2:$B$23,MATCH(1,("Received"=$B$2:$B$23)*($A2=$A$2:$A$23),0)),""))) Ctrl + Shift + Enter not just enter on a PC or Command + Return on a MAC.
Copied down till C23.

Would that be close to what you needed?
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
cyrilbrd said:
Hi,
not sure if this would be acceptable but you could create a summary in C1 such as:
[TABLE="width: 240"]
<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.
Click to expand...


[TABLE="width: 85"]
<colgroup><col width="85"></colgroup><tbody>[TR]
[TD="width: 85"]Hi Cyrilbird,[/TD]
[/TR]
</tbody>[/TABLE]

First of all thanks for reply, tried your formula but only show blank cells instead of result.

I think my clarification regarding criteria of formula is not clear. Let me explain again:

1. Column "A" having serial numbers.
2. There are lots of firms in column "B" with same name as a firm contain lots of docs type. like: Firm ABC Pvt. Ltd. is entered from B2:B12.
3. Column "C" having status of docs either they received or pending which entered manually.
4. Column "D" required a formula which will check that if the status marked as "Received" then it will mark a status "Received" in column "D" anywhere in D2:D12 only one time.
5. It will show "Pending" status anywhere in D2:D12 only one time If "Pending" status mark between C2:C12 even single status mark as "Pending" and rest are mark as "Received".

21ooci8.jpg


Thanks
 
Upvote 0
Hi..

I know you wanted a formula.. but in any case.. here's a VBA solution that works..

Code: 
Private Sub CommandButton1_Click()
    Dim FR As Long, x As Long, Rng As Range
    Application.ScreenUpdating = False
    
    With CreateObject("scripting.dictionary")
            For Each it In Sheets("Sheet1").Columns(2).SpecialCells(2).Offset(1)
                x0 = .Item(it.Value)
            Next
            Z = .keys
        End With
        
    For j = LBound(Z) To UBound(Z) - 1
    With Range("A1:D" & Range("C" & Rows.Count).End(xlUp).Row)
        .AutoFilter 2, Z(j)
        FR = ActiveSheet.AutoFilter.Range.Offset(1, 0).Resize(.Rows.Count).SpecialCells(12).Row
        .AutoFilter 3, "<>Received"
        Set Rng = Sheets("Sheet1").AutoFilter.Range
                x = Rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
                If x >= 1 Then
        Cells(FR, 4).Value = "Pending"
        Else
        Cells(FR, 4).Value = "Recieved"
        End If
        .AutoFilter
    End With
    Next j
    Application.ScreenUpdating = True
End Sub

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:48px;" /><col style="width:85px;" /><col style="width:64px;" /><col style="width:106px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:61px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Sr. No.</td><td >Firm Name</td><td >Status</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1</td><td >ABC Pvt. Ltd.</td><td >Received</td><td >Recieved</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">2</td><td >ABC Pvt. Ltd.</td><td >Received</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">3</td><td >ABC Pvt. Ltd.</td><td >Received</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">4</td><td >ABC Pvt. Ltd.</td><td >Received</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">5</td><td >ABC Pvt. Ltd.</td><td >Received</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">6</td><td >ABC Pvt. Ltd.</td><td >Received</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">7</td><td >ABC Pvt. Ltd.</td><td >Received</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">8</td><td >ABC Pvt. Ltd.</td><td >Received</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">9</td><td >ABC Pvt. Ltd.</td><td >Received</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">10</td><td >ABC Pvt. Ltd.</td><td >Received</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">11</td><td >ABC Pvt. Ltd.</td><td >Received</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">12</td><td >XYZ Pvt. Ltd.</td><td >Received</td><td >Pending</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">13</td><td >XYZ Pvt. Ltd.</td><td >Received</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">14</td><td >XYZ Pvt. Ltd.</td><td >Received</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">15</td><td >XYZ Pvt. Ltd.</td><td >Pending</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">16</td><td >XYZ Pvt. Ltd.</td><td >Received</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">17</td><td >XYZ Pvt. Ltd.</td><td >Received</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">18</td><td >XYZ Pvt. Ltd.</td><td >Received</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">19</td><td >XYZ Pvt. Ltd.</td><td >Received</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">20</td><td >XYZ Pvt. Ltd.</td><td >Received</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="text-align:right; ">21</td><td >XYZ Pvt. Ltd.</td><td >Received</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="text-align:right; ">22</td><td >XYZ Pvt. Ltd.</td><td >Received</td><td > </td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
 
Last edited:
Upvote 0
In D2 copied down:

=IF(COUNTIF(B$2:B2,B2)=1,IF(COUNTIFS(B$2:B$23,B2,C$2:C$23,"Received")=COUNTIF(B$2:B$23,B2),"Received","Pending"),"")
 
Upvote 0
Hey Andrew, can you please help me little bit more on this:

=IF(COUNTIF(B$2:B2,B2)=1,IF(COUNTIFS(B$2:B$23,B2,C$2:C$23,"Received")=COUNTIF(B$2:B$23,B2),"Received",IF(COUNTIF(B$2:B2,B2)=1,IF(COUNTIFS(B$2:B$23,B2,C$2:C$23,"Pending")=COUNTIF(B$2:B$23,B2),"Pending",IF(COUNTIF(B$2:B2,B2)=1,IF(COUNTIFS(B$2:B$23,B2,C$2:C$23,"Waived")=COUNTIF(B$2:B$23,B2),"Waived","-"))))))

Want "Waived" status also same as "Received" and "Pending"

Thanks..
 
Upvote 0
Like this?

=IF(COUNTIF(B$2:B2,B2)=1,IF(COUNTIFS(B$2:B$23,B2,C$2:C$23,"Received")=COUNTIF(B$2:B$23,B2),"Received",IF(COUNTIFS(B$2:B$23,B2,C$2:C$23,"Pending")=COUNTIF(B$2:B$23,B2),"Pending","Waived")),"")
 
Upvote 0
Thanks Andrew,

Formula works and i already tried this, but i want when status "Waived" mark then only it will show "Waived" else it will show "-".

Thanks
 
Upvote 0
Hey Andrew,

A little clarification on this:

If all the status of a particular firm is "Received" then it will show "Received",
If all the status of a particular firm is "Received" and one or two status is "Pending" then it will show "Pending",
If there is any other status mark apart from "Received" and "Pending" then it will show "-".
Forget about "Waived", i'll handle it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
count how many times text string appears in column
Replies
2
Views
197
ajm
A
R
  • Question Question
VBA Question - VBA for validating value from multiple cells/column and fetching value another sheet's cell and pasting it another cell of sheet
Replies
2
Views
312
Kaper
Kaper
ukbulldog001
  • Question Question
Macro to concatenate rows to single cell
Replies
5
Views
437
ukbulldog001
ukbulldog001
B
  • Question Question
Formula for count unique value in large data with one criteria?
Replies
5
Views
617
Peter_SSs
Peter_SSs
B
  • Question Question
How to make this IF formula work?
Replies
1
Views
242
Fluff
Fluff

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top