extracting similar data from a list
 

I asked this question, but I would like to clarify. I have a range of data
as shown:

a b
1 jan 5
2 feb 6
3 mar 7
4 jan 8
5 mar 10
6 jan 3

On a different sheet, I would like to extract all the data that is the same
in 'a' and its corresponding value in 'b', and I would like them all
together. No blanks in between. I would like on a different sheet to see
all the 'jan' in the first three cells on the sheet with the values from 'b'
beside them. Or, if it were 'feb' that I wanted, I would only see one cell,
etc.

Thanks,

extracting similar data from a list
 

datafilterautofiltercopy
if desired.record a macro and clean it up
--
Don Guillett
SalesAid Software

"Duane" wrote in message
...
I asked this question, but I would like to clarify. I have a range of
data
as shown:

a b
1 jan 5
2 feb 6
3 mar 7
4 jan 8
5 mar 10
6 jan 3

On a different sheet, I would like to extract all the data that is the
same
in 'a' and its corresponding value in 'b', and I would like them all
together. No blanks in between. I would like on a different sheet to see
all the 'jan' in the first three cells on the sheet with the values from
'b'
beside them. Or, if it were 'feb' that I wanted, I would only see one
cell,
etc.

Thanks,

extracting similar data from a list
 

Let's say you data in Sheet1
in sheet2 A1 =jan
Sheet2 B1
=IF(ISERR(SMALL(IF(Sheet1!$A$1:$A$6=Sheet2!$A$1,RO W(INDIRECT("1:"&ROWS(Sheet1!$B$1:$B$6)))),ROWS($1: 1))),"",INDEX(Sheet1!$B$1:$B$6,SMALL(IF(Sheet1!$A$ 1:$A$6=Sheet2!$A$1,ROW(INDIRECT("1:"&ROWS(Sheet1!$ B$1:$B$6)))),ROWS($1:1))))

ctrl+shift+enter (not just enter)
Copy down as far as need


"Duane" wrote:

I asked this question, but I would like to clarify. I have a range of data
as shown:

a b
1 jan 5
2 feb 6
3 mar 7
4 jan 8
5 mar 10
6 jan 3

On a different sheet, I would like to extract all the data that is the same
in 'a' and its corresponding value in 'b', and I would like them all
together. No blanks in between. I would like on a different sheet to see
all the 'jan' in the first three cells on the sheet with the values from 'b'
beside them. Or, if it were 'feb' that I wanted, I would only see one cell,
etc.

Thanks,

extracting similar data from a list
 

Thanks Teethless. I had managed to come across that formula in another forum.

Thanks for the macro idea too Don. I overlooked using that method with a
macro.

Both ideas work for me, but I am going with the formula

"Teethless mama" wrote:

Let's say you data in Sheet1
in sheet2 A1 =jan
Sheet2 B1
=IF(ISERR(SMALL(IF(Sheet1!$A$1:$A$6=Sheet2!$A$1,RO W(INDIRECT("1:"&ROWS(Sheet1!$B$1:$B$6)))),ROWS($1: 1))),"",INDEX(Sheet1!$B$1:$B$6,SMALL(IF(Sheet1!$A$ 1:$A$6=Sheet2!$A$1,ROW(INDIRECT("1:"&ROWS(Sheet1!$ B$1:$B$6)))),ROWS($1:1))))

ctrl+shift+enter (not just enter)
Copy down as far as need


"Duane" wrote:

I asked this question, but I would like to clarify. I have a range of data
as shown:

a b
1 jan 5
2 feb 6
3 mar 7
4 jan 8
5 mar 10
6 jan 3

On a different sheet, I would like to extract all the data that is the same
in 'a' and its corresponding value in 'b', and I would like them all
together. No blanks in between. I would like on a different sheet to see
all the 'jan' in the first three cells on the sheet with the values from 'b'
beside them. Or, if it were 'feb' that I wanted, I would only see one cell,
etc.

Thanks,

extracting similar data from a list
 

The "overhead" in your computer will be happier if you do it with a macro.

--
Don Guillett
SalesAid Software

"Duane" wrote in message
...
Thanks Teethless. I had managed to come across that formula in another
forum.

Thanks for the macro idea too Don. I overlooked using that method with a
macro.

Both ideas work for me, but I am going with the formula

"Teethless mama" wrote:

Let's say you data in Sheet1
in sheet2 A1 =jan
Sheet2 B1
=IF(ISERR(SMALL(IF(Sheet1!$A$1:$A$6=Sheet2!$A$1,RO W(INDIRECT("1:"&ROWS(Sheet1!$B$1:$B$6)))),ROWS($1: 1))),"",INDEX(Sheet1!$B$1:$B$6,SMALL(IF(Sheet1!$A$ 1:$A$6=Sheet2!$A$1,ROW(INDIRECT("1:"&ROWS(Sheet1!$ B$1:$B$6)))),ROWS($1:1))))

ctrl+shift+enter (not just enter)
Copy down as far as need


"Duane" wrote:

I asked this question, but I would like to clarify. I have a range of
data
as shown:

a b
1 jan 5
2 feb 6
3 mar 7
4 jan 8
5 mar 10
6 jan 3

On a different sheet, I would like to extract all the data that is the
same
in 'a' and its corresponding value in 'b', and I would like them all
together. No blanks in between. I would like on a different sheet to
see
all the 'jan' in the first three cells on the sheet with the values
from 'b'
beside them. Or, if it were 'feb' that I wanted, I would only see one
cell,
etc.

Thanks,