Search for matches in two columns

D

Dingy101

First two columns of data which will vary depending on other functions in
worksheet

8 9 5
5 7 5
5 7 4
4 5 0
2 5
1 5
0 4
0 0

I want to in a third column to list any occurences of values in column 2
that appear in column 1, including multiples, there will always be 16 rows of
this data. I already have data sorted in accending order in first and second
columns using Large function. Also would be helpful to blank any 0 value.

Would be nice if empty values in third column are blank, but can deal with
anything.

Thank You in advance

Gary
 
T

T. Valko

Try this...

Data in the range A2:B9.

Enter this formula in D2. This will return the count of matches and be used
as an error trap.

=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B9,A2:A9,0))))

Enter this array formula** in E2 and copy down to E9.

=IF(ROWS(E$2:E2)>D$2,"",INDEX(B:B,SMALL(IF(ISNUMBER(MATCH(B$2:B$9,A$2:A$9,0)),ROW(B$2:B$9)),ROWS(E$2:E2))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
D

Dingy101

Biff,

Thank You, as usual great answer.

It almost works, if there is such a thing.

At times, depending on data set in A2:B9 the last non 0 entry repeats the
number of times it appears in column B, but without avaliable matches in
column A.

Below is a cut & paste of actual data set, third column is the error trap
formula results.

What I would expect to see in fourth column is only a single 270 entry, rest
looks good.


280 280 13 280
280 275 13 275
280 275 13 275
278 272 13 270
278 272 14 270
278 270 15 270
275 270 15 270
275 270 15 270
275 270 15 270
275 270 15 0
275 270 15 0
270 262 15 0
0 0 16 0
0 0 16
0 0 16
0 0 16


Gary
 
T

T. Valko

It almost works, if there is such a thing.

A formula either works or it doesn't! There is no gray area!

Ok, I don't see how you arrive at the results you expect.
I want to...list any occurences of values in
column 2 that appear in column 1, including multiples
Click to expand...

And that's exactly what my formula does.

Based on this sample data you posted, what results do expect:
 
M

minyeh

Biff,

Thank You, as usual great answer.

It almost works, if there is such a thing.

At times, depending on data set in A2:B9 the last non 0 entry repeats the
number of times it appears in column B, but without avaliable matches in
column A.

Below is a cut & paste of actual data set, third column is the error trap
formula results.

What I would expect to see in fourth column is only a single 270 entry, rest
looks good.

280     280     13      280
280     275     13      275
280     275     13      275
278     272     13      270
278     272     14      270
278     270     15      270
275     270     15      270
275     270     15      270
275     270     15      270
275     270     15      0
275     270     15      0
270     262     15      0
0       0       16      0
0       0       16      
0       0       16      
0       0       16      

Gary








- Show quoted text -
Click to expand...


Data in the range A2:B13
In cell C2, key in
=IF(COUNTIF(A:A,B2)>COUNTIF(B$2:B2,B2)-1,B2,"")
fill down as far as u want
then do a filter to eliminate the blank cells,
or do a one-to-one mapping to another column if u want
but simple is better right? : ) hope this helps
 
T

T. Valko

but simple is better right? : )

And simple is relative, right? <g>

--
Biff
Microsoft Excel MVP


Biff,

Thank You, as usual great answer.

It almost works, if there is such a thing.

At times, depending on data set in A2:B9 the last non 0 entry repeats the
number of times it appears in column B, but without avaliable matches in
column A.

Below is a cut & paste of actual data set, third column is the error trap
formula results.

What I would expect to see in fourth column is only a single 270 entry,
rest
looks good.

280 280 13 280
280 275 13 275
280 275 13 275
278 272 13 270
278 272 14 270
278 270 15 270
275 270 15 270
275 270 15 270
275 270 15 270
275 270 15 0
275 270 15 0
270 262 15 0
0 0 16 0
0 0 16
0 0 16
0 0 16

Gary








- Show quoted text -
Click to expand...


Data in the range A2:B13
In cell C2, key in
=IF(COUNTIF(A:A,B2)>COUNTIF(B$2:B2,B2)-1,B2,"")
fill down as far as u want
then do a filter to eliminate the blank cells,
or do a one-to-one mapping to another column if u want
but simple is better right? : ) hope this helps
 
A

Ashish Mathur

Hi,


Give headings to the first range, say Header1. To the second range, give a
heading Header2. Say that the data (including the header row) is D8:E16.
In H9, enter =COUNTIF($D$9:$D$16,E9)>=1. In cell L8, enter Header2. Go to
Data > Filter > Advanced Filter and select Copy to another location. In the
list range, select D8:E16. In the criteria range, select H8:H9. In the
copy to box, select L8. Click on OK

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
D

Dingy101

Based on this sample data you posted, what results do expect:

Third column is expected results

A -----B-----C
280---280---280
280---275---275
280---275---275
278---272---270
278---272---0
278---270---0
275---270---0
275---270---0
275---270
275---270
275---270
270---262
0-------0
0-------0
0-------0
0-------0


There is one occurence of 280 in both columns,
There are two occurences of 275 in both columns,
There is one occurence of 270 in both columns,
There are four occurences of 0 in both columns,

Only if an item in column two has a corresponding match in column one does
it get listed, once the pair is matched, they are removed from selection set.

280, there are one in column two and three in column one, 280 gets listed
one time in solution.

275, there are two in column two and four in column one, 275 gets listed two
times in solution

270 has six in column two and one in column one, 270 gets listed once in
solution.

Zero's are four times in each column, listed four times in solution.

If a number is in only one column but not other it is not listed in solution.

I put dashes in between expected data numbers above to try to maintain view
of columns.

Gary
 
D

Dingy101

What I would expect to see in fourth column is only a single 270 entry,
From this comment I see I might have confused you.

Fourth column should have:
280
275
275
270
0
0
0
0

I meant that it should have only one 270 entry, not the four it shows. The
280 and 275 pair are fine.

Gary
 
T

T. Valko

Ok, this is a bit more complicated than it appears.

Use a helper column to "mark" the numbers to extract.

With the data in A2:B17...

Enter this formula in C2 and copy down to C17:

=IF(COUNTIF(A$2:A$17,B2)>=COUNTIF(B$2:B2,B2),"x","")

Enter this formula in E1. This will return the count of "x" from C2:C17:

=COUNTIF(C2:C17,"x")

Enter this array formula** in E2 and copy down to E17:

=IF(ROWS(E$2:E2)>E$1,"",LARGE(IF(C$2:C$17="x",B$2:B$17),ROWS(E$2:E2)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel VBA macro, search - accumulate - delete original 0
Page numbering with Merged Document and IF statements 1
lookup two different variables 0
Match and delete rows on two different sheets from reference numbers 4
Seperate numbers in a cell 2
Sumproduct? 3
Finding dynamic maxima 9
Sum of Variable Cells in a fixed Row 3

Top