Excel 2016: looking up multiple items over 2 worksheets and reporting back true or false 2

[pendle666]

Hello

I've two reports detailing absences.

The first is the 'clean' report with the correct items, around 600 rows. An example is:

[tt]Number Type StartDate EndDate Hours
123456 LSL 30/07/2018 30/07/2018 7.6
123456 LSL 31/07/2018 31/07/2018 7.6
123456 LSL 01/08/2018 01/08/2018 7.6
987654 RDO 30/07/2018 30/07/2018 7.6
987654 RDO 31/07/2018 31/07/2018 7.6
456654 RDO 21/08/2018 21/08/2018 7.6
456654 RDO 22/08/2018 22/08/2018 7.6
456654 RDO 23/08/2018 23/08/2018 7.6[/tt]



The other report is very big at over 20,000 rows, but some of those rows will be the same as the above.

What I need to do is match these, but because there is no one single identifier, vlookup isn't an option. Basically, I need to look at each line and see if it's replicated in the other worksheet. If it is, then return TRUE, if that line can't be found, then return FALSE.

Is someone able to point me in the right direction? I'd prefer to use a formula if possible.

thanks


thank you for helping

____________
Pendle

 

[combo]

VLOOKUP solution:
Create helper columns with concatenated data (with & operator: =A2&B@ for instance) and apply VLOOKUP.

Power query (get and transform in excel 2016) solution:
Create tables from your data, start query with bigger table, join second table, use join type according to required output, expand joined table, process data as you need, output to new sheet.
It's really worth to invest some time and learn this new feature. It is a part of excel business intelligence, together with power pivot (professional version), in my opinion, the biggest improvement in excel since implementing VBA.

combo

 

[SkipVought]

Hi,

I made your table a Structured Table (Insert > Tables > Table) Named tABS1.

I made the other table a Structured Table named tABS2.

I added a column after the Hours column with this formula...
[tt]
=IFERROR(MATCH([@Number]&[@Type]&[@StartDate]&[@EndDate]&[@Hours]
,tABS2[Number]&tABS2[Type]&tABS2[StartDate]&tABS2[EndDate]&tABS2[Hours],0)>0,"")
[/tt]
...and made it an ARRAY FORMULA by hitting SHIFT+CNTR+ENTER

Took me about 10 minutes to do the entire test.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pendle666]

 

[pendle666]

Hi Skip

I've followed your instructions and created the tables and then copy/pasted your formula into Column F on the 'clean sheet'.

Although I did Shift Control Enter to make it an array formula, I didn't "see" anything happen when I did this and the formula had automatically been copied down the table.

But the results are blank. I know that there are entries in the big sheet which will match.

I'm not sure where I've gone wrong.

Thanks



thank you for helping

____________
Pendle

 

[SkipVought]

Would not know without seeing your workbook. Plz upload.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pendle666]

 

[SkipVought]

You did not enter the formula as an ARRAY formula!!!

Your workbook returned.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pendle666]

Oh I thought I had - the Shift Control Enter?

Oh well, I've learned something new today

Thank you so much for your help as always.

thank you for helping

____________
Pendle

 

[pendle666]

Combo, thank you for your suggestion. This particular piece is time-driven, but certainly what you suggest looks interesting and worthy of a decent amount of time to learn.

thank you for helping

____________
Pendle