| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
help..
i have the following given :
A B 1 asih 234 2 aa 334 3 bb 434 4 asih 534 5 cc 634 6 dd 734 7 asih 834 i managed to do the following : {INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=C1,ROW($A$1:$A $7)),ROW(1:1)),2)} and i dragged it to cell C3. NOW..... my purpose for this is to be able TO enter the word "asih" in C1 and C2 and C3 in order to be able its coresponding data which are : 234 , 534 , 834 MY QUESTION NOW IS : what should i do to insert the following formula : index(....),match(....) ) WITHIN THE PRECEDENT FORMULA: {INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=C1,ROW($A$1:$A $7)),ROW(1:1)),2)} AND THAT IN ORDER TO GET FOR EXAMPLE (734) IF I HAD TO TYPE (DD).???? THANKS FOR YOUR HELP |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help..
Hi,
With your search string in C1 Try this in D1 and drag down 3 rows =INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$C$1,ROW($B$ 1:$B$10)-ROW($B$1)+1),ROW(A1))) It's an array so commit with CTRL+Shift+Enter Mike "pierre" wrote: i have the following given : A B 1 asih 234 2 aa 334 3 bb 434 4 asih 534 5 cc 634 6 dd 734 7 asih 834 i managed to do the following : {INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=C1,ROW($A$1:$A $7)),ROW(1:1)),2)} and i dragged it to cell C3. NOW..... my purpose for this is to be able TO enter the word "asih" in C1 and C2 and C3 in order to be able its coresponding data which are : 234 , 534 , 834 MY QUESTION NOW IS : what should i do to insert the following formula : index(....),match(....) ) WITHIN THE PRECEDENT FORMULA: {INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=C1,ROW($A$1:$A $7)),ROW(1:1)),2)} AND THAT IN ORDER TO GET FOR EXAMPLE (734) IF I HAD TO TYPE (DD).???? THANKS FOR YOUR HELP |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help..
it worked ,THANK YOU SIR, but can you tell me more about this formula
especially this part : ROW($B$1:$B$10)-ROW($B$1)+1),ROW(A1))) AGAIN THANK YOU Hi, With your search string in C1 Try this in D1 and drag down 3 rows =INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$C$1,ROW($B$ 1:$B$10)-ROW($B$1)+1),ROW(A1))) It's an array so commit with CTRL+Shift+Enter |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help..
SUPPOSE now , i have the following data :
A B 1 ASIH 234 2 aa 334 3 BB 434 4 ASIH 534 5 cc 634 6 BB 734 7 ASIH 834 suppose i want to have the results of "asih" and " BB"...how to edit the formula : =INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$C$1,ROW($B$ 1:$B$10)-ROW($B$1)+1),ROW(A1))) PLEASE HELP "Mike H" wrote: Hi, With your search string in C1 Try this in D1 and drag down 3 rows =INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$C$1,ROW($B$ 1:$B$10)-ROW($B$1)+1),ROW(A1))) It's an array so commit with CTRL+Shift+Enter Mike "pierre" wrote: i have the following given : A B 1 asih 234 2 aa 334 3 bb 434 4 asih 534 5 cc 634 6 dd 734 7 asih 834 i managed to do the following : {INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=C1,ROW($A$1:$A $7)),ROW(1:1)),2)} and i dragged it to cell C3. NOW..... my purpose for this is to be able TO enter the word "asih" in C1 and C2 and C3 in order to be able its coresponding data which are : 234 , 534 , 834 MY QUESTION NOW IS : what should i do to insert the following formula : index(....),match(....) ) WITHIN THE PRECEDENT FORMULA: {INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=C1,ROW($A$1:$A $7)),ROW(1:1)),2)} AND THAT IN ORDER TO GET FOR EXAMPLE (734) IF I HAD TO TYPE (DD).???? THANKS FOR YOUR HELP |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help..
=INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$C$1,ROW($B$ 1:$B$10)-ROW($B$1)+1),ROW(A1)))
Try this: C1 = ASIH C2 = BB Array entered** in D1: =INDEX(A$1:A$7,SMALL(IF(($A$1:$A$7=$C$1)+($A$1:$A$ 7=$C$2),ROW(A$1:A$7)-MIN(ROW(A$1:A$7))+1),ROWS(D$1:D1))) Copy across to E1 then down until you get #NUM! errors meaning all the relative data has been extracted. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "pierre" wrote in message ... SUPPOSE now , i have the following data : A B 1 ASIH 234 2 aa 334 3 BB 434 4 ASIH 534 5 cc 634 6 BB 734 7 ASIH 834 suppose i want to have the results of "asih" and " BB"...how to edit the formula : =INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$C$1,ROW($B$ 1:$B$10)-ROW($B$1)+1),ROW(A1))) PLEASE HELP "Mike H" wrote: Hi, With your search string in C1 Try this in D1 and drag down 3 rows =INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$C$1,ROW($B$ 1:$B$10)-ROW($B$1)+1),ROW(A1))) It's an array so commit with CTRL+Shift+Enter Mike "pierre" wrote: i have the following given : A B 1 asih 234 2 aa 334 3 bb 434 4 asih 534 5 cc 634 6 dd 734 7 asih 834 i managed to do the following : {INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=C1,ROW($A$1:$A $7)),ROW(1:1)),2)} and i dragged it to cell C3. NOW..... my purpose for this is to be able TO enter the word "asih" in C1 and C2 and C3 in order to be able its coresponding data which are : 234 , 534 , 834 MY QUESTION NOW IS : what should i do to insert the following formula : index(....),match(....) ) WITHIN THE PRECEDENT FORMULA: {INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=C1,ROW($A$1:$A $7)),ROW(1:1)),2)} AND THAT IN ORDER TO GET FOR EXAMPLE (734) IF I HAD TO TYPE (DD).???? THANKS FOR YOUR HELP |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help..
THANK YOU SIR , I REALLY APPRECIATE IT ... IT WORKED NICE.....but could you
tell me more about : MIN(ROW(A$1:A$7))+1) ...what does this mean ? "Mike H" wrote: Hi, With your search string in C1 Try this in D1 and drag down 3 rows =INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$C$1,ROW($B$ 1:$B$10)-ROW($B$1)+1),ROW(A1))) It's an array so commit with CTRL+Shift+Enter Mike "pierre" wrote: i have the following given : A B 1 asih 234 2 aa 334 3 bb 434 4 asih 534 5 cc 634 6 dd 734 7 asih 834 i managed to do the following : {INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=C1,ROW($A$1:$A $7)),ROW(1:1)),2)} and i dragged it to cell C3. NOW..... my purpose for this is to be able TO enter the word "asih" in C1 and C2 and C3 in order to be able its coresponding data which are : 234 , 534 , 834 MY QUESTION NOW IS : what should i do to insert the following formula : index(....),match(....) ) WITHIN THE PRECEDENT FORMULA: {INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=C1,ROW($A$1:$A $7)),ROW(1:1)),2)} AND THAT IN ORDER TO GET FOR EXAMPLE (734) IF I HAD TO TYPE (DD).???? THANKS FOR YOUR HELP |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help..
MIN :
Returns the smallest number in a set of values. This portion is looking into the smallest of the array, it will store the reference in the variable "ROW"+ 1 Search the help file for MIN. "pierre" wrote: THANK YOU SIR , I REALLY APPRECIATE IT ... IT WORKED NICE.....but could you tell me more about : MIN(ROW(A$1:A$7))+1) ...what does this mean ? "Mike H" wrote: Hi, With your search string in C1 Try this in D1 and drag down 3 rows =INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$C$1,ROW($B$ 1:$B$10)-ROW($B$1)+1),ROW(A1))) It's an array so commit with CTRL+Shift+Enter Mike "pierre" wrote: i have the following given : A B 1 asih 234 2 aa 334 3 bb 434 4 asih 534 5 cc 634 6 dd 734 7 asih 834 i managed to do the following : {INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=C1,ROW($A$1:$A $7)),ROW(1:1)),2)} and i dragged it to cell C3. NOW..... my purpose for this is to be able TO enter the word "asih" in C1 and C2 and C3 in order to be able its coresponding data which are : 234 , 534 , 834 MY QUESTION NOW IS : what should i do to insert the following formula : index(....),match(....) ) WITHIN THE PRECEDENT FORMULA: {INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=C1,ROW($A$1:$A $7)),ROW(1:1)),2)} AND THAT IN ORDER TO GET FOR EXAMPLE (734) IF I HAD TO TYPE (DD).???? THANKS FOR YOUR HELP |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help..
This is hard to *clearly* explain and even harder for someone to understand.
Let's see if I can do a good job of explaining it... =INDEX(A$1:A$7,SMALL(IF(($A$1:$A$7=$C$1)+($A$1:$A$ 7=$C$2),ROW(A$1:A$7)-MIN(ROW(A$1:A$7))+1),ROWS(D$1:D1))) The INDEX function holds an array of values. These values are in specific positions relative to the array. In the above formula the indexed array is the range A1:A7. Their positions within the indexed array a A1 = position 1 A2 = position 2 A3 = position 3 A4 = position 4 ... A7 = position 7 The important thing to understand about these positions is that they're relative to the referenced range. If the indexed range was G6:G12 the relative positions would still be the same: G6 = position 1 G7 = position 2 G8 = position 3 G9 = position 4 ... G12 = position 7 In the above formula the logical test of the IF function returns an array of 1s and 0s. The 1s are evaluated as TRUE and the 0s are evaluated as FALSE. Where the logical test evaluates to TRUE (1s), the corresponding ROW number is passed to the SMALL function. We use the row number to tell the INDEX function which position of the indexed array to return as our result. Since the relative positions start at 1 and go to 7 (in this example) we have to make sure that the row numbers being passed to INDEX are the same as the position numbers. We do that using this expression: ROW(A$1:A$7)-MIN(ROW(A$1:A$7))+1 In this example the row numbers already correspond to the position numbers by virtue of the fact that our referenced range is A1:A7 and we use ROW(A1:A7). So: A1 = position 1 = ROW(A1) A2 = position 2 = ROW(A2) A3 = position 3 = ROW(A3) ... A7 = position 7 = ROW(A7) But, if the referenced range was G6:G12 and we used ROW(G6:G12) then the row numbers would not correspond to the position numbers: G6 = position 1 = ROW(G6) G7 = position 2 = ROW(G7) G8 = position 3 = ROW(G8) ... G12 = position 7 = ROW(G12) What we need to do is convert the row numbers 6:12 (a total of 7) to the row numbers 1:7 so that they correspond to the position numbers of the indexed array. Here's how we do that: ROW(G$6:G$12)-MIN(ROW(G$6:G$12))+1 We subtract the MIN row number from the array of row numbers then add 1. This is how that looks: ROW(G6) - ROW(G6) = 0 + 1 = 1 ROW(G7) - ROW(G6) = 1 + 1 = 2 ROW(G8) - ROW(G6) = 2 + 1 = 3 ROW(G9) - ROW(G6) = 3 + 1 = 4 ... ROW(G12) - ROW(G6) = 6 + 1 = 7 Now we have row numbers 1:7 that correspond to the position numbers 1:7 of the indexed array. -- Biff Microsoft Excel MVP "pierre" wrote in message ... THANK YOU SIR , I REALLY APPRECIATE IT ... IT WORKED NICE.....but could you tell me more about : MIN(ROW(A$1:A$7))+1) ...what does this mean ? "Mike H" wrote: Hi, With your search string in C1 Try this in D1 and drag down 3 rows =INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$C$1,ROW($B$ 1:$B$10)-ROW($B$1)+1),ROW(A1))) It's an array so commit with CTRL+Shift+Enter Mike "pierre" wrote: i have the following given : A B 1 asih 234 2 aa 334 3 bb 434 4 asih 534 5 cc 634 6 dd 734 7 asih 834 i managed to do the following : {INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=C1,ROW($A$1:$A $7)),ROW(1:1)),2)} and i dragged it to cell C3. NOW..... my purpose for this is to be able TO enter the word "asih" in C1 and C2 and C3 in order to be able its coresponding data which are : 234 , 534 , 834 MY QUESTION NOW IS : what should i do to insert the following formula : index(....),match(....) ) WITHIN THE PRECEDENT FORMULA: {INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=C1,ROW($A$1:$A $7)),ROW(1:1)),2)} AND THAT IN ORDER TO GET FOR EXAMPLE (734) IF I HAD TO TYPE (DD).???? THANKS FOR YOUR HELP |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help..
THANK YOU SO MUCH....now i have learned something new, thanks to you ...and i
added it to my EXCEL arsenal :) indexed array a A1 = position 1 A2 = position 2 A3 = position 3 A4 = position 4 ... A7 = position 7 The important thing to understand about these positions is that they're relative to the referenced range. If the indexed range was G6:G12 the relative positions would still be the same: G6 = position 1 G7 = position 2 G8 = position 3 G9 = position 4 ... G12 = position 7 In the above formula the logical test of the IF function returns an array of 1s and 0s. The 1s are evaluated as TRUE and the 0s are evaluated as FALSE. Where the logical test evaluates to TRUE (1s), the corresponding ROW number is passed to the SMALL function. We use the row number to tell the INDEX function which position of the indexed array to return as our result. Since the relative positions start at 1 and go to 7 (in this example) we have to make sure that the row numbers being passed to INDEX are the same as the position numbers. We do that using this expression: ROW(A$1:A$7)-MIN(ROW(A$1:A$7))+1 In this example the row numbers already correspond to the position numbers by virtue of the fact that our referenced range is A1:A7 and we use ROW(A1:A7). So: A1 = position 1 = ROW(A1) A2 = position 2 = ROW(A2) A3 = position 3 = ROW(A3) ... A7 = position 7 = ROW(A7) But, if the referenced range was G6:G12 and we used ROW(G6:G12) then the row numbers would not correspond to the position numbers: G6 = position 1 = ROW(G6) G7 = position 2 = ROW(G7) G8 = position 3 = ROW(G8) ... G12 = position 7 = ROW(G12) What we need to do is convert the row numbers 6:12 (a total of 7) to the row numbers 1:7 so that they correspond to the position numbers of the indexed array. Here's how we do that: ROW(G$6:G$12)-MIN(ROW(G$6:G$12))+1 We subtract the MIN row number from the array of row numbers then add 1. This is how that looks: ROW(G6) - ROW(G6) = 0 + 1 = 1 ROW(G7) - ROW(G6) = 1 + 1 = 2 ROW(G8) - ROW(G6) = 2 + 1 = 3 ROW(G9) - ROW(G6) = 3 + 1 = 4 ... ROW(G12) - ROW(G6) = 6 + 1 = 7 Now we have row numbers 1:7 that correspond to the position numbers 1:7 of the indexed array. -- Biff Microsoft Excel MVP "pierre" wrote in message ... THANK YOU SIR , I REALLY APPRECIATE IT ... IT WORKED NICE.....but could you tell me more about : MIN(ROW(A$1:A$7))+1) ...what does this mean ? "Mike H" wrote: Hi, With your search string in C1 Try this in D1 and drag down 3 rows =INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$C$1,ROW($B$ 1:$B$10)-ROW($B$1)+1),ROW(A1))) It's an array so commit with CTRL+Shift+Enter Mike "pierre" wrote: i have the following given : A B 1 asih 234 2 aa 334 3 bb 434 4 asih 534 5 cc 634 6 dd 734 7 asih 834 i managed to do the following : {INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=C1,ROW($A$1:$A $7)),ROW(1:1)),2)} and i dragged it to cell C3. NOW..... my purpose for this is to be able TO enter the word "asih" in C1 and C2 and C3 in order to be able its coresponding data which are : 234 , 534 , 834 MY QUESTION NOW IS : what should i do to insert the following formula : index(....),match(....) ) WITHIN THE PRECEDENT FORMULA: {INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=C1,ROW($A$1:$A $7)),ROW(1:1)),2)} AND THAT IN ORDER TO GET FOR EXAMPLE (734) IF I HAD TO TYPE (DD).???? THANKS FOR YOUR HELP |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help..
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "pierre" wrote in message ... THANK YOU SO MUCH....now i have learned something new, thanks to you ...and i added it to my EXCEL arsenal :) indexed array a A1 = position 1 A2 = position 2 A3 = position 3 A4 = position 4 ... A7 = position 7 The important thing to understand about these positions is that they're relative to the referenced range. If the indexed range was G6:G12 the relative positions would still be the same: G6 = position 1 G7 = position 2 G8 = position 3 G9 = position 4 ... G12 = position 7 In the above formula the logical test of the IF function returns an array of 1s and 0s. The 1s are evaluated as TRUE and the 0s are evaluated as FALSE. Where the logical test evaluates to TRUE (1s), the corresponding ROW number is passed to the SMALL function. We use the row number to tell the INDEX function which position of the indexed array to return as our result. Since the relative positions start at 1 and go to 7 (in this example) we have to make sure that the row numbers being passed to INDEX are the same as the position numbers. We do that using this expression: ROW(A$1:A$7)-MIN(ROW(A$1:A$7))+1 In this example the row numbers already correspond to the position numbers by virtue of the fact that our referenced range is A1:A7 and we use ROW(A1:A7). So: A1 = position 1 = ROW(A1) A2 = position 2 = ROW(A2) A3 = position 3 = ROW(A3) ... A7 = position 7 = ROW(A7) But, if the referenced range was G6:G12 and we used ROW(G6:G12) then the row numbers would not correspond to the position numbers: G6 = position 1 = ROW(G6) G7 = position 2 = ROW(G7) G8 = position 3 = ROW(G8) ... G12 = position 7 = ROW(G12) What we need to do is convert the row numbers 6:12 (a total of 7) to the row numbers 1:7 so that they correspond to the position numbers of the indexed array. Here's how we do that: ROW(G$6:G$12)-MIN(ROW(G$6:G$12))+1 We subtract the MIN row number from the array of row numbers then add 1. This is how that looks: ROW(G6) - ROW(G6) = 0 + 1 = 1 ROW(G7) - ROW(G6) = 1 + 1 = 2 ROW(G8) - ROW(G6) = 2 + 1 = 3 ROW(G9) - ROW(G6) = 3 + 1 = 4 ... ROW(G12) - ROW(G6) = 6 + 1 = 7 Now we have row numbers 1:7 that correspond to the position numbers 1:7 of the indexed array. -- Biff Microsoft Excel MVP "pierre" wrote in message ... THANK YOU SIR , I REALLY APPRECIATE IT ... IT WORKED NICE.....but could you tell me more about : MIN(ROW(A$1:A$7))+1) ...what does this mean ? "Mike H" wrote: Hi, With your search string in C1 Try this in D1 and drag down 3 rows =INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$C$1,ROW($B$ 1:$B$10)-ROW($B$1)+1),ROW(A1))) It's an array so commit with CTRL+Shift+Enter Mike "pierre" wrote: i have the following given : A B 1 asih 234 2 aa 334 3 bb 434 4 asih 534 5 cc 634 6 dd 734 7 asih 834 i managed to do the following : {INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=C1,ROW($A$1:$A $7)),ROW(1:1)),2)} and i dragged it to cell C3. NOW..... my purpose for this is to be able TO enter the word "asih" in C1 and C2 and C3 in order to be able its coresponding data which are : 234 , 534 , 834 MY QUESTION NOW IS : what should i do to insert the following formula : index(....),match(....) ) WITHIN THE PRECEDENT FORMULA: {INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=C1,ROW($A$1:$A $7)),ROW(1:1)),2)} AND THAT IN ORDER TO GET FOR EXAMPLE (734) IF I HAD TO TYPE (DD).???? THANKS FOR YOUR HELP |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
