Show list of customers that have not ordered

[jeff_miller]

I have 4 reports that I get
The data in these reports is simple, It contains one column with the customer code and a second column with an order entry date
The 4 reports are
Orders for the last two years
Orders for the last Month
Orders for the last 3 weeks
Orders for the last two weeks

I would like to drop all four of these reports into a different tab on a worksheet and add a fifth tab that would list the following
Customers who have ordered in the last two years but not in the last two weeks
Customers who have ordered in the last two years but not in the last three weeks
Customers who have ordered in the last two years but not in the last One Month

Any ideas?
I dont want to use Conditional formatting and highlighting as I would have to manually look for the customers who had not ordered for that time period.

 

[oldbrewer]

[TABLE="width: 935"]
<colgroup><col span="4"><col><col span="5"><col><col><col span="2"></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]
[TD][/TD]
[TD][/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]
[TD][/TD]
[TD][/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]
[TD][/TD]
[TD][/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]
[TD][/TD]
[TD][/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]
[TD][/TD]
[TD][/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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]orders in[/TD]
[TD]orders in[/TD]
[TD]customer[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]last[/TD]
[TD]last[/TD]
[TD]names[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2 years[/TD]
[TD]2 weeks[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]code[/TD]
[TD]order date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust1[/TD]
[TD="align: right"]01/06/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust2[/TD]
[TD="align: right"]03/07/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD]cust2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust3[/TD]
[TD="align: right"]04/08/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD]cust3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust4[/TD]
[TD="align: right"]05/09/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD]cust4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust5[/TD]
[TD="align: right"]07/10/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust6[/TD]
[TD="align: right"]08/11/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust7[/TD]
[TD="align: right"]10/12/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD]cust7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust8[/TD]
[TD="align: right"]11/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust9[/TD]
[TD="align: right"]12/02/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust10[/TD]
[TD="align: right"]16/03/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust1[/TD]
[TD="align: right"]17/04/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust2[/TD]
[TD="align: right"]19/05/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust3[/TD]
[TD="align: right"]20/06/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust4[/TD]
[TD="align: right"]22/07/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust5[/TD]
[TD="align: right"]23/08/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust6[/TD]
[TD="align: right"]24/09/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]the table identifies firstly[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust7[/TD]
[TD="align: right"]26/10/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]customers who have ordered[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust8[/TD]
[TD="align: right"]27/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]in last 2 years[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust9[/TD]
[TD="align: right"]29/12/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]then secondly who has ordered[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust10[/TD]
[TD="align: right"]30/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]in last 2 weeks[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust1[/TD]
[TD="align: right"]03/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust2[/TD]
[TD="align: right"]04/11/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]thus 4 customers are highlighted[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust3[/TD]
[TD="align: right"]05/11/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust4[/TD]
[TD="align: right"]06/11/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]easy to add 2 more columns[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust5[/TD]
[TD="align: right"]07/11/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]for last 3 and 4 weeks[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust6[/TD]
[TD="align: right"]08/11/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust1[/TD]
[TD="align: right"]09/11/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust8[/TD]
[TD="align: right"]10/11/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust9[/TD]
[TD="align: right"]11/11/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cust10[/TD]
[TD="align: right"]12/11/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jeff_miller]

Thanks oldbrewer.

What I am hoping for is a 4th tab on the spreadsheet that would have one area that would look at the list of orders for the last two years and then compare that to the list of orders for the last two weeks, and report back a list of customers who have ordered in the last two years, but not in the last two weeks. The then a few rows down I would have another list of customers who have ordered in the last two years but not in the last three weeks, and then the same thing again a few rows down for the customers who have not ordered in the last month.

 

[Fluff]

Maybe something like

Sub Test()
   Dim Cl As Range
   Dim Yr As Worksheet, Ows As Worksheet, Sht As Worksheet
   Dim i As Long
   
   Set Yr = Sheets("[COLOR=#ff0000]2years[/COLOR]")
   Set Ows = Sheets("[COLOR=#ff0000]Output[/COLOR]")
   Ows.Range("A1:C1").Value = Array("2Weeks", "3Weeks", "4Weeks")
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Yr.Range("A2", Yr.Range("A" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Empty
      Next Cl
      For Each Sht In Sheets(Array("[COLOR=#ff0000]2week[/COLOR]", "[COLOR=#ff0000]3week[/COLOR]", "[COLOR=#ff0000]Month[/COLOR]"))
         For Each Cl In Sht.Range("A2", Sht.Range("A" & Rows.Count).End(xlUp))
            If .Exists(Cl.Value) Then .Remove Cl.Value
         Next Cl
         Ows.Range("A2").Offset(, i).Resize(.Count).Value = Application.Transpose(.Keys)
         i = i + 1
      Next Sht
   End With
End Sub

 

[jeff_miller]

Thank you Fluff
I have worked minimally with VBA
Are the red areas places where I would replace that in the code with the actual tab names?

 

[Fluff]

Yes they are, I remembered to highlight them, but forgot to say why

 

[jeff_miller]

Thanks, I will give this a try when I have some time! Really appreciate it!!

 

[Fluff]

You're welcome.

 

[jeff_miller]

Hello Fluff,

I am getting the same results in column B and C (3Weeks and 4Weeks)
The data in sheet "Last Three weeks" has about 35 more records than the data in "Last Month".
This is the code that I am using

Sub Test()
Dim Cl As Range
Dim Yr As Worksheet, Ows As Worksheet, Sht As Worksheet
Dim i As Long

Set Yr = Sheets("Last two years")
Set Ows = Sheets("Report")
Ows.Range("A1:C1").Value = Array("2Weeks", "3Weeks", "4Weeks")

With CreateObject("scripting.dictionary")
For Each Cl In Yr.Range("A2", Yr.Range("A" & Rows.Count).End(xlUp))
.Item(Cl.Value) = Empty
Next Cl
For Each Sht In Sheets(Array("Last two weeks", "Last Three weeks", "Last Month"))
For Each Cl In Sht.Range("A2", Sht.Range("A" & Rows.Count).End(xlUp))
If .Exists(Cl.Value) Then .Remove Cl.Value
Next Cl
Ows.Range("A2").Offset(, i).Resize(.Count).Value = Application.Transpose(.Keys)
i = i + 1
Next Sht
End With


Is there a way I could share the workbook with you so you could see what I am looking at?
again, appreciate the help!

 

[Fluff]

You cannot upload to the site, but you can upload to a share site such as OneDrive, DropBox, GoogleDrive. Mark for sharing & then post the link to the thread.