|
lookup formula
Hi,
I have an issue bringing across data into a table. In worksheet 1 I have my data, in worksheet 2, I have one table that needs to be populated by only some of the data in worksheet 1. The problem is that I have multiple sets of criteria that could meet each cell in the table. For example, the first column in my table is simply a name. But, Not only do i need my table to show how many times the name appears in worksheet 1 in a different row, but I also need it to seperate out each individual name. Example: In worksheet 1 I may have something like this: Column 1 Column 2 John Yes Paul Yes Stephen No John Yes Jason Yes Paul No John No Bob Maybe I need my table in worksheet 2 to look something like this (the criteria for returning the value is Yes or Maybe): Column 1 John Paul John Jason Bob Any ideas? |
lookup formula
Is this a one time need or will worksheet 2 need to dynamically change
whenever worksheet 1 values change from a maybe or no to a yes? If it's a one time need then I'd simply have worksheet 2 contain =IF(Sheet1!B1="yes",Sheet1!A1,IF(Sheet1!B1="maybe" ,Sheet1!A1,"")) in any rows with names on worksheet1, so Worksheet1 Fred yes Bert no John yes Paul yes Sarah yes John maybe gives Worksheet2 Fred John Paul Sarah John You can then highlight, copy those cells and paste special values so you lose the formulae and just get the names listed |
lookup formula
Try to make a pivot table on a new sheet, you could show only those names
which are marked with Yes or Maybe, and also you could count the name appearances. "StephenAccountant" wrote: Hi, I have an issue bringing across data into a table. In worksheet 1 I have my data, in worksheet 2, I have one table that needs to be populated by only some of the data in worksheet 1. The problem is that I have multiple sets of criteria that could meet each cell in the table. For example, the first column in my table is simply a name. But, Not only do i need my table to show how many times the name appears in worksheet 1 in a different row, but I also need it to seperate out each individual name. Example: In worksheet 1 I may have something like this: Column 1 Column 2 John Yes Paul Yes Stephen No John Yes Jason Yes Paul No John No Bob Maybe I need my table in worksheet 2 to look something like this (the criteria for returning the value is Yes or Maybe): Column 1 John Paul John Jason Bob Any ideas? |
lookup formula
hmmm, possibly
Can I do it with out showing any gaps in worksheet 2? "Piscator" wrote: Is this a one time need or will worksheet 2 need to dynamically change whenever worksheet 1 values change from a maybe or no to a yes? If it's a one time need then I'd simply have worksheet 2 contain =IF(Sheet1!B1="yes",Sheet1!A1,IF(Sheet1!B1="maybe" ,Sheet1!A1,"")) in any rows with names on worksheet1, so Worksheet1 Fred yes Bert no John yes Paul yes Sarah yes John maybe gives Worksheet2 Fred John Paul Sarah John You can then highlight, copy those cells and paste special values so you lose the formulae and just get the names listed |
lookup formula
=IF(ISERR(SMALL(IF((status="yes")+(status="maybe") ,ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"", INDEX(names,SMALL(IF((status="yes")+(status="maybe "),ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1) )))
ctrl+shift+enter, not just enter copy down "StephenAccountant" wrote: Hi, I have an issue bringing across data into a table. In worksheet 1 I have my data, in worksheet 2, I have one table that needs to be populated by only some of the data in worksheet 1. The problem is that I have multiple sets of criteria that could meet each cell in the table. For example, the first column in my table is simply a name. But, Not only do i need my table to show how many times the name appears in worksheet 1 in a different row, but I also need it to seperate out each individual name. Example: In worksheet 1 I may have something like this: Column 1 Column 2 John Yes Paul Yes Stephen No John Yes Jason Yes Paul No John No Bob Maybe I need my table in worksheet 2 to look something like this (the criteria for returning the value is Yes or Maybe): Column 1 John Paul John Jason Bob Any ideas? |
lookup formula
Ok i would like to try this formula but am getting a bit confused.
Do i replace "status" with the column? What do i Replace "name" with? Sorry, i've never used ROW or INDIRECT formulas before so not sure how they work. "Teethless mama" wrote: =IF(ISERR(SMALL(IF((status="yes")+(status="maybe") ,ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"", INDEX(names,SMALL(IF((status="yes")+(status="maybe "),ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1) ))) ctrl+shift+enter, not just enter copy down "StephenAccountant" wrote: Hi, I have an issue bringing across data into a table. In worksheet 1 I have my data, in worksheet 2, I have one table that needs to be populated by only some of the data in worksheet 1. The problem is that I have multiple sets of criteria that could meet each cell in the table. For example, the first column in my table is simply a name. But, Not only do i need my table to show how many times the name appears in worksheet 1 in a different row, but I also need it to seperate out each individual name. Example: In worksheet 1 I may have something like this: Column 1 Column 2 John Yes Paul Yes Stephen No John Yes Jason Yes Paul No John No Bob Maybe I need my table in worksheet 2 to look something like this (the criteria for returning the value is Yes or Maybe): Column 1 John Paul John Jason Bob Any ideas? |
lookup formula
"status" is a define name in a range (ex: B2:B100 contain "yes", "no", "maybe")
"names" is a define name in a range (ex: A2:A100 contain a list of names) "StephenAccountant" wrote: Ok i would like to try this formula but am getting a bit confused. Do i replace "status" with the column? What do i Replace "name" with? Sorry, i've never used ROW or INDIRECT formulas before so not sure how they work. "Teethless mama" wrote: =IF(ISERR(SMALL(IF((status="yes")+(status="maybe") ,ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"", INDEX(names,SMALL(IF((status="yes")+(status="maybe "),ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1) ))) ctrl+shift+enter, not just enter copy down "StephenAccountant" wrote: Hi, I have an issue bringing across data into a table. In worksheet 1 I have my data, in worksheet 2, I have one table that needs to be populated by only some of the data in worksheet 1. The problem is that I have multiple sets of criteria that could meet each cell in the table. For example, the first column in my table is simply a name. But, Not only do i need my table to show how many times the name appears in worksheet 1 in a different row, but I also need it to seperate out each individual name. Example: In worksheet 1 I may have something like this: Column 1 Column 2 John Yes Paul Yes Stephen No John Yes Jason Yes Paul No John No Bob Maybe I need my table in worksheet 2 to look something like this (the criteria for returning the value is Yes or Maybe): Column 1 John Paul John Jason Bob Any ideas? |
lookup formula
holy crap it worked.
Thank you so much. =IF(ISERR(SMALL(IF(('Data Entry'!$B$8:$B$200=Lists!$B$10)+('Data Entry'!$B$8:$B$200=Lists!$B$11),ROW(INDIRECT("1:"& ROWS('Data Entry'!$A$8:$A$200)))),ROWS($1:1))),"",INDEX('Data Entry'!$A$8:$A$200,SMALL(IF((status=Lists!$B$10)+( status=Lists!$B$11),ROW(INDIRECT("1:"&ROWS('Data Entry'!$A$8:$A$200)))),ROWS($1:1)))) "Teethless mama" wrote: "status" is a define name in a range (ex: B2:B100 contain "yes", "no", "maybe") "names" is a define name in a range (ex: A2:A100 contain a list of names) "StephenAccountant" wrote: Ok i would like to try this formula but am getting a bit confused. Do i replace "status" with the column? What do i Replace "name" with? Sorry, i've never used ROW or INDIRECT formulas before so not sure how they work. "Teethless mama" wrote: =IF(ISERR(SMALL(IF((status="yes")+(status="maybe") ,ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"", INDEX(names,SMALL(IF((status="yes")+(status="maybe "),ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1) ))) ctrl+shift+enter, not just enter copy down "StephenAccountant" wrote: Hi, I have an issue bringing across data into a table. In worksheet 1 I have my data, in worksheet 2, I have one table that needs to be populated by only some of the data in worksheet 1. The problem is that I have multiple sets of criteria that could meet each cell in the table. For example, the first column in my table is simply a name. But, Not only do i need my table to show how many times the name appears in worksheet 1 in a different row, but I also need it to seperate out each individual name. Example: In worksheet 1 I may have something like this: Column 1 Column 2 John Yes Paul Yes Stephen No John Yes Jason Yes Paul No John No Bob Maybe I need my table in worksheet 2 to look something like this (the criteria for returning the value is Yes or Maybe): Column 1 John Paul John Jason Bob Any ideas? |
lookup formula
Are the only possible entries Yes, No or Maybe (and no empty cells)?
If so, you can do this more efficiently (smaller formula, faster calculating, non-volatile) if you just test for entries that are not "no". =IF(ROWS($1:1)<=COUNTIF(status,"<no"),INDEX(name, SMALL(IF(status<"no",ROW(name)-MIN(ROW(name))+1),ROWS($1:1))),"") I keep trying to "convince" TM this method is much better (smaller formula, faster calculating, non-volatile) but "he" doesn't want to listen! <g Biff "StephenAccountant" wrote in message ... holy crap it worked. Thank you so much. =IF(ISERR(SMALL(IF(('Data Entry'!$B$8:$B$200=Lists!$B$10)+('Data Entry'!$B$8:$B$200=Lists!$B$11),ROW(INDIRECT("1:"& ROWS('Data Entry'!$A$8:$A$200)))),ROWS($1:1))),"",INDEX('Data Entry'!$A$8:$A$200,SMALL(IF((status=Lists!$B$10)+( status=Lists!$B$11),ROW(INDIRECT("1:"&ROWS('Data Entry'!$A$8:$A$200)))),ROWS($1:1)))) "Teethless mama" wrote: "status" is a define name in a range (ex: B2:B100 contain "yes", "no", "maybe") "names" is a define name in a range (ex: A2:A100 contain a list of names) "StephenAccountant" wrote: Ok i would like to try this formula but am getting a bit confused. Do i replace "status" with the column? What do i Replace "name" with? Sorry, i've never used ROW or INDIRECT formulas before so not sure how they work. "Teethless mama" wrote: =IF(ISERR(SMALL(IF((status="yes")+(status="maybe") ,ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"", INDEX(names,SMALL(IF((status="yes")+(status="maybe "),ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1) ))) ctrl+shift+enter, not just enter copy down "StephenAccountant" wrote: Hi, I have an issue bringing across data into a table. In worksheet 1 I have my data, in worksheet 2, I have one table that needs to be populated by only some of the data in worksheet 1. The problem is that I have multiple sets of criteria that could meet each cell in the table. For example, the first column in my table is simply a name. But, Not only do i need my table to show how many times the name appears in worksheet 1 in a different row, but I also need it to seperate out each individual name. Example: In worksheet 1 I may have something like this: Column 1 Column 2 John Yes Paul Yes Stephen No John Yes Jason Yes Paul No John No Bob Maybe I need my table in worksheet 2 to look something like this (the criteria for returning the value is Yes or Maybe): Column 1 John Paul John Jason Bob Any ideas? |
lookup formula
I forgot:
That formula is also an array formula entered with CTRL,SHIFT,ENTER (not just Enter). Biff "T. Valko" wrote in message ... Are the only possible entries Yes, No or Maybe (and no empty cells)? If so, you can do this more efficiently (smaller formula, faster calculating, non-volatile) if you just test for entries that are not "no". =IF(ROWS($1:1)<=COUNTIF(status,"<no"),INDEX(name, SMALL(IF(status<"no",ROW(name)-MIN(ROW(name))+1),ROWS($1:1))),"") I keep trying to "convince" TM this method is much better (smaller formula, faster calculating, non-volatile) but "he" doesn't want to listen! <g Biff "StephenAccountant" wrote in message ... holy crap it worked. Thank you so much. =IF(ISERR(SMALL(IF(('Data Entry'!$B$8:$B$200=Lists!$B$10)+('Data Entry'!$B$8:$B$200=Lists!$B$11),ROW(INDIRECT("1:"& ROWS('Data Entry'!$A$8:$A$200)))),ROWS($1:1))),"",INDEX('Data Entry'!$A$8:$A$200,SMALL(IF((status=Lists!$B$10)+( status=Lists!$B$11),ROW(INDIRECT("1:"&ROWS('Data Entry'!$A$8:$A$200)))),ROWS($1:1)))) "Teethless mama" wrote: "status" is a define name in a range (ex: B2:B100 contain "yes", "no", "maybe") "names" is a define name in a range (ex: A2:A100 contain a list of names) "StephenAccountant" wrote: Ok i would like to try this formula but am getting a bit confused. Do i replace "status" with the column? What do i Replace "name" with? Sorry, i've never used ROW or INDIRECT formulas before so not sure how they work. "Teethless mama" wrote: =IF(ISERR(SMALL(IF((status="yes")+(status="maybe") ,ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"", INDEX(names,SMALL(IF((status="yes")+(status="maybe "),ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1) ))) ctrl+shift+enter, not just enter copy down "StephenAccountant" wrote: Hi, I have an issue bringing across data into a table. In worksheet 1 I have my data, in worksheet 2, I have one table that needs to be populated by only some of the data in worksheet 1. The problem is that I have multiple sets of criteria that could meet each cell in the table. For example, the first column in my table is simply a name. But, Not only do i need my table to show how many times the name appears in worksheet 1 in a different row, but I also need it to seperate out each individual name. Example: In worksheet 1 I may have something like this: Column 1 Column 2 John Yes Paul Yes Stephen No John Yes Jason Yes Paul No John No Bob Maybe I need my table in worksheet 2 to look something like this (the criteria for returning the value is Yes or Maybe): Column 1 John Paul John Jason Bob Any ideas? |
| All times are GMT +1. The time now is 10:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com