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

Sorry I meant decending order in first post

"Dingy101" wrote:

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 decending 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

Excel 2003, been a long day.

"Dingy101" wrote:

Sorry I meant decending order in first post

"Dingy101" wrote:

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 decending 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

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(ISNUMBE R(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.

--
Biff
Microsoft Excel MVP


"Dingy101" wrote in message
...
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

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. Valko" wrote:

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(ISNUMBE R(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.

--
Biff
Microsoft Excel MVP


"Dingy101" wrote in message
...
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


.

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

And that's exactly what my formula does.

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

280 280
280 275
280 275
278 272
278 272
278 270
275 270
275 270
275 270
275 270
275 270
270 262
0 0
0 0
0 0
0 0

--
Biff
Microsoft Excel MVP


"Dingy101" wrote in message
...
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. Valko" wrote:

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(ISNUMBE R(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.

--
Biff
Microsoft Excel MVP


"Dingy101" wrote in message
...
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


.

On Dec 8, 3:09*am, Dingy101
wrote:
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. Valko" wrote:
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(ISNUMBE R(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.

--
Biff
Microsoft Excel MVP

"Dingy101" wrote in message
...
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

.- Hide quoted text -

- Show quoted text -


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

but simple is better right? : )

And simple is relative, right? <g

--
Biff
Microsoft Excel MVP


"minyeh" wrote in message
...
On Dec 8, 3:09 am, Dingy101
wrote:
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. Valko" wrote:
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(ISNUMBE R(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.

--
Biff
Microsoft Excel MVP

"Dingy101" wrote in message
...
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

.- Hide quoted text -

- Show quoted text -


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

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

"Dingy101" wrote in message
...
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

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


"T. Valko" wrote:

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

And that's exactly what my formula does.

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

280 280
280 275
280 275
278 272
278 272
278 270
275 270
275 270
275 270
275 270
275 270
270 262
0 0
0 0
0 0
0 0

--
Biff
Microsoft Excel MVP


"Dingy101" wrote in message
...
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. Valko" wrote:

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(ISNUMBE R(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.

--
Biff
Microsoft Excel MVP


"Dingy101" wrote in message
...
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


.



.

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

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. Valko" wrote:

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

And that's exactly what my formula does.

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

280 280
280 275
280 275
278 272
278 272
278 270
275 270
275 270
275 270
275 270
275 270
270 262
0 0
0 0
0 0
0 0

--
Biff
Microsoft Excel MVP


"Dingy101" wrote in message
...
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. Valko" wrote:

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(ISNUMBE R(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.

--
Biff
Microsoft Excel MVP


"Dingy101" wrote in message
...
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


.



.

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.

--
Biff
Microsoft Excel MVP


"Dingy101" wrote in message
...
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


"T. Valko" wrote:

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

And that's exactly what my formula does.

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

280 280
280 275
280 275
278 272
278 272
278 270
275 270
275 270
275 270
275 270
275 270
270 262
0 0
0 0
0 0
0 0

--
Biff
Microsoft Excel MVP


"Dingy101" wrote in message
...
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. Valko" wrote:

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(ISNUMBE R(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.

--
Biff
Microsoft Excel MVP


"Dingy101" wrote in message
...
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


.



.