| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup query - Biff
Hi Biff - sorry your #1 Pain again!! I did put this in a thread but don't
think it went through. all is working well (+ I've made some changes based on other tips from you on previous notes) however there are still issues with duplicates. Currently I have as an example =IF(COUNTIF(Tasks!B$2:B$576,A59),INDEX(Tasks!A$2:A $576,MATCH(A59,Tasks!B$2:B$576,0)),"-") On the tasks sheet row 59,60 and 61 column B are all the same which is the same as the number listed on the current sheet in A59, A60 & A61 - but the data I'm collecting from the tasks sheet in columns D, F & J (same rows) change on each row which the formula isnt picking up The formula gives the correct answer for A59 but when I drag down to use for A60 and A61 it only provides the row 59 answers again. Gosh I hope this makes it clearer Biff its the only way I can think to clarify for you. Any assistance as always appreciated Lise -- Thanks as always Lise |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup query - Biff
I'm not sure I understand.
This is what it sounds like to me... Your lookup value in A59 has more than one instance in Tasks!B$2:B$576 so the formula is returning the same result from Tasks!A$2:A$576 for each instance of the lookup value. For eample: A59 = 1 1...first 1...second 1...third Every time you lookup 1 the result is first. -- Biff Microsoft Excel MVP "Lise" wrote in message ... Hi Biff - sorry your #1 Pain again!! I did put this in a thread but don't think it went through. all is working well (+ I've made some changes based on other tips from you on previous notes) however there are still issues with duplicates. Currently I have as an example =IF(COUNTIF(Tasks!B$2:B$576,A59),INDEX(Tasks!A$2:A $576,MATCH(A59,Tasks!B$2:B$576,0)),"-") On the tasks sheet row 59,60 and 61 column B are all the same which is the same as the number listed on the current sheet in A59, A60 & A61 - but the data I'm collecting from the tasks sheet in columns D, F & J (same rows) change on each row which the formula isnt picking up The formula gives the correct answer for A59 but when I drag down to use for A60 and A61 it only provides the row 59 answers again. Gosh I hope this makes it clearer Biff its the only way I can think to clarify for you. Any assistance as always appreciated Lise -- Thanks as always Lise |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup query - Biff
Yes that's right - so the 1 (using your example) shows on 3 seperate rows in
the sheet I want the answers to go to but the data in the others columns (which is different each time) does not it only keeps repeating the first data. am I asking too much do you think? -- Thanks as always Lise "T. Valko" wrote: I'm not sure I understand. This is what it sounds like to me... Your lookup value in A59 has more than one instance in Tasks!B$2:B$576 so the formula is returning the same result from Tasks!A$2:A$576 for each instance of the lookup value. For eample: A59 = 1 1...first 1...second 1...third Every time you lookup 1 the result is first. -- Biff Microsoft Excel MVP "Lise" wrote in message ... Hi Biff - sorry your #1 Pain again!! I did put this in a thread but don't think it went through. all is working well (+ I've made some changes based on other tips from you on previous notes) however there are still issues with duplicates. Currently I have as an example =IF(COUNTIF(Tasks!B$2:B$576,A59),INDEX(Tasks!A$2:A $576,MATCH(A59,Tasks!B$2:B$576,0)),"-") On the tasks sheet row 59,60 and 61 column B are all the same which is the same as the number listed on the current sheet in A59, A60 & A61 - but the data I'm collecting from the tasks sheet in columns D, F & J (same rows) change on each row which the formula isnt picking up The formula gives the correct answer for A59 but when I drag down to use for A60 and A61 it only provides the row 59 answers again. Gosh I hope this makes it clearer Biff its the only way I can think to clarify for you. Any assistance as always appreciated Lise -- Thanks as always Lise . |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup query - Biff
am I asking too much do you think?
Not yet! <g The best way to do this defpends on how your data is setup. Is the data to lookup sorted or grouped together like this: 1...first 1...second 1...third 2...first 3...first 3...second Or is it random: 1...first 2...first 1...second 3...first 1...third 3...second -- Biff Microsoft Excel MVP "Lise" wrote in message ... Yes that's right - so the 1 (using your example) shows on 3 seperate rows in the sheet I want the answers to go to but the data in the others columns (which is different each time) does not it only keeps repeating the first data. am I asking too much do you think? -- Thanks as always Lise "T. Valko" wrote: I'm not sure I understand. This is what it sounds like to me... Your lookup value in A59 has more than one instance in Tasks!B$2:B$576 so the formula is returning the same result from Tasks!A$2:A$576 for each instance of the lookup value. For eample: A59 = 1 1...first 1...second 1...third Every time you lookup 1 the result is first. -- Biff Microsoft Excel MVP "Lise" wrote in message ... Hi Biff - sorry your #1 Pain again!! I did put this in a thread but don't think it went through. all is working well (+ I've made some changes based on other tips from you on previous notes) however there are still issues with duplicates. Currently I have as an example =IF(COUNTIF(Tasks!B$2:B$576,A59),INDEX(Tasks!A$2:A $576,MATCH(A59,Tasks!B$2:B$576,0)),"-") On the tasks sheet row 59,60 and 61 column B are all the same which is the same as the number listed on the current sheet in A59, A60 & A61 - but the data I'm collecting from the tasks sheet in columns D, F & J (same rows) change on each row which the formula isnt picking up The formula gives the correct answer for A59 but when I drag down to use for A60 and A61 it only provides the row 59 answers again. Gosh I hope this makes it clearer Biff its the only way I can think to clarify for you. Any assistance as always appreciated Lise -- Thanks as always Lise . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup query - Biff
Hi Biff
Sorry for late reply - I can actually sort to be either way so which ever works best with your idea. -- Thanks as always Lise "T. Valko" wrote: am I asking too much do you think? Not yet! <g The best way to do this defpends on how your data is setup. Is the data to lookup sorted or grouped together like this: 1...first 1...second 1...third 2...first 3...first 3...second Or is it random: 1...first 2...first 1...second 3...first 1...third 3...second -- Biff Microsoft Excel MVP "Lise" wrote in message ... Yes that's right - so the 1 (using your example) shows on 3 seperate rows in the sheet I want the answers to go to but the data in the others columns (which is different each time) does not it only keeps repeating the first data. am I asking too much do you think? -- Thanks as always Lise "T. Valko" wrote: I'm not sure I understand. This is what it sounds like to me... Your lookup value in A59 has more than one instance in Tasks!B$2:B$576 so the formula is returning the same result from Tasks!A$2:A$576 for each instance of the lookup value. For eample: A59 = 1 1...first 1...second 1...third Every time you lookup 1 the result is first. -- Biff Microsoft Excel MVP "Lise" wrote in message ... Hi Biff - sorry your #1 Pain again!! I did put this in a thread but don't think it went through. all is working well (+ I've made some changes based on other tips from you on previous notes) however there are still issues with duplicates. Currently I have as an example =IF(COUNTIF(Tasks!B$2:B$576,A59),INDEX(Tasks!A$2:A $576,MATCH(A59,Tasks!B$2:B$576,0)),"-") On the tasks sheet row 59,60 and 61 column B are all the same which is the same as the number listed on the current sheet in A59, A60 & A61 - but the data I'm collecting from the tasks sheet in columns D, F & J (same rows) change on each row which the formula isnt picking up The formula gives the correct answer for A59 but when I drag down to use for A60 and A61 it only provides the row 59 answers again. Gosh I hope this makes it clearer Biff its the only way I can think to clarify for you. Any assistance as always appreciated Lise -- Thanks as always Lise . . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup query - Biff
If the data is sorted then the formula is less complicated!
Let's assume this is your data sorted by column A: ...........A..........B 1....................... 2........1..........A 3........1..........C 4........1..........E 5........2..........K 6........3..........L 7........3..........P D2 = your lookup value Enter this formula in E2 and copy down until you get blanks: =IF(ROWS(E$2:E2)COUNTIF(A$2:A$7,D$2),"",INDEX(B$2 :B$7,MATCH(D$2,A$2:A$7,0)+ROWS(E$2:E2)-1)) You need to copy to a number of cells that is at least equal to the max number of instances of any one lookup value. For example, in the above 1 appears the most times, 3. So, you need to copy the formula to at least 3 cells. -- Biff Microsoft Excel MVP "Lise" wrote in message ... Hi Biff Sorry for late reply - I can actually sort to be either way so which ever works best with your idea. -- Thanks as always Lise "T. Valko" wrote: am I asking too much do you think? Not yet! <g The best way to do this defpends on how your data is setup. Is the data to lookup sorted or grouped together like this: 1...first 1...second 1...third 2...first 3...first 3...second Or is it random: 1...first 2...first 1...second 3...first 1...third 3...second -- Biff Microsoft Excel MVP "Lise" wrote in message ... Yes that's right - so the 1 (using your example) shows on 3 seperate rows in the sheet I want the answers to go to but the data in the others columns (which is different each time) does not it only keeps repeating the first data. am I asking too much do you think? -- Thanks as always Lise "T. Valko" wrote: I'm not sure I understand. This is what it sounds like to me... Your lookup value in A59 has more than one instance in Tasks!B$2:B$576 so the formula is returning the same result from Tasks!A$2:A$576 for each instance of the lookup value. For eample: A59 = 1 1...first 1...second 1...third Every time you lookup 1 the result is first. -- Biff Microsoft Excel MVP "Lise" wrote in message ... Hi Biff - sorry your #1 Pain again!! I did put this in a thread but don't think it went through. all is working well (+ I've made some changes based on other tips from you on previous notes) however there are still issues with duplicates. Currently I have as an example =IF(COUNTIF(Tasks!B$2:B$576,A59),INDEX(Tasks!A$2:A $576,MATCH(A59,Tasks!B$2:B$576,0)),"-") On the tasks sheet row 59,60 and 61 column B are all the same which is the same as the number listed on the current sheet in A59, A60 & A61 - but the data I'm collecting from the tasks sheet in columns D, F & J (same rows) change on each row which the formula isnt picking up The formula gives the correct answer for A59 but when I drag down to use for A60 and A61 it only provides the row 59 answers again. Gosh I hope this makes it clearer Biff its the only way I can think to clarify for you. Any assistance as always appreciated Lise -- Thanks as always Lise . . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup query - Biff
Fantastic as always Biff - works like a charm
-- Thanks so much Lise "T. Valko" wrote: If the data is sorted then the formula is less complicated! Let's assume this is your data sorted by column A: ...........A..........B 1....................... 2........1..........A 3........1..........C 4........1..........E 5........2..........K 6........3..........L 7........3..........P D2 = your lookup value Enter this formula in E2 and copy down until you get blanks: =IF(ROWS(E$2:E2)COUNTIF(A$2:A$7,D$2),"",INDEX(B$2 :B$7,MATCH(D$2,A$2:A$7,0)+ROWS(E$2:E2)-1)) You need to copy to a number of cells that is at least equal to the max number of instances of any one lookup value. For example, in the above 1 appears the most times, 3. So, you need to copy the formula to at least 3 cells. -- Biff Microsoft Excel MVP "Lise" wrote in message ... Hi Biff Sorry for late reply - I can actually sort to be either way so which ever works best with your idea. -- Thanks as always Lise "T. Valko" wrote: am I asking too much do you think? Not yet! <g The best way to do this defpends on how your data is setup. Is the data to lookup sorted or grouped together like this: 1...first 1...second 1...third 2...first 3...first 3...second Or is it random: 1...first 2...first 1...second 3...first 1...third 3...second -- Biff Microsoft Excel MVP "Lise" wrote in message ... Yes that's right - so the 1 (using your example) shows on 3 seperate rows in the sheet I want the answers to go to but the data in the others columns (which is different each time) does not it only keeps repeating the first data. am I asking too much do you think? -- Thanks as always Lise "T. Valko" wrote: I'm not sure I understand. This is what it sounds like to me... Your lookup value in A59 has more than one instance in Tasks!B$2:B$576 so the formula is returning the same result from Tasks!A$2:A$576 for each instance of the lookup value. For eample: A59 = 1 1...first 1...second 1...third Every time you lookup 1 the result is first. -- Biff Microsoft Excel MVP "Lise" wrote in message ... Hi Biff - sorry your #1 Pain again!! I did put this in a thread but don't think it went through. all is working well (+ I've made some changes based on other tips from you on previous notes) however there are still issues with duplicates. Currently I have as an example =IF(COUNTIF(Tasks!B$2:B$576,A59),INDEX(Tasks!A$2:A $576,MATCH(A59,Tasks!B$2:B$576,0)),"-") On the tasks sheet row 59,60 and 61 column B are all the same which is the same as the number listed on the current sheet in A59, A60 & A61 - but the data I'm collecting from the tasks sheet in columns D, F & J (same rows) change on each row which the formula isnt picking up The formula gives the correct answer for A59 but when I drag down to use for A60 and A61 it only provides the row 59 answers again. Gosh I hope this makes it clearer Biff its the only way I can think to clarify for you. Any assistance as always appreciated Lise -- Thanks as always Lise . . . |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup query - Biff
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Lise" wrote in message ... Fantastic as always Biff - works like a charm -- Thanks so much Lise "T. Valko" wrote: If the data is sorted then the formula is less complicated! Let's assume this is your data sorted by column A: ...........A..........B 1....................... 2........1..........A 3........1..........C 4........1..........E 5........2..........K 6........3..........L 7........3..........P D2 = your lookup value Enter this formula in E2 and copy down until you get blanks: =IF(ROWS(E$2:E2)COUNTIF(A$2:A$7,D$2),"",INDEX(B$2 :B$7,MATCH(D$2,A$2:A$7,0)+ROWS(E$2:E2)-1)) You need to copy to a number of cells that is at least equal to the max number of instances of any one lookup value. For example, in the above 1 appears the most times, 3. So, you need to copy the formula to at least 3 cells. -- Biff Microsoft Excel MVP "Lise" wrote in message ... Hi Biff Sorry for late reply - I can actually sort to be either way so which ever works best with your idea. -- Thanks as always Lise "T. Valko" wrote: am I asking too much do you think? Not yet! <g The best way to do this defpends on how your data is setup. Is the data to lookup sorted or grouped together like this: 1...first 1...second 1...third 2...first 3...first 3...second Or is it random: 1...first 2...first 1...second 3...first 1...third 3...second -- Biff Microsoft Excel MVP "Lise" wrote in message ... Yes that's right - so the 1 (using your example) shows on 3 seperate rows in the sheet I want the answers to go to but the data in the others columns (which is different each time) does not it only keeps repeating the first data. am I asking too much do you think? -- Thanks as always Lise "T. Valko" wrote: I'm not sure I understand. This is what it sounds like to me... Your lookup value in A59 has more than one instance in Tasks!B$2:B$576 so the formula is returning the same result from Tasks!A$2:A$576 for each instance of the lookup value. For eample: A59 = 1 1...first 1...second 1...third Every time you lookup 1 the result is first. -- Biff Microsoft Excel MVP "Lise" wrote in message ... Hi Biff - sorry your #1 Pain again!! I did put this in a thread but don't think it went through. all is working well (+ I've made some changes based on other tips from you on previous notes) however there are still issues with duplicates. Currently I have as an example =IF(COUNTIF(Tasks!B$2:B$576,A59),INDEX(Tasks!A$2:A $576,MATCH(A59,Tasks!B$2:B$576,0)),"-") On the tasks sheet row 59,60 and 61 column B are all the same which is the same as the number listed on the current sheet in A59, A60 & A61 - but the data I'm collecting from the tasks sheet in columns D, F & J (same rows) change on each row which the formula isnt picking up The formula gives the correct answer for A59 but when I drag down to use for A60 and A61 it only provides the row 59 answers again. Gosh I hope this makes it clearer Biff its the only way I can think to clarify for you. Any assistance as always appreciated Lise -- Thanks as always Lise . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| one more question BIFF | Excel Worksheet Functions | |||
| Biff I need your help!! | Excel Worksheet Functions | |||
| Hello Biff are u here | Excel Discussion (Misc queries) | |||
| help with spaces in functions (Biff) | Excel Worksheet Functions | |||
| To Biff & Niek | New Users to Excel | |||
Linear Mode
