I have two lists - 'List 1' and 'List 2'. List 1 is the total dataset and it contains all the entries in List 2. Is there a simple way of indicating which entries in List 1 are also included in List 2 by comparing certain criteria? List 1 List 2 DateType£ DateType£01/01/2020Apples1.00 01/01/2020Apples2.0001/01/2020Apples2.00 02/01/2020Apples1.0002/01/2020Pears3.00 03/01/2020Pears4.0002/01/2020Pears4.00 04/01/2020Bananas2.0002/01/2020Apples1.00 02/01/2020Apples2.00 02/01/2020Bananas2.00 03/01/2020Pears3.00 03/01/2020Pears4.00 03/01/2020Apples1.00 03/01/2020Apples2.00 04/01/2020Bananas2.00 I want to match the categories - Date,Type AND £ and highlight in List 1 the entries from List 2 Desired output List 1 Entry included in List 2DateType£ 01/01/2020Apples1.00 01/01/2020Apples2.00Y02/01/2020Pears3.00 02/01/2020Pears4.00 02/01/2020Apples1.00Y02/01/2020Apples2.00 02/01/2020Bananas2.00 03/01/2020Pears3.00 03/01/2020Pears4.00Y03/01/2020Apples1.00 03/01/2020Apples2.00 04/01/2020Bananas2.00Y For context, actual List 1 contains 35k entries and List 2 contains 25k entries. I've tried the 'Join' tool but it only results in the matched entries only. I still want the full data from List 1 - ie not just matched entries. I also tried just joining by a single category (eg type) but that results in a very high number of outputs (35k x 25k) I tried the 'Union' tool but that just adds List 2 entries to the bottom of List 1. Not what I wanted. I was thinking of using the Formula tool - ie IF List1 date = List2 date AND List1 type = List2 type AND List1 £ = List2 £ THEN "Y" ELSE "" - but Formula tools only accept single inputs. I don't really want to combine the data, just highlight the relevant entries in List 1

Identify comparable entries in two datasets

I have two lists - 'List 1' and 'List 2'. List 1 is the total dataset and it contains all the entries in List 2. Is there a simple way of indicating which entries in List 1 are also included in List 2 by comparing certain criteria?

List 1			List 2
Date	Type	£	Date	Type	£
01/01/2020	Apples	1.00	01/01/2020	Apples	2.00
01/01/2020	Apples	2.00	02/01/2020	Apples	1.00
02/01/2020	Pears	3.00	03/01/2020	Pears	4.00
02/01/2020	Pears	4.00	04/01/2020	Bananas	2.00
02/01/2020	Apples	1.00
02/01/2020	Apples	2.00
02/01/2020	Bananas	2.00
03/01/2020	Pears	3.00
03/01/2020	Pears	4.00
03/01/2020	Apples	1.00
03/01/2020	Apples	2.00
04/01/2020	Bananas	2.00

I want to match the categories - Date,Type AND £ and highlight in List 1 the entries from List 2

Desired output
List 1			Entry included in List 2
Date	Type	£
01/01/2020	Apples	1.00
01/01/2020	Apples	2.00	Y
02/01/2020	Pears	3.00
02/01/2020	Pears	4.00
02/01/2020	Apples	1.00	Y
02/01/2020	Apples	2.00
02/01/2020	Bananas	2.00
03/01/2020	Pears	3.00
03/01/2020	Pears	4.00	Y
03/01/2020	Apples	1.00
03/01/2020	Apples	2.00
04/01/2020	Bananas	2.00	Y

For context, actual List 1 contains 35k entries and List 2 contains 25k entries.

I've tried the 'Join' tool but it only results in the matched entries only. I still want the full data from List 1 - ie not just matched entries. I also tried just joining by a single category (eg type) but that results in a very high number of outputs (35k x 25k)

I tried the 'Union' tool but that just adds List 2 entries to the bottom of List 1. Not what I wanted.

I was thinking of using the Formula tool - ie IF List1 date = List2 date AND List1 type = List2 type AND List1 £ = List2 £ THEN "Y" ELSE "" - but Formula tools only accept single inputs. I don't really want to combine the data, just highlight the relevant entries in List 1

Join

Accepted answers

Qiu

@raj8257
Because the "+" can only work with string type of Data fields, and we need to convert those numeric fields before concatanating them.

I modified the workflow now to be more dynamic to adapt the number of columns changes.

1121-raj8257-R1.yxmd

All comments

DeanWest

@raj8257

Here is a proposed solution. You could perform a left outer join in order to achieve the sample output you provided.

In order to perform the left outer join, you would just need to join by the three fields and then unionize the "L" (Left/Left Unjoin) output anchor and the "J" (Join/Inner Join) output anchor. Please refer to the snippet and example workflow attached below:

Please let me know if you need any more assistance 🙂

Left-Outer-Join_example..yxmd

Qiu

@raj8257

How about a little out of box thinking here.

1121-raj8257.yxmd

raj8257

Hi @Qiu Thanks for the response. The actual dataset includes a number of columns to add together. I'm trying to add a numerical value like in the example to a number of text strings, but when I add the value the formula doesn't work

I'm guessing it's because the data type is string even though i'm adding in a number value but I'm not sure.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335