Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding a match and pasting next to it | Excel Discussion (Misc queries) | |||
Finding a Text match | Excel Discussion (Misc queries) | |||
Finding Closest Match | Excel Worksheet Functions | |||
Finding a match in several columns | Excel Worksheet Functions | |||
Finding a match | Excel Discussion (Misc queries) |