SOLVED

Need Help sorting Data

Copper Contributor

I need help sorting data by order date. I have over 100K lines in two columns with one showing the part number and the other showing the order date. I want to know how many instances there were where we went 2 years or more in-between the purchase of an item. 

8 Replies
best response confirmed by Grahmfs13 (Microsoft)
Solution

@fellw1jr 

This solution uses sorting and conditional formatting.

1. Sort by Part (A to Z) then by Order Date (Old to New).

2. Use conditional formatting w/formula to identify instances where the part ordered has been 2+ years since previous order:

=AND($A2=$A1,DATEDIF($B1,$B2,"y")>=2)

 

Bless your heart, i've been trying to figure out how to do this for hours today. A million thank yous!!!!!!
You're welcome!

@fellw1jr 

An alternative could be Power Query.

purchase date item.JPG

Quick follow up. With that formula being used, it is highlighting the order from 2 years ago. How can i tweak the formula so it highlights the order that was placed after its been two years? @Patrick2788 

The formula is concerned with orders greater than or equal to 2 years.

Yes and that is what i want. I am just looking for the formula to highlight the order that is greater than or equal to two years. Currently it is highlighting the older of the orders. Below I've tried to show an example...currently it is highlighting the blue row, i want it to highlight the orange row which is the order two years or greater in the future. I'm sorry for nagging you about this, I really am appreciative of your help here. The file I have has 300K thousand lines which makes this a huge help if i can get this to work.

 

2/7/2016---AE5554C

2/7/2016---AE5554C

9/7/2017---AE5554C

4/8/2018---AE5554C

11/10/2014---AE6664B

11/13/2013---AE1101B
12/17/2017---AE1101B

2/10/2018---AE1101B

2/10/2018---AE1101B

2/10/2018---AE1101B

 

@Patrick2788 

 

@fellw1jr 

That makes sense. Here's the revision to the formula:

=OR(IFERROR(AND($A2=$A1,DATEDIF($B1,$B2,"y")>=2),0),IFERROR(AND($A2=$A3,DATEDIF($B2,$B3,"y")>=2),0))

 

1 best response

Accepted Solutions
best response confirmed by Grahmfs13 (Microsoft)
Solution

@fellw1jr 

This solution uses sorting and conditional formatting.

1. Sort by Part (A to Z) then by Order Date (Old to New).

2. Use conditional formatting w/formula to identify instances where the part ordered has been 2+ years since previous order:

=AND($A2=$A1,DATEDIF($B1,$B2,"y")>=2)

 

View solution in original post