How to Summarise both Full Match and Starts With Match for the same value?

[Mert]

Hi, I would like to explain my analytical problem with a simple example below. Thank you in advance for your help.

My first table

Field 1

ABC985

ABC98501

ABC98502

ABC98503

XYZ12345

My second table

Field 1

ABC985

ABC98501

I would like to count for each value in table 2 how many times it (full or starts with) matches with one or more value in table 1. The desired output table is below:

Field 1	Full Match	Starts with
ABC985	1	3
ABC98501	1	0

ABC985 (from table 2) has 1 Full Match because it matches itself in table 1.

ABC985 (from table 2) has 3 Starts With Match because it matches 3 other rows (ends with 01/02/03) from a starts with point of view.

ABC98501 (from table 2) has 1 Full Match because it matches itself in table 1.

ABC98501 (from table 2) has 0 Starts With Match because no row in table 1 has a value starting with ABC98501.

I hope that my ask and explanation is clear enough. Thank you again!

[binuacs · Accepted solution]

@Mert a batch macro approach

[OllieClarke · Accepted solution]

Hi @Mert 

Depending on your data size, this approach doesn't use macros, but does cross join your data sets together, so it won't scale well if your data is big

Hope that helps,

Ollie

[Mert]

@binuacs and @OllieClarke thank you. Both solutions have been very helpful.