finding all cells that match a certain value
 

ok i have some data arranged as such, the first column is filled with
names and the column next to it is a remarks column, filled with say,
numbers from 1-10. what i want is to have a third column, whereby
let's say i choose the number 8, all the names with a number 8 next to
them will appear in the third column. can anyone help?

thanks.

finding all cells that match a certain value
 

A2:A8 = names
B2:B* = values
C2 =IF(B2=$D$1;A2;"") and copy down

in cell D1 write number which you want

is this what you want?

tomek



Uzytkownik "OTS" napisal w wiadomosci
ups.com...
ok i have some data arranged as such, the first column is filled with
names and the column next to it is a remarks column, filled with say,
numbers from 1-10. what i want is to have a third column, whereby
let's say i choose the number 8, all the names with a number 8 next to
them will appear in the third column. can anyone help?

thanks.

finding all cells that match a certain value
 

On Jul 26, 2:39 pm, "tomek gomek" wrote:
A2:A8 = names
B2:B* = values
C2 =IF(B2=$D$1;A2;"") and copy down

in cell D1 write number which you want

is this what you want?

tomek

Uzytkownik "OTS" napisal w wiadomoscinews:1185427519.335146.98570@r34g2000hsd .googlegroups.com...



ok i have some data arranged as such, the first column is filled with
names and the column next to it is a remarks column, filled with say,
numbers from 1-10. what i want is to have a third column, whereby
let's say i choose the number 8, all the names with a number 8 next to
them will appear in the third column. can anyone help?

thanks.- Hide quoted text -

- Show quoted text -

hi thanks for the reply. it's not exactly what i need. you see, my
list of names goes on for very long. what i'd like is to choose a
number, then have all the corresponding names appear by themselves. so
if the first relevant name is in A3, then the next relevant name is in
A450, when i choose their number, A3 will appear in say C1, then A450
in C2, the next relevant one in C3 and so on.

finding all cells that match a certain value
 

If you have no more than 1000 different entries in your remarks column,
you can use autofilter. Would that be sufficient to your purposes?

- David

OTS wrote:
ok i have some data arranged as such, the first column is filled with
names and the column next to it is a remarks column, filled with say,
numbers from 1-10. what i want is to have a third column, whereby
let's say i choose the number 8, all the names with a number 8 next to
them will appear in the third column. can anyone help?

thanks.

finding all cells that match a certain value
 

David

There is not a 1000 entry limit to a column of data to autofilter.

You can have many thousands of entries in the column but you just won't get more
than 1000 to show in the drop-down list.

See Debra Dalgleish's site for workarounds to the 1000 entry drop-down limit.

http://www.contextures.on.ca/xlautofilter02.html#Limits


Gord Dibben MS Excel MVP

On Mon, 30 Jul 2007 02:41:32 GMT, David Hilberg wrote:

If you have no more than 1000 different entries in your remarks column,
you can use autofilter. Would that be sufficient to your purposes?

- David

OTS wrote:
ok i have some data arranged as such, the first column is filled with
names and the column next to it is a remarks column, filled with say,
numbers from 1-10. what i want is to have a third column, whereby
let's say i choose the number 8, all the names with a number 8 next to
them will appear in the third column. can anyone help?

thanks.

finding all cells that match a certain value
 

Gord - Thanks for the link

You can have many thousands of entries in the column but you just
won't get more
than 1000 to show in the drop-down list.

That's what I meant when I said "no more than 1000 different entries." I
guess it wasn't clear. Oh, well.

- David

Gord Dibben wrote:
David

There is not a 1000 entry limit to a column of data to autofilter.

You can have many thousands of entries in the column but you just won't get more
than 1000 to show in the drop-down list.

See Debra Dalgleish's site for workarounds to the 1000 entry drop-down limit.

http://www.contextures.on.ca/xlautofilter02.html#Limits


Gord Dibben MS Excel MVP

On Mon, 30 Jul 2007 02:41:32 GMT, David Hilberg wrote:

If you have no more than 1000 different entries in your remarks column,
you can use autofilter. Would that be sufficient to your purposes?

- David

OTS wrote:
ok i have some data arranged as such, the first column is filled with
names and the column next to it is a remarks column, filled with say,
numbers from 1-10. what i want is to have a third column, whereby
let's say i choose the number 8, all the names with a number 8 next to
them will appear in the third column. can anyone help?

thanks.

finding all cells that match a certain value
 

thanks everyone for replying. well, autofilter does do the job, but it
covers up other important data i have in other columns when i filter.
i've been using it so far but i have to keep filtering and unfiltering
to get the data i need. i thought that if there was another way to do
it without covering up other data, it would be much more useful to me.

finding all cells that match a certain value
 

Okay, here is a two-column solution. You'll have to modify the formulas
to suit your ranges.

Assuming your Names are in B2:B10 and Remarks are in C2:C10:
D is the helper column.
In D1 enter 0.
In D2 enter and copy down:

=MATCH(E$1,INDIRECT(ADDRESS(2+SUM(D$1:D1),3)&":c10 "),0)

(The 2 is for column B, where the Names are. The 3 is for the Remarks
column, C.)

In E1 enter the Remark you are searching upon.
In E2 enter and copy down:

=OFFSET(INDEX(C$2:C$10,SUM(D$2:D2)),,-1)

(The -1 assumes the Names column is one column to the left of the
Remarks column.)

You should see names in column E. When no more of the desired remarks
are found, the formula will yield #REF!

Hope this helps,

- David

OTS wrote:
ok i have some data arranged as such, the first column is filled with
names and the column next to it is a remarks column, filled with say,
numbers from 1-10. what i want is to have a third column, whereby
let's say i choose the number 8, all the names with a number 8 next to
them will appear in the third column. can anyone help?

thanks.

finding all cells that match a certain value
 

(The 2 is for column B ...

Correction: 2 is the starting row in this example.

- David

David Hilberg wrote:
Okay, here is a two-column solution. You'll have to modify the formulas
to suit your ranges.

Assuming your Names are in B2:B10 and Remarks are in C2:C10:
D is the helper column.
In D1 enter 0.
In D2 enter and copy down:

=MATCH(E$1,INDIRECT(ADDRESS(2+SUM(D$1:D1),3)&":c10 "),0)

(The 2 is for column B, where the Names are. The 3 is for the Remarks
column, C.)

In E1 enter the Remark you are searching upon.
In E2 enter and copy down:

=OFFSET(INDEX(C$2:C$10,SUM(D$2:D2)),,-1)

(The -1 assumes the Names column is one column to the left of the
Remarks column.)

You should see names in column E. When no more of the desired remarks
are found, the formula will yield #REF!

Hope this helps,

- David

OTS wrote:
ok i have some data arranged as such, the first column is filled with
names and the column next to it is a remarks column, filled with say,
numbers from 1-10. what i want is to have a third column, whereby
let's say i choose the number 8, all the names with a number 8 next to
them will appear in the third column. can anyone help?

thanks.

finding all cells that match a certain value
 

david, i tried out your formula and it works beautifully when i test
it out with the columns you used. thanks so much. now i just gotta
mess around to see what i can do to make those #N/As disappear =)
thanks for all the help.

finding all cells that match a certain value
 

OTS wrote:
david, i tried out your formula and it works beautifully when i test
it out with the columns you used. thanks so much. now i just gotta
mess around to see what i can do to make those #N/As disappear =)
thanks for all the help.

Your welcome! Thanks for the feedback.
(If adapting the formula is too involved, I also have a User Defined
Function programmed in VBA that you can install.)

- David