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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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

J
  • Question Question
Copying Rows Based on Column Value
Replies
1
Views
180
jolivanes
jolivanes
J
  • Question Question
VBA or formula to move data in last used column to column A
Replies
5
Views
228
Peter_SSs
Peter_SSs
drom
  • Question Question
SUMPRODUCT to count dependent validation list column with wrong items
Replies
1
Views
277
drom
drom
M
  • Solved
Optimizing my rows to send e-mail to different address
Replies
2
Views
178
MasterBash
M
T
  • Solved
How can I filter duplicates from a worksheet column out of a data validation list drop-down box?
Replies
5
Views
241
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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