Delete duplicate value for same date and other looked up value.

Topic

New Reply

Dear All,

I was trying to delete the duplicate values which comes from XLOOKUP formula but is same for several dates.
For example, in attached file, In column C, HCFT comes from the Xlookup on Column B.
However, the value in Column C is repetitive for the same date.
I am looking for a VBA to remove the repetitive HCFT only (not all the rows) for the same date and Vehicle number.
My objective is to remove the colored values using VBA in Column C since those are equals to same date same vehicle.
Could you please help me to sort the problem?
Thanks in advance.

Shotez

Reply | Quote

Replies

I think the attached does what you want. Unzipping attachment will give xlsm file and you may have to enable macros when you open it. Not sure how much you understand macros, etc but you can see the macro by pressing F11.

Regards

1 user thanked author for this post.

shazzad.arla

Reply | Quote

Hi Btbs,

You are absolutely on point.

Thanks for your effort and time.

 

Shazzad

Reply | Quote

You’re welcome, glad I could help.

Reply | Quote

Hi Shazzad

I am looking for a VBA to remove the repetitive HCFT only (not all the rows) for the same date <span style=”text-decoration: underline;”>and Vehicle number</span>.

That vba assumes your data is pre-sorted by Date, then by Vehicle No., then by HCFT.

If you had multiple vehicles with the same HCFT it wouldn’t work because the vba doesn’t check for the same vehicle number (so you couldn’t use that if you had multiple vehicles with the same HCFT).

So another way of doing this, without using any vba, is to use conditional formatting.

The data would still have to be pre-sorted by by Date, then by Vehicle No., then by HCFT.

The conditional formatting (using ‘white text color’ and  No-Color-fill) would be based on a formula:

Code:

=AND(a2=A1, B2=B1, C2=C1)

..and with the Applies-to range set for =$C$2:$C$43 in your example file

..see attached file

zeddy

Delete-duplicate-zeddy

1 user thanked author for this post.

shazzad.arla

Reply | Quote

Hi Zeddy,

You have put more logic on the problem. And I must say your solution is much simpler. Thanks a lot.

Shazzad

Reply | Quote