Multiple If functions to compare cells and paste in separate cells help

[CDaviess]

Im stuck at getting a macro to filter through some data. I have this data set;

This continues for 1000s of rows, i need a macro to:
Go through date/time col
check for any overlaps
where there are overlaps compare to ocular hazard col
if both dates have ocular hazard 1 then only paste one of them
if both dates have ocular hazard 2 then only paste one of them
if dates have different ocular hazard then paste row containing 2

then create a new worksheet with the new data in it.
I just cant get anything to work as i get so caught up in loops and IF statements.

Any help would be greatly appreciated!

Cheers,

 

[DanteAmor]

Hi @CDaviess. Thanks for posting on the forum.

In order to better understand the problem, could you help me by explaining the following:

check for any overlaps

What do you mean by "overlap" could you explain it with an example?

if both dates have ocular hazard 1 then only paste one of them Give an example.
if both dates have ocular hazard 2 then only paste one of them Give an example.
if dates have different ocular hazard then paste row containing 2 Give an example.

then create a new worksheet with the new data in it. Give an example.

Please, explain with examples what you need, although you put an image, you are not explaining which records should be copied and which not.


Note XL2BB:
It would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture. This way you will have more opportunities to receive help.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.


--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[CDaviess]

Hi @CDaviess. Thanks for posting on the forum.

In order to better understand the problem, could you help me by explaining the following:


What do you mean by "overlap" could you explain it with an example?


Please, explain with examples what you need, although you put an image, you are not explaining which records should be copied and which not.


Note XL2BB:
It would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture. This way you will have more opportunities to receive help.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.


--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

Hi Dante,
So for example cell A2 contains the date stamp 2022-03-17 17:57:00 but Cell A980 may also contain that same date.
I would then need the macro to where there is two dates that are the same look at column D and compare the ocular hazard of the two date stamps.
The Ocular Hazard column can only contain the values 1 or 2.

If the date stamps are the same, prioritise the row with the ocular hazard value of 2. However if they are both the same (1 or 2) then only one of the rows with the date stamp needs to be copied.

All rows need to be assessed and copied into a new worksheet within the workbook.


Unfortunately I am on my work computer and cant use XL2BB, will try and help to understand anyway i can!

Many thanks

 

[DanteAmor]

You can use the functionality of excel.
Copy your data on sheet2.
Sorts the data by DateStamp (oldest to newest). Adds another level of sorting by the Ocular Hazard # field (highest to lowest - this is very important).
Select the data, Click Data Menu, Remove Duplicates, and then Under Columns, check only DateStamp and press ok.

Review the result and tell me.

 

[CDaviess]

You can use the functionality of excel.
Copy your data on sheet2.
Sorts the data by DateStamp (oldest to newest). Adds another level of sorting by the Ocular Hazard # field (highest to lowest - this is very important).
Select the data, Click Data Menu, Remove Duplicates, and then Under Columns, check only DateStamp and press ok.



View attachment 89637

Review the result and tell me.

HI Dante,

I tried that and that's brilliant!
Do you know how to automate that in VBA?

Many thanks

 

[CDaviess]

HI Dante,

I tried that and that's brilliant!
Do you know how to automate that in VBA?

Many thanks

Ive managed to get it to work in VBA thank you