Comparing 1 Row to a Group of Rows

[Brendan007]

Hello,

I have a master list of data and a bunch of smaller lists. The data in the master should contain all of the data in the smaller lists.

I need a formula to return a yes or no if a row in the master is represented in the smaller list. Think about it this way: I'm trying to check if a row of data in the master matches exactly in the smaller list it's being compared to.

I've added a photo explaining more.

 

[Brendan007]

Here's the photo link: https://ibb.co/iOUKUd

 

[Robby19]

Very crude, but this should get you what you need.

Copy/Paste in J as far down as you need.

You will have to change Sheet1 and Sheet2 to your specific needs.

=IFERROR(IF(AND(INDEX(Sheet2!A:A,MATCH(Sheet1!A2,Sheet2!A:A,0))=Sheet1!A2,INDEX(Sheet2!B:B,MATCH(Sheet1!B2,Sheet2!B:B,0))=Sheet1!B2,INDEX(Sheet2!C:C,MATCH(Sheet1!C2,Sheet2!C:C,0))=Sheet1!C2,INDEX(Sheet2!D:D,MATCH(Sheet1!D2,Sheet2!D:D,0))=Sheet1!D2,INDEX(Sheet2!E:E,MATCH(Sheet1!E2,Sheet2!E:E,0))=Sheet1!E2,INDEX(Sheet2!F:F,MATCH(Sheet1!F2,Sheet2!F:F,0))=Sheet1!F2,INDEX(Sheet2!G:G,MATCH(Sheet1!G2,Sheet2!G:G,0))=Sheet1!G2,INDEX(Sheet2!H:H,MATCH(Sheet1!H2,Sheet2!H:H,0))=Sheet1!H2,INDEX(Sheet2!I:I,MATCH(Sheet1!I2,Sheet2!I:I,0))=Sheet1!I2),"Yes",""),"No")

 

[Aladin Akyurek]

Images/photos are not useful as they cannot be read into Excel.

Sheet1

a,b,3,3/2/18
a,b,7,4/2/18

Sheet2

d,c,3,3/2/18
a,b,3,3/2/18
x,y,5,3/2/18
a,b,7,4/2/18


In E2 of Sheet2 enter and copy down:

=TEXTJOIN("|",TRUE,$A2:$D2)

Now we can do the check...

In d2 of Sheet1 enter and copy down:

=ISNUMBER(MATCH(TEXTJOIN("|",TRUE,$A2:$D2),Sheet2!$E:$E,0))

 

[Peter_SSs]

Aladin, a couple of comments:

1. I think a typo near the end of your post. I think you mean In E2 of Sheet1, not D2.

2. I believe that the 2nd argument of both your TEXTJOIN functions should be FALSE, not TRUE. Otherwise, although it may not be possible with the OP's data, but to be sure

a, , 3,3/2/18 in one sheet would match with a,3, , 3/2/18 in the other since, with TRUE, both TEXTJOIN functions would yield "a|3|43134" (last value depending on date system of course)

 

[Aladin Akyurek]

Peter,

Good catch on both counts. Thanks.
I trust the first is easily detectible.
The second is not admissible, that is, TRUE instead of FALSE. Rather a thoughtless injection.

 

[Peter_SSs]

@Brendan007
Given the variety of type of data in your two sheets & lack of blanks among your data, I think you could avoid any helper column.
Notes
- The inclusion of the blank column J in my formula reference to the range in sheet 'LIST 1' is an added safety measure.
- The TEXTJOIN function is only available in the very latest version(s) of excel

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
2	15/06/2018 8:33	BUY	SPY	276	20-Jun-18	PUT	4	0.98	-392
3	15/06/2018 11:56	BUY	SPY	276	20-Jun-18	PUT	5	0.87	-435
4	13/06/2018 13:04	BUY	SPY	279	22-Jun-18	CALL	7	1.04	-728
5	14/06/2018 8:53	BUY	SPY	279	22-Jun-18	CALL	4	0.9	-360
6	20/12/2017 15:00	Buy	SPY	266.5	DEC 22 2017	C	10	1.05	-1050	Yes
7	20/12/2017 13:09	Buy	SPY	266.5	DEC 22 2017	C	6	1.25	-750
8	20/12/2017 11:35	Buy	FB	177.5	DEC 22 2017	C	6	1.4	-840
SHEET 1