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

[teziboy]

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

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

So that helpers do not have to manually type sample data:
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.

Could you simply adjust your formula instead of resorting to vba?

22 03 20.xlsm
	A	B	C	D	E	F	G
1
2	1/01/2022	119883	1300
3	1/01/2022	119883				207372	1300
4	1/01/2022	119883				181685	1300
5	1/01/2022	207372	1300			119882	1300
6	1/01/2022	207372				119883	1300
7	1/01/2022	207372
8	1/01/2022	207372
9	1/01/2022	181685	1300
10	1/01/2022	181685
11	1/01/2022	181685
12	1/01/2022	181685
XLOOKUP

Cell Formulas
Range	Formula
C2:C12	C2	=IF(AND(A2=A1,B2=B1),"",XLOOKUP(B2,F$3:F$6,G$3:G$6))

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Delete duplicate value for same date and other look up value.
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[teziboy]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

So that helpers do not have to manually type sample data:
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.

Could you simply adjust your formula instead of resorting to vba?

22 03 20.xlsm
	A	B	C	D	E	F	G
1
2	1/01/2022	119883	1300
3	1/01/2022	119883				207372	1300
4	1/01/2022	119883				181685	1300
5	1/01/2022	207372	1300			119882	1300
6	1/01/2022	207372				119883	1300
7	1/01/2022	207372
8	1/01/2022	207372
9	1/01/2022	181685	1300
10	1/01/2022	181685
11	1/01/2022	181685
12	1/01/2022	181685
XLOOKUP

Cell Formulas
Range	Formula
C2:C12	C2	=IF(AND(A2=A1,B2=B1),"",XLOOKUP(B2,F$3:F$6,G$3:G$6))

Thank you so much Peter

 

[teziboy]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Delete duplicate value for same date and other look up value.
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Hi Fluff,

Duly noted.
The post is also at Delete duplicate value for same date and other look up value.