Flagging Unique and Duplicate values via VBA

[Kiwihiker]

I have a dataset example - see below. I want to be able to parse the dataset (by VBA, as there may be thousands of records, unless it can be formularised?)) and determine whether a Product (Column B in the attachment) is unique (i.e. does not occur in any other row in a specific column) or, if not unique, flag if the duplicate is the latest instance based on the highest numeric value within a transaction ID (column D). Note that there may be hundreds of duplicates for a given Product.

In a new column (Column G in the attachment), I would like to return TRUE where a Product is unique (i.e. does not occur in any other record) , OR if it is the latest instance of a Product (based on the numeric portion of the transaction ID which are the numbers following the 'TR-' value). Otherwise, return FALSE for that record.

I have manually populated (in Column F) the logiccal result I would like to achieve by code in Column G.

For context, I will use this value as part of an INDEX MATCH criteria from another worksheet to report on the latest instance of a given Product (where part of the MATCH will be whether the cell value is TRUE for a given row).

Cheers

Gerry

FindLatestDuplicate.xlsx
	A	B	C	D	E	F
1	AnotherColumn	Product	YetAnotherColumn	TransactionID	AndAnotherColumn	Unique/Latest
2	Unimportant value	Apple	Unimportant value	TR-10001	Unimportant value	FALSE
3	Unimportant value	Banana	Unimportant value	TR-10002	Unimportant value	FALSE
4	Unimportant value	Apricot	Unimportant value	TR-10003	Unimportant value	TRUE
5	Unimportant value	Pear	Unimportant value	TR-10004	Unimportant value	TRUE
6	Unimportant value	Apple	Unimportant value	TR-1000001	Unimportant value	TRUE
7	Unimportant value	Banana	Unimportant value	TR-10006	Unimportant value	FALSE
8	Unimportant value	Orange	Unimportant value	TR-10007	Unimportant value	FALSE
9	Unimportant value	Banana	Unimportant value	TR-10008	Unimportant value	TRUE
10	Unimportant value	Apple	Unimportant value	TR-100999	Unimportant value	FALSE
11	Unimportant value	Apple	Unimportant value	TR-100998	Unimportant value	FALSE
12	Unimportant value	Orange	Unimportant value	TR-100997	Unimportant value	TRUE
Sheet1

 

[JamesCanale]

MrExcelPlayground5.xlsx
	A	B	C	D	E	F	G
1	AnotherColumn	Product	YetAnotherColumn	TransactionID	AndAnotherColumn	Unique/Latest	Unique/Latest
2	Unimportant value	Apple	Unimportant value	TR-10001	Unimportant value	FALSE	FALSE
3	Unimportant value	Banana	Unimportant value	TR-10002	Unimportant value	FALSE	FALSE
4	Unimportant value	Apricot	Unimportant value	TR-10003	Unimportant value	TRUE	TRUE
5	Unimportant value	Pear	Unimportant value	TR-10004	Unimportant value	TRUE	TRUE
6	Unimportant value	Apple	Unimportant value	TR-1000001	Unimportant value	TRUE	TRUE
7	Unimportant value	Banana	Unimportant value	TR-10006	Unimportant value	FALSE	FALSE
8	Unimportant value	Orange	Unimportant value	TR-10007	Unimportant value	FALSE	FALSE
9	Unimportant value	Banana	Unimportant value	TR-10008	Unimportant value	TRUE	TRUE
10	Unimportant value	Apple	Unimportant value	TR-100999	Unimportant value	FALSE	FALSE
11	Unimportant value	Apple	Unimportant value	TR-100998	Unimportant value	FALSE	FALSE
12	Unimportant value	Orange	Unimportant value	TR-100997	Unimportant value	TRUE	TRUE
Sheet4

Cell Formulas
Range	Formula
G2:G12	G2	=OR(COUNTIF($B$2:$B$12,B2)=1,VALUE(RIGHT(D2,LEN(D2)-3))=SUMPRODUCT(VALUE(RIGHT($D$2:$D$12,LEN($D$2:$D$12)-3)),--($B$2:$B$12=B2),--(VALUE(RIGHT($D$2:$D$12,LEN($D$2:$D$12)-3))=(MAX(VALUE(RIGHT($D$2:$D$12,LEN($D$2:$D$12)-3))*($B$2:$B$12=B2))))))

 

[Kiwihiker]

Brilliant - thank you!