I need some help please

[catalincoman]

Hi,
Please if someone can help, I have a big table that contains 4 columns and approx. 5000 rows. First column has the date, second has a ID number, third has a serial number and fourth has a type of activity. I need somehow to sort and check if on the same serial number there are multiple same type activities in a defined time interval ( for example: one month interval ) but having the ID number different. If this criteria is not meet then delete all the other rows.
Maybe you can help me with a VBA or some formulas that can do the above requirements.

Example:

1​	Act. Start Date	ID Number	Serial Nr	Activity Type
2​	2022.05.07	123456​	120b547	y09
3​	2022.01.15	658699​	55i698	r04
4​	2022.11.17	123456​	120b547	y09
5​	2022.05.18	6985633​	120b547	y09
6​	2022.05.10	455822​	120b547	z02
7​	2022.06.03	985896​	120b547	y09
8​	2022.09.23	4589987​	120b547	y09

In the above table the serial number 120b547 appears multiple times but only row 2, 5 and 7 meet the criteria. all the other should be deleted.

Thank you in advance for for your help and support.

BR,
Catalin

 

[jdellasala]

Hi,
Please if someone can help, I have a big table that contains 4 columns and approx. 5000 rows. First column has the date, second has a ID number, third has a serial number and fourth has a type of activity. I need somehow to sort and check if on the same serial number there are multiple same type activities in a defined time interval ( for example: one month interval ) but having the ID number different. If this criteria is not meet then delete all the other rows.
Maybe you can help me with a VBA or some formulas that can do the above requirements.

Example:

1​	Act. Start Date	ID Number	Serial Nr	Activity Type
2​	2022.05.07	123456​	120b547	y09
3​	2022.01.15	658699​	55i698	r04
4​	2022.11.17	123456​	120b547	y09
5​	2022.05.18	6985633​	120b547	y09
6​	2022.05.10	455822​	120b547	z02
7​	2022.06.03	985896​	120b547	y09
8​	2022.09.23	4589987​	120b547	y09

In the above table the serial number 120b547 appears multiple times but only row 2, 5 and 7 meet the criteria. all the other should be deleted.

Thank you in advance for for your help and support.

BR,
Catalin

What is the criteria for keeping those rows. There are other rows where the Serial Nr is 120b547 and the ID Number is 123456, likewise the Activity Type y09. There is apparent reason for selecting "rows" 2, 5, and 7.
Offhand I'd say convert the data into an Excel Table and use Power Query to filter out the unwanted rows. I'd demonstrate, but as I said I don't know what the parameters are.
Also, PLEASE use XL2BB. The unformatted tables have an issue as they add a CODE 63 to the end of numeric data making it a LOT of work to use.

 

[catalincoman]

What is the criteria for keeping those rows. There are other rows where the Serial Nr is 120b547 and the ID Number is 123456, likewise the Activity Type y09. There is apparent reason for selecting "rows" 2, 5, and 7.
Offhand I'd say convert the data into an Excel Table and use Power Query to filter out the unwanted rows. I'd demonstrate, but as I said I don't know what the parameters are.
Also, PLEASE use XL2BB. The unformatted tables have an issue as they add a CODE 63 to the end of numeric data making it a LOT of work to use.

I tried to install the XL2BB add-in but I have a trust security issue and it keeps disable the macros. So unfortunately is not working for me as I have no rights to change that.

Regarding your question, I'm interested to find out if for all the serial numbers I have in the table, if there are some repetitive activities in a time frame that could be 1M, 3M or 6 Month depending on what I'm choosing. So, in the example I gave, there is this serial number 120b547 appearing multiple times. I am now interested if for this serial number there are multiple, same type activities appearing in one month. So I notice that there are 5 entries of Y09 activities for this serial number, but in a one month interval there are only 3 entries and all of them are having different ID numbers. All the other are in more then one month interval so they should not be taken into account. If for example, from this 3 entries, 2 of them are having the same id number just one should be taken into account.

Imagine that I have 5000 rows with more then 1000 different serial numbers and I have to check it one by one. I want to find a solution to make this sorting and filtering job much easier or automatic if possible and just from the entire table to keep only the rows that are proving the criteria as in the example I gave.