Need assistance with lists and finding a function, list of functions, or macros to help me search those lists for criteria for a ruleset project.

Question

Hello all,

I have a list of items, all with number IDs, the list shows what recipe they are all associated with along with the date those recipes were made. There are a few million Items and recipes. I had a ruleset that I would filter out items under a certain age, but now, I am realizing I need to filter out Items that have a recipe under a certain age. If the list was smaller I would just look in groups of the single item and all the recipes, but since I can have 400 rows per 1 item ID, its going to need something more automatic. I have a column that shows the date the recipe was created, so I was wondering if there's a way to incorporate a If with a count, and some lookup to count the number of recipes a Single item is in and then the amount of recipes that were created a couple of years ago so I can get a list of Items no longer in use recently. I figure the first column I can add is Count of Items by Item number, and then the next column I would have would be the count of dates, per the item number, over like 4 years.

Answer 1

Hi,

Based on the data that you have shared, show the expected result very clearly.

Answer 2

ECC_MaterialNumber	ECC_CreateDate	PO_EXIST	Recipe_Create_Date	Older than 4 Years?	OBJECTTYPE	RECIPE_NAM
33701	4/21/2006	N	4/28/2007	Y	RECIPE	6E+19
33713	4/21/2006	N	4/28/2007	Y	RECIPE	6E+19
33713	4/21/2006	N	4/28/2007	Y	RECIPE	6E+19
90251	4/21/2006	N	4/28/2007	Y	RECIPE	6E+19
90254	4/21/2006	N	4/26/2007	Y	RECIPE	6E+19
90254	4/21/2006	N	4/28/2023	N	RECIPE	6E+19
90385	4/21/2006	N	4/22/2007	Y	RECIPE	6E+19
90385	4/21/2006	N	4/26/2007	Y	RECIPE	6E+19
90385	4/21/2006	N	4/26/2007	Y	RECIPE	6E+19
90385	4/21/2006	N	4/28/2007	Y	RECIPE	6E+19
90385	4/21/2006	N	4/28/2007	Y	RECIPE	6E+19
90385	4/21/2006	N	4/28/2007	Y	RECIPE	6E+19
90385	4/21/2006	N	4/29/2007	Y	RECIPE	6E+19
90385	4/21/2006	N	4/29/2007	Y	RECIPE	6E+19
90385	4/21/2006	N	4/29/2007	Y	RECIPE	6E+19
90385	4/21/2006	N	4/29/2007	Y	RECIPE	6E+19
90385	4/21/2006	N	4/29/2007	Y	RECIPE	6E+19
90385	4/21/2006	N	4/29/2007	Y	RECIPE	6E+19
90385	4/21/2006	N	4/29/2007	Y	RECIPE	6E+19
90385	4/21/2006	N	4/29/2007	Y	RECIPE	6E+19
90385	4/21/2006	N	10/6/2008	Y	RECIPE	6E+19
90385	4/21/2006	N	11/26/2009	Y	RECIPE	6E+19
90385	4/21/2006	N	1/19/2024	N	RECIPE	6E+19

Just a small portion of this data set. I have a count on another sheet in the document that counts each Material Number from a list I copied from here and removed the duplicates. I would be looking for help with creating a formula or macro that would look at that no duplicate list on the other sheet for the Material number its looking for, then look at this sheet, in column E (Older than 4 Years), and give me the count of Y or N, either would be fine as if count of Y I compare to the count of Material Numbers, and I am just thinking of this as I am typing this, or if it is No I guess I can just have a formula not needing the count of the material numbers on the other sheet, just the Material numbers its looking for and then the amount of Ns and if there's at least one I can filter those out from my delete list.

Answer 3

Hi there

We definitely can help you,

Could you share a sample of your data with us by copying and pasting a few table rows with all the corresponding column headers in your next reply?

Regards

Jeovany