Extracting a Unique List Using Complex Criteria

[LearnExcl]

Hello, I have the below sample table (the actual table contains 1000 rows):

NAME CTRL CODE
Joe X3F0F3 23
Smith Y3MGG1 12
Synder D23SD4 32
Allison A3D0FH 56
Tom X3F1F3 89
Tommy E23SD4 50
Richard H61MN0 11
Ellen Y3MGG1 22
Samuel X3MGG1 90
John L23SD4 89
Rebecca C3D0FH 73
Helen S3D2FH 21
Jennifer Z3F9F3 91

I would like to extract a unique list from the list above onto another sheet based on the whether the 2nd and 3rd characters of the CTRL numbers match "3F", "3H", "22", "3D". Based on the table above, the result should look like:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Joe[/TD]
[TD]X3F0F3[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Z3F1F3[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD]Jennifer[/TD]
[TD]X3F9F3[/TD]
[TD]91[/TD]
[/TR]
[TR]
[TD]Allison[/TD]
[TD]A3D0FH[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]Rebecca[/TD]
[TD]C3D0FH[/TD]
[TD]73[/TD]
[/TR]
[TR]
[TD]Helen[/TD]
[TD]S3D2FH[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



There are going to be cases where the list won't have any corresponding data, in case of which the results should be "".

I really appreciate your assistance with it in advance.

 

[Marcelo Branco]

I don't like the formula you've suggested - more complicated and less efficient


Try to understand the logic of the formulas i've provided. They are not complicated.
For example (formula in post 19)
This part
IF(ISNUMBER(MATCH(C$2:C$14,{56;21},0))
checks whether each value in C2:C14 matches the array {56,21} - in other words: checks whether each value in C2:C14 is equal to 56 OR equal to 21
If a match occurs the function MATCH returns a number and the function ISNUMBER returns True

In the second if
IF(D$2:D$14=1
the formula compares the values in D2:D14 against just one number (the number 1), so MATCH is not necessary - we can use a simple comparison

At last, if both conditions are met the function returns the row number (x) of the pair Cx -Dx that meet both criteria.

That said, to add a new condition to check if the values in column I are equal to Z13, ISNUMBER(MATCH(...)) is not necessary because you want to compare the values in column I with just one value (Z13).

All you have to do, is to add this condition:
IF($I$2:$I$14=$Z$13,...

Try it.

M.

 

[LearnExcl]

Got it. So then I could use your version of formula to check most everthing. How about checking for mismatches comparing two columns by two columns. For instance, if say in Sheet 1, column A houses names and column B houses dates. Sheet 2 is identical to Sheet 1 in terms of column headers, but may grow in number of rows with more names and dates every time new data are uploaded onto Sheet 2. Say that I am interested in extracting all the names (which both exist in Sheet 2 and 1) in terms of whose dates have been changed by comparing Sheet 2 to Sheet 1.

To add more clarity, say that I am looking for "if the name in Sheet2!$A$2:$A$300 exists in Sheet1!$A$2:$A$300, but the corresponding date is different in Sheet2!$B$2:$300 when compared to Sheet1$B$2:$B$300 (column B)" than give me a unique list of those names. What approach would you recommend?

 

[Marcelo Branco]

To check mismatches
Examples
Simple mismatch
A$2:A$10<>B$2

Complex mismatch (for example: A$2:A10<>"John" AND A$2:A$10<>"Peter" AND A$2:A$10<>"Richard")
ISNA(MATCH(A$2:A$10,{"John","Peter","Richard"},0))
Or you can create a list of names to exclude in say Z2:Z4 like

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

Z​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Exclude​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

John​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Peter​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Richard​

[/TD]
[/TR]
</tbody>[/TABLE]


and use
=ISNA(MATCH(A$2:A$10,Z$2:Z$4,0))

M.

 

[Marcelo Branco]

Correction
The condition should be
IF(ISNA(MATCH(A$2:A$10,Z$2:Z$4,0)),...

M.

 

[LearnExcl]

We are checking Sheet 2 against Sheet 1. Where the names match but the dates don't, then fetch the name and date Here is an example:

 [TABLE="width: 151"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Sheet 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jon[/TD]
[TD]30 Apr 2017[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]3 Jun 2017[/TD]
[/TR]
[TR]
[TD]Richard[/TD]
[TD]5 Jul 2017[/TD]
[/TR]
[TR]
[TD]Melissa[/TD]
[TD]12 Jan 2017[/TD]
[/TR]
[TR]
[TD]Ashley[/TD]
[TD]20 Feb 2017[/TD]
[/TR]
[TR]
[TD]Robert[/TD]
[TD]29 Sep 2017[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 151"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 150"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Sheet 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jon[/TD]
[TD]30 Apr 2017[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]12 Jun 2017[/TD]
[/TR]
[TR]
[TD]Richard[/TD]
[TD]5 Jul 2017[/TD]
[/TR]
[TR]
[TD]Melissa[/TD]
[TD]12 Jan 2017[/TD]
[/TR]
[TR]
[TD]Ashley[/TD]
[TD]4 Feb 2017[/TD]
[/TR]
[TR]
[TD]Robert[/TD]
[TD]29 Sep 2017[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 151"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Result [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]12 Jun 2017[/TD]
[/TR]
[TR]
[TD]Ashley[/TD]
[TD]4 Feb 2017[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

This is a more complex case

Something like this (dates as dd/mm/yyyy)

Sheet1

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Name​

[/TD]
[TD]

Date​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Jon​

[/TD]
[TD]

30/04/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Peter​

[/TD]
[TD]

03/06/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Richard​

[/TD]
[TD]

05/07/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Melissa​

[/TD]
[TD]

12/01/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Ashley​

[/TD]
[TD]

20/02/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Robert​

[/TD]
[TD]

29/09/2017​

[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Name​

[/TD]
[TD]

Date​

[/TD]
[TD][/TD]
[TD]

Name​

[/TD]
[TD]

Date​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Jon​

[/TD]
[TD]

30/04/2017​

[/TD]
[TD][/TD]
[TD]

Peter​

[/TD]
[TD]

12/06/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Peter​

[/TD]
[TD]

12/06/2017​

[/TD]
[TD][/TD]
[TD]

Ashley​

[/TD]
[TD]

04/02/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Richard​

[/TD]
[TD]

05/07/2017​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Melissa​

[/TD]
[TD]

12/01/2017​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Ashley​

[/TD]
[TD]

04/02/2017​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Robert​

[/TD]
[TD]

29/09/2017​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in D2 copied down (note that i concatenated the columns so that the MATCH function works properly)
=IFERROR(INDEX(A:A,SMALL(IF(ISNA(MATCH(A$2:A$7&"|"&B$2:B$7,Sheet1!A$2:A$7&"|"&Sheet1!B$2:B$7,0)),ROW(A$2:A$7)),ROWS(D$2:D2))),"")
Ctrl+Shift+Enter

Regular formula in E2
=IF(D2<>"",VLOOKUP(D2,A$2:B$7,2,0),"")

format E2:E3 as Date

M.

 

[LearnExcl]

Great! Thank you! Can we also apply something like MATCH(A$2:A$10,{"John","Peter","Richard"},0) within Sumproduct? It looks like Sumproduct only takes one argument at a time. For instance, if I needed to look for multiple matches under a column such as =SUMPRODUCT(--(A$2:A$10={"John","Peter","Richard"}),--(... it results in an error. Is there a way to look for multiple matches under one column with SUMPRODUCT?

 

[Marcelo Branco]

Try

=SUMPRODUCT(--ISNUMBER(MATCH(A$2:A$10,{"John","Peter","Richard"},0)))

M.

 

[LearnExcl]

Great! Thank you! Can we also apply something like MATCH(A$2:A$10,{"John","Peter","Richard"},0) within Sumproduct? It looks like Sumproduct only takes one argument at a time. For instance, if I needed to look for multiple matches under a column such as =SUMPRODUCT(--(A$2:A$10={"John","Peter","Richard"}),--(... it results in an error. Is there a way to look for multiple matches under one column with SUMPRODUCT?

I believe I have tried something like =SUMPRODUCT(--(ISNUMBER(MATCH(LEFT(A$2:A$10,2),{"42","34","13"},0)),--(... and it did not work. Would you be able to provide any insight? BTW - A$2:A$10 houses data-filtered values with drop down menus; not sure if it would have any impact on the formulas.

I truly appreciate your time.

 

[LearnExcl]

Is it the "(" that I placed before ISNUMBER maybe?