Hi,

I have not been able to find the solution to my problem after searching
through the groups so I've decided to join and post my problem... :o)

I have a long list of ID numbers in an excel spreadsheet which I would
like to extract certain values from. In another cell, I have a drop
down menu (data validation) which will provide the criteria for the
function to operate.

For example, if say, column B contains the following:

1A
1B
1C
2A
2B
2C
3A
3B
3C

I want excel to extract from column B, all the ID numbers that begin
with say, the number "2" (selected from the drop down menu) and display
the results in a row.

So, it should return with the following, in a row (separate cells):

2A 2B 2C

The actual ID codes are something like this: "D0771S" but I don't think
it will affect the above example.

I think my main hurdle is getting Excel to actually return a group of
results rather than simply extracting single values and present them
"transposed" in a row. Also, I would prefer to keep to formulas and not
go into macros/VB.

I realise the "extraction" part of this problem can also be done using
the autofilter/filter function, but this function will form part of
other functions where the results will work in conjunction with another
set of formulas etc so unfortunately filters cannot be used.

Many thanks for your help in advance!

CTR

CTR,

OK. Let's say that your data is on a sheet named "Data Sheet", and you want to show your values on
a sheet name "Display Sheet". Also, your 'drop down' cell is cell A1 on sheet "Display Sheet".
Also, I am assuming that your list starts in cell B2 on sheet "Data Sheet"

On Sheet "Data Sheet", enter this formula in cell C2

=LEFT(B2,LEN('Display Sheet'!$A$1))='Display Sheet'!$A$1

and copy down to match your list in column B.

Then in cell B2 on sheet "Display Sheet", array enter (enter using Ctrl-Shift-Enter) this formula

=IF(COUNTIF('Data Sheet'!$C:$C,TRUE)=COLUMN()-COLUMN($A$2),INDEX('Data Sheet'!$B$1:$B$100,
LARGE(('Data Sheet'!$C$1:$C$100=TRUE)*ROW('Data Sheet'!$C$1:$C$100),COLUMN()-COLUMN($A$2))),"")

Then copy that cell across row 2 of sheet "Display Sheet" to display all the cells from column B of
"Data Sheet" that start with the value entered in cell A1. Note that the order will be reversed,
but that can be addressed if you require it.

HTH,
Bernie
MS Excel MVP


"CTR" wrote in message
oups.com...
Hi,

I have not been able to find the solution to my problem after searching
through the groups so I've decided to join and post my problem... :o)

I have a long list of ID numbers in an excel spreadsheet which I would
like to extract certain values from. In another cell, I have a drop
down menu (data validation) which will provide the criteria for the
function to operate.

For example, if say, column B contains the following:

1A
1B
1C
2A
2B
2C
3A
3B
3C

I want excel to extract from column B, all the ID numbers that begin
with say, the number "2" (selected from the drop down menu) and display
the results in a row.

So, it should return with the following, in a row (separate cells):

2A 2B 2C

The actual ID codes are something like this: "D0771S" but I don't think
it will affect the above example.

I think my main hurdle is getting Excel to actually return a group of
results rather than simply extracting single values and present them
"transposed" in a row. Also, I would prefer to keep to formulas and not
go into macros/VB.

I realise the "extraction" part of this problem can also be done using
the autofilter/filter function, but this function will form part of
other functions where the results will work in conjunction with another
set of formulas etc so unfortunately filters cannot be used.

Many thanks for your help in advance!

CTR

Thanks Bernie, thats great!

Bernie Deitrick wrote:
CTR,

OK. Let's say that your data is on a sheet named "Data Sheet", and you want to show your values on
a sheet name "Display Sheet". Also, your 'drop down' cell is cell A1 on sheet "Display Sheet".
Also, I am assuming that your list starts in cell B2 on sheet "Data Sheet"

On Sheet "Data Sheet", enter this formula in cell C2

=LEFT(B2,LEN('Display Sheet'!$A$1))='Display Sheet'!$A$1

and copy down to match your list in column B.

Then in cell B2 on sheet "Display Sheet", array enter (enter using Ctrl-Shift-Enter) this formula

=IF(COUNTIF('Data Sheet'!$C:$C,TRUE)=COLUMN()-COLUMN($A$2),INDEX('Data Sheet'!$B$1:$B$100,
LARGE(('Data Sheet'!$C$1:$C$100=TRUE)*ROW('Data Sheet'!$C$1:$C$100),COLUMN()-COLUMN($A$2))),"")

Then copy that cell across row 2 of sheet "Display Sheet" to display all the cells from column B of
"Data Sheet" that start with the value entered in cell A1. Note that the order will be reversed,
but that can be addressed if you require it.

HTH,
Bernie
MS Excel MVP


"CTR" wrote in message
oups.com...
Hi,

I have not been able to find the solution to my problem after searching
through the groups so I've decided to join and post my problem... :o)

I have a long list of ID numbers in an excel spreadsheet which I would
like to extract certain values from. In another cell, I have a drop
down menu (data validation) which will provide the criteria for the
function to operate.

For example, if say, column B contains the following:

1A
1B
1C
2A
2B
2C
3A
3B
3C

I want excel to extract from column B, all the ID numbers that begin
with say, the number "2" (selected from the drop down menu) and display
the results in a row.

So, it should return with the following, in a row (separate cells):

2A 2B 2C

The actual ID codes are something like this: "D0771S" but I don't think
it will affect the above example.

I think my main hurdle is getting Excel to actually return a group of
results rather than simply extracting single values and present them
"transposed" in a row. Also, I would prefer to keep to formulas and not
go into macros/VB.

I realise the "extraction" part of this problem can also be done using
the autofilter/filter function, but this function will form part of
other functions where the results will work in conjunction with another
set of formulas etc so unfortunately filters cannot be used.

Many thanks for your help in advance!

CTR