Extracting a Unique List Using Complex Criteria

L

LearnExcl

Board Regular
Joined
Mar 17, 2010
Messages
245
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Sort by date Sort by votes
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.
 
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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:

Code: 
 [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]

Code: 
[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]

Code: 
[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]
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
LearnExcl said:
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?
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
EXTRAPOLATE DATA FROM A TABLE!!!
Replies
6
Views
1K
JamesCanale
JamesCanale

Forum statistics

Threads
1,224,830
Messages
6,181,227
Members
453,025
Latest member
Hannah_Pham93

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top