| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
HELP!!
i am using the following formula to populate certain fields....
=IF(ISERROR(INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10=$ A$10,ROW($A$1:$A$10)),ROW(1:1)),2)),"",INDEX($A$1: $B$10,SMALL(IF($A$1:$A$10=$A$10,ROW($A$1:$A$10)),R OW(1:1)),2)) i have 10 columns in workbook 1 and i am tryin to populate all the data from 10 columns of workbook 1 in to workbook 2. but the prob is if i use the above formula, i have to modify it in all the columns of workbook 2 to get the data from workbook 1. what makes my job easier is if i cld use the above function or any other to populate all the 10 columns of workbook1 in 10 different columns of workbook 2 without having to use the formula 10 different times and modifying it each time. its like writing the function in one cell and all the 10 cells get populated by itself in workbook 2 with the data from workbook 1. I used the above function as there are dupluicates in the data and vlookup dont work in that case. Any help wld be appreciated. |
|
#2
|
|||
|
|||
|
$A$1 is an absolute reference. When you copy the formula to another column,
it stays the same. A$1 is an absolute row reference. The column will change when you copy it. eg if you copy it from column C to E the A will change to C. HTH -- Ian -- "nick" wrote in message ... i am using the following formula to populate certain fields.... =IF(ISERROR(INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10=$ A$10,ROW($A$1:$A$10)),ROW(1:1)),2)),"",INDEX($A$1: $B$10,SMALL(IF($A$1:$A$10=$A$10,ROW($A$1:$A$10)),R OW(1:1)),2)) i have 10 columns in workbook 1 and i am tryin to populate all the data from 10 columns of workbook 1 in to workbook 2. but the prob is if i use the above formula, i have to modify it in all the columns of workbook 2 to get the data from workbook 1. what makes my job easier is if i cld use the above function or any other to populate all the 10 columns of workbook1 in 10 different columns of workbook 2 without having to use the formula 10 different times and modifying it each time. its like writing the function in one cell and all the 10 cells get populated by itself in workbook 2 with the data from workbook 1. I used the above function as there are dupluicates in the data and vlookup dont work in that case. Any help wld be appreciated. |
|
#3
|
|||
|
|||
|
Assuming that you're going to enter the formula in B10, try the
following... B10, copied across and down: =IF(ROWS(B$10:B10)<=COUNTIF($A$1:$A$10,$A$10),INDE X(B$1:B$10,SMALL(IF($A$ 1:$A$10=$A$10,ROW($A$1:$A$10)-ROW($A$1)+1),ROWS(B$10:B10))),"") ....confirmed with CONTROL+SHIFT+ENTER. Make sure that you add the appropriate workbook and/or sheet names. Hope this helps! In article , "nick" wrote: i am using the following formula to populate certain fields.... =IF(ISERROR(INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10=$ A$10,ROW($A$1:$A$10)),ROW(1: 1)),2)),"",INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10=$A $10,ROW($A$1:$A$10)),ROW(1:1 )),2)) i have 10 columns in workbook 1 and i am tryin to populate all the data from 10 columns of workbook 1 in to workbook 2. but the prob is if i use the above formula, i have to modify it in all the columns of workbook 2 to get the data from workbook 1. what makes my job easier is if i cld use the above function or any other to populate all the 10 columns of workbook1 in 10 different columns of workbook 2 without having to use the formula 10 different times and modifying it each time. its like writing the function in one cell and all the 10 cells get populated by itself in workbook 2 with the data from workbook 1. I used the above function as there are dupluicates in the data and vlookup dont work in that case. Any help wld be appreciated. |
|
#4
|
|||
|
|||
|
In an effort to make it clear, let's assume that Sheet1 contains your
source data... On Sheet2... B10, copied across and down: =IF(ROWS(B$10:B10)<=COUNTIF(Sheet1!$A$1:$A$10,$A$1 0),INDEX(Sheet1!B$1:B$1 0,SMALL(IF(Sheet1!$A$1:$A$10=$A$10,ROW(Sheet1!$A$1 :$A$10)-ROW(Sheet1!$A$1 )+1),ROWS(B$10:B10))),"") ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Domenic wrote: Assuming that you're going to enter the formula in B10, try the following... B10, copied across and down: =IF(ROWS(B$10:B10)<=COUNTIF($A$1:$A$10,$A$10),INDE X(B$1:B$10,SMALL(IF($A$ 1:$A$10=$A$10,ROW($A$1:$A$10)-ROW($A$1)+1),ROWS(B$10:B10))),"") ...confirmed with CONTROL+SHIFT+ENTER. Make sure that you add the appropriate workbook and/or sheet names. Hope this helps! |
|
#5
|
|||
|
|||
|
Thanks alot to both of u....
"Domenic" wrote: Assuming that you're going to enter the formula in B10, try the following... B10, copied across and down: =IF(ROWS(B$10:B10)<=COUNTIF($A$1:$A$10,$A$10),INDE X(B$1:B$10,SMALL(IF($A$ 1:$A$10=$A$10,ROW($A$1:$A$10)-ROW($A$1)+1),ROWS(B$10:B10))),"") ....confirmed with CONTROL+SHIFT+ENTER. Make sure that you add the appropriate workbook and/or sheet names. Hope this helps! In article , "nick" wrote: i am using the following formula to populate certain fields.... =IF(ISERROR(INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10=$ A$10,ROW($A$1:$A$10)),ROW(1: 1)),2)),"",INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10=$A $10,ROW($A$1:$A$10)),ROW(1:1 )),2)) i have 10 columns in workbook 1 and i am tryin to populate all the data from 10 columns of workbook 1 in to workbook 2. but the prob is if i use the above formula, i have to modify it in all the columns of workbook 2 to get the data from workbook 1. what makes my job easier is if i cld use the above function or any other to populate all the 10 columns of workbook1 in 10 different columns of workbook 2 without having to use the formula 10 different times and modifying it each time. its like writing the function in one cell and all the 10 cells get populated by itself in workbook 2 with the data from workbook 1. I used the above function as there are dupluicates in the data and vlookup dont work in that case. Any help wld be appreciated. |
|
#6
|
|||
|
|||
|
hey i'm using the same formula, and would like to search with a wildcard
....can someone help? eg: IF(ISERROR(INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10=$A $10,ROW($A$1:$A$10)),ROW(1:1)),2)),"",INDEX($A$1:$ B$10,SMALL(IF($A$1:$A$10=$A$10,ROW($A$1:$A$10)),RO W(1:1)),2)) Now i need to replace (if($a$1:$a$10= ($a$10 (i need to add a wildcard here..how?,ROW......) "nick" wrote: Thanks alot to both of u.... "Domenic" wrote: Assuming that you're going to enter the formula in B10, try the following... B10, copied across and down: =IF(ROWS(B$10:B10)<=COUNTIF($A$1:$A$10,$A$10),INDE X(B$1:B$10,SMALL(IF($A$ 1:$A$10=$A$10,ROW($A$1:$A$10)-ROW($A$1)+1),ROWS(B$10:B10))),"") ....confirmed with CONTROL+SHIFT+ENTER. Make sure that you add the appropriate workbook and/or sheet names. Hope this helps! In article , "nick" wrote: i am using the following formula to populate certain fields.... =IF(ISERROR(INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10=$ A$10,ROW($A$1:$A$10)),ROW(1: 1)),2)),"",INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10=$A $10,ROW($A$1:$A$10)),ROW(1:1 )),2)) i have 10 columns in workbook 1 and i am tryin to populate all the data from 10 columns of workbook 1 in to workbook 2. but the prob is if i use the above formula, i have to modify it in all the columns of workbook 2 to get the data from workbook 1. what makes my job easier is if i cld use the above function or any other to populate all the 10 columns of workbook1 in 10 different columns of workbook 2 without having to use the formula 10 different times and modifying it each time. its like writing the function in one cell and all the 10 cells get populated by itself in workbook 2 with the data from workbook 1. I used the above function as there are dupluicates in the data and vlookup dont work in that case. Any help wld be appreciated. |
|
#7
|
|||
|
|||
|
Can you specify the criteria for A1:A10?
In article , xlarch wrote: hey i'm using the same formula, and would like to search with a wildcard ...can someone help? eg: IF(ISERROR(INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10=$A $10,ROW($A$1:$A$10)),ROW(1:1 )),2)),"",INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10=$A$ 10,ROW($A$1:$A$10)),ROW(1:1) ),2)) Now i need to replace (if($a$1:$a$10= ($a$10 (i need to add a wildcard here..how?,ROW......) |
|
#8
|
|||
|
|||
|
I don't quite understand what you mean by criteria for a1:a10. I'm pretty new
to excel. Just to put my question in perspective... this is an example of what i'm doing: http://office.microsoft.com/en-us/as...0corresponding As you will see at the bottom of the above page/link i put the value "ashish"in a10, and it returns corresponding numbers. if i replace "ashish" with "sh"in a10, i need to get all the corresponding values from column b, for every cell in column a that has "sh", which would mean it gives me the following values: a b 10 sh 234 11 534 12 634 13 734 14 834 since(sh is a part of a1,a4,a5,a6,a7) Hope this clarifies it a little better! Do let me know if you figure it out! Thanks for your help! "Domenic" wrote: Can you specify the criteria for A1:A10? In article , xlarch wrote: hey i'm using the same formula, and would like to search with a wildcard ...can someone help? eg: IF(ISERROR(INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10=$A $10,ROW($A$1:$A$10)),ROW(1:1 )),2)),"",INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10=$A$ 10,ROW($A$1:$A$10)),ROW(1:1) ),2)) Now i need to replace (if($a$1:$a$10= ($a$10 (i need to add a wildcard here..how?,ROW......) |
|
#9
|
|||
|
|||
|
To return the values in Column B where the last two characters for the
corresponding values in Column A is 'sh', try... B10, copied down: =IF(ROWS(B$10:B10)<=COUNTIF($A$1:$A$7,"*sh"),INDEX (B$1:B$7,SMALL(IF(RIGHT ($A$1:$A$7,2)=$A$10,ROW($A$1:$A$7)-ROW($A$1)+1),ROWS(B$10:B10))),"") ....confirmed with CONTROL+SHIFT+ENTER. To return the values in Column B where the corresponding values in Column A contain 'sh' anywhere in the string, try the following formula instead... B10, copied down: =IF(ROWS(B$10:B10)<=COUNTIF($A$1:$A$7,"*sh*"),INDE X(B$1:B$7,SMALL(IF(ISNU MBER(SEARCH($A$10,$A$1:$A$7)),ROW($A$1:$A$7)-ROW($A$1)+1),ROWS(B$10:B10)) ),"") ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , xlarch wrote: I don't quite understand what you mean by criteria for a1:a10. I'm pretty new to excel. Just to put my question in perspective... this is an example of what i'm doing: http://office.microsoft.com/en-us/as...#Return%20one% 20corresponding As you will see at the bottom of the above page/link i put the value "ashish"in a10, and it returns corresponding numbers. if i replace "ashish" with "sh"in a10, i need to get all the corresponding values from column b, for every cell in column a that has "sh", which would mean it gives me the following values: a b 10 sh 234 11 534 12 634 13 734 14 834 since(sh is a part of a1,a4,a5,a6,a7) Hope this clarifies it a little better! Do let me know if you figure it out! Thanks for your help! |
|
#10
|
|||
|
|||
|
Thanks for your help.
What if "sh" is a variable that i want to keep changing, i.e a10 is a variable, so that when i put different values in a10, i get different sets of data? Can either of the formulae (the two you sent me and the original one) be made to work in that way? thanks again Domenic "Domenic" wrote: To return the values in Column B where the last two characters for the corresponding values in Column A is 'sh', try... B10, copied down: =IF(ROWS(B$10:B10)<=COUNTIF($A$1:$A$7,"*sh"),INDEX (B$1:B$7,SMALL(IF(RIGHT ($A$1:$A$7,2)=$A$10,ROW($A$1:$A$7)-ROW($A$1)+1),ROWS(B$10:B10))),"") ....confirmed with CONTROL+SHIFT+ENTER. To return the values in Column B where the corresponding values in Column A contain 'sh' anywhere in the string, try the following formula instead... B10, copied down: =IF(ROWS(B$10:B10)<=COUNTIF($A$1:$A$7,"*sh*"),INDE X(B$1:B$7,SMALL(IF(ISNU MBER(SEARCH($A$10,$A$1:$A$7)),ROW($A$1:$A$7)-ROW($A$1)+1),ROWS(B$10:B10)) ),"") ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , xlarch wrote: I don't quite understand what you mean by criteria for a1:a10. I'm pretty new to excel. Just to put my question in perspective... this is an example of what i'm doing: http://office.microsoft.com/en-us/as...#Return%20one% 20corresponding As you will see at the bottom of the above page/link i put the value "ashish"in a10, and it returns corresponding numbers. if i replace "ashish" with "sh"in a10, i need to get all the corresponding values from column b, for every cell in column a that has "sh", which would mean it gives me the following values: a b 10 sh 234 11 534 12 634 13 734 14 834 since(sh is a part of a1,a4,a5,a6,a7) Hope this clarifies it a little better! Do let me know if you figure it out! Thanks for your help! |
|
#11
|
|||
|
|||
|
For the first formula...
=IF(ROWS(B$10:B10)<=COUNTIF($A$1:$A$7,"*"&$A$10),I NDEX(B$1:B$7,SMALL(IF(R IGHT($A$1:$A$7,2)=$A$10,ROW($A$1:$A$7)-ROW($A$1)+1),ROWS(B$10:B10))),"") For the second formula... =IF(ROWS(B$10:B10)<=COUNTIF($A$1:$A$7,"*"&$A$10&"* "),INDEX(B$1:B$7,SMALL( IF(ISNUMBER(SEARCH($A$10,$A$1:$A$7)),ROW($A$1:$A$7 )-ROW($A$1)+1),ROWS(B$1 0:B10))),"") Hope this helps! In article , xlarch wrote: Thanks for your help. What if "sh" is a variable that i want to keep changing, i.e a10 is a variable, so that when i put different values in a10, i get different sets of data? Can either of the formulae (the two you sent me and the original one) be made to work in that way? thanks again Domenic |
|
#12
|
|||
|
|||
|
it works! eureka!
Thank you ever so much! You made my day brother...regards |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
