Adding Criteria to Unique Distinct List

J

Jnrrpg11

New Member
Joined
Jun 29, 2017
Messages
28
Hello Everyone,

I hope you can help with issue i have with a unique distinct list.

I have a list of job numbers in Column C
I have them marked as Complete/Incomplete in Column D
I have them marked as Routine/Non-Routine in Column G

I want to make a unique list of job numbers that are both incomplete and Routine.
The Formula I have is returning job numbers that are either incomplete or Routine.

=INDEX('(System) Country 1'!$C$5:$C$5000,MATCH(0,IF(('(System) Country 1'!$D$5:$D$5000="Incomplete")+('(System) Country 1'!$G$5:$G$5000="Routine"),COUNTIF($H$5:$H5,'(System) Country 1'!$C$5:$C$5000)),0))

I know i have to modify the section in blue perhaps with adding an AND function but i cannot get it to work.
I did try adding AND and changing the blue + to a , but i only got an #N/A error

If anyone has any suggestions it would be greatly appreciated.


Thanks as Always and Kind Regard
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Hello Mukshy,

Thanks for replying. Ive put together an example of what the data looks like.
The sheet compiles this from a massive raw data web export.

[TABLE="width: 1526"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Job No[/TD]
[TD]Status[/TD]
[TD]Period[/TD]
[TD]Quarter[/TD]
[TD]Routine/Non[/TD]
[TD]Subtotal Local[/TD]
[TD]Sub Total USD[/TD]
[TD]Trade[/TD]
[TD]Category[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]758452[/TD]
[TD]Completed[/TD]
[TD]P11[/TD]
[TD]Q4[/TD]
[TD]Routine[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]JANITORIAL SERVICES-OTHER[/TD]
[TD]MAINTENANCE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]455646[/TD]
[TD]Completed[/TD]
[TD]P11[/TD]
[TD]Q4[/TD]
[TD]Routine[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]FLOOR MAINTENANCE-STONE[/TD]
[TD]MAINTENANCE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1165146[/TD]
[TD]Completed[/TD]
[TD]P11[/TD]
[TD]Q4[/TD]
[TD]Routine[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]JANITORIAL-GLASS STOREFRONT CLEANING[/TD]
[TD]MAINTENANCE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5468133[/TD]
[TD]Completed[/TD]
[TD]P11[/TD]
[TD]Q4[/TD]
[TD]Routine[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]JANITORIAL-GLASS STOREFRONT CLEANING[/TD]
[TD]MAINTENANCE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1131547[/TD]
[TD]Completed[/TD]
[TD]P11[/TD]
[TD]Q4[/TD]
[TD]Routine[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]JANITORIAL SERVICES-DAILY[/TD]
[TD]MAINTENANCE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]897451[/TD]
[TD]Incomplete[/TD]
[TD]P11[/TD]
[TD]Q4[/TD]
[TD]Routine[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]PLUMBING-PM[/TD]
[TD]MAINTENANCE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]215466[/TD]
[TD]Incomplete[/TD]
[TD]P11[/TD]
[TD]Q4[/TD]
[TD]Routine[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]JANITORIAL SERVICES-DAILY[/TD]
[TD]MAINTENANCE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1254863[/TD]
[TD]Incomplete[/TD]
[TD]P12[/TD]
[TD]Q4[/TD]
[TD]Routine[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]JANITORIAL SERVICES-OTHER[/TD]
[TD]MAINTENANCE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1154877[/TD]
[TD]Incomplete[/TD]
[TD]P12[/TD]
[TD]Q4[/TD]
[TD]Routine[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]JANITORIAL-GLASS STOREFRONT CLEANING[/TD]
[TD]MAINTENANCE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]16548752[/TD]
[TD]Incomplete[/TD]
[TD]P12[/TD]
[TD]Q4[/TD]
[TD]Routine[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]HVAC-PM[/TD]
[TD]MAINTENANCE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2356464[/TD]
[TD]Incomplete[/TD]
[TD]P12[/TD]
[TD]Q4[/TD]
[TD]Routine[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]JANITORIAL SERVICES-DAILY[/TD]
[TD]MAINTENANCE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1254564[/TD]
[TD]Incomplete[/TD]
[TD]P12[/TD]
[TD]Q4[/TD]
[TD]Routine[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]HVAC-PM[/TD]
[TD]MAINTENANCE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]21321654[/TD]
[TD]Incomplete[/TD]
[TD]P12[/TD]
[TD]Q4[/TD]
[TD]Routine[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]JANITORIAL SERVICES-DAILY[/TD]
[TD]MAINTENANCE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]13265452[/TD]
[TD]Completed[/TD]
[TD]P10[/TD]
[TD]Q4[/TD]
[TD]Non-Routine[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]ELECTRICAL-GENERAL[/TD]
[TD]REPAIRS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]21315462[/TD]
[TD]Completed[/TD]
[TD]P10[/TD]
[TD]Q4[/TD]
[TD]Non-Routine[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]ELECTRICAL-GENERAL[/TD]
[TD]REPAIRS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]21321654[/TD]
[TD]Completed[/TD]
[TD]P10[/TD]
[TD]Q4[/TD]
[TD]Non-Routine[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]ELECTRICAL-GENERAL[/TD]
[TD]REPAIRS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]21654663[/TD]
[TD]Completed[/TD]
[TD]P10[/TD]
[TD]Q4[/TD]
[TD]Non-Routine[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]DISPLAY FIXTURE[/TD]
[TD]REPAIRS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]85484651[/TD]
[TD]Incomplete[/TD]
[TD]P10[/TD]
[TD]Q4[/TD]
[TD]Non-Routine[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]SERVICE CALLS OTHER[/TD]
[TD]REPAIRS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2164683[/TD]
[TD]Completed[/TD]
[TD]P10[/TD]
[TD]Q4[/TD]
[TD]Non-Routine[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]LIGHTING REPAIR[/TD]
[TD]REPAIRS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1321654[/TD]
[TD]Completed[/TD]
[TD]P10[/TD]
[TD]Q4[/TD]
[TD]Non-Routine[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]AUDIO VISUAL REPAIRS[/TD]
[TD]WARRANTY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


As an example i would like the list to include Jobs 897451 through to 21321654 as they are both Incomplete and Routine ignoring all other numbers as they don't hit both these criteria.
I should point out that the Job No is in Column C of the table and that column A and B are both blank.

I hope this helps.
 
Upvote 0
You did not say where to put the list, so I put it in Column A starting at Row 2...
Code: 
[table="width: 500"]
[tr]
	[td]Sub GetIncompleteRoutineJobNumbers()
  Dim LastRow As Long, List As Variant
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Range("A2:A" & LastRow) = Evaluate(Replace("IF((D2:D#=""Incomplete"")*(G2:G#=""Routine""),C2:C#,"""")", "#", LastRow))
  Range("A2:A" & LastRow).SpecialCells(xlBlanks).Delete xlShiftUp
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
Let's call '(System) Country 1'!$C$5:$C$5000 Jobs; '(System) Country 1'!$D$5:$D$5000 Cstatus, and '(System) Country 1'!$G$5:$G$5000 Dstatus.

In H4 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(jobs=""),IF(Cstatus="incomplete",IF(Dstatus="routine",MATCH(jobs,jobs,0)))),ROW(jobs)-ROW(INDEX(jobs,1,1))+1),1))

In H5 control+shift+enter, not just enter, and copy down:

=IF(ROWS($H$5:H5)>$H$4,"",INDEX(jobs,SMALL(IF(FREQUENCY(IF(1-(jobs=""),IF(Cstatus="incomplete",IF(Dstatus="routine",MATCH(jobs,jobs,0)))),ROW(jobs)-ROW(INDEX(jobs,1,1))+1),ROW(jobs)-ROW(INDEX(jobs,1,1))+1),ROWS($H$5:H5))))
 
Upvote 0
Or this macro

Code: 
Sub aTest()
    Dim dic As Object, vData As Variant, i As Long
    
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = vbTextCompare
    With Sheets("(System) Country 1")
        vData = .Range("C5:G" & .Cells(.Rows.Count, "C").End(xlUp).Row).Value
        For i = LBound(vData, 1) To UBound(vData, 1)
            If vData(i, 2) = "Incomplete" And vData(i, 5) = "Routine" Then dic(vData(i, 1)) = Empty
        Next i
    End With
    
    'List in Sheet1 beginning in H6 <--Adjust
    Sheets("Sheet1").Range("H6").Resize(dic.Count) = Application.Transpose(dic.keys)
End Sub

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

drom
  • Question Question
SUMPRODUCT to count dependent validation list column with wrong items
Replies
1
Views
263
drom
drom
M
  • Solved
Optimizing my rows to send e-mail to different address
Replies
2
Views
169
MasterBash
M
J
  • Question Question
Filtering Results in List Box
Replies
2
Views
117
JohnS_323
J
S
  • Question Question
Workbook Index of Sheet name Hyperlinks + additional information
Replies
1
Views
304
stuartgb100
S
A
  • Question Question
List of distinct names
Replies
3
Views
87
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,907
Messages
6,175,301
Members
452,633
Latest member
DougMo

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