| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel Function for select data which NOT exist from the list
There are 2 data sets are assumed running in A3 and in B3 down, viz.:
In A3 down is: A, B, C, D, E, F, G, H, I, J, K and L In B3 down is: B, A, H, C, F, I, K, J, H, C and J. The result which I want is to list or select the data which is NOT exist in the second set of data (Col B) when compare to the first set of data. In my example, the result is D, E, G, and L actomactic appear in cell C3, C4, C5 and C6 accordingly. I need an Excel formula to have this result! Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200804/1 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Excel Function for select data which NOT exist from the list
Try this:
A3:A14 = rng1 B3:B13 = rng2 Enter this array formula** in C3 and copy down until you get blanks: =IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(rng2,rng1)=0)),INDEX(rng1,SMALL(IF(COUNTI F(rng2,rng1)=0,ROW(rng1)-MIN(ROW(rng1))+1),ROWS(C$3:C3))),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" <u43231@uwe wrote in message news:836e39f005b4d@uwe... There are 2 data sets are assumed running in A3 and in B3 down, viz.: In A3 down is: A, B, C, D, E, F, G, H, I, J, K and L In B3 down is: B, A, H, C, F, I, K, J, H, C and J. The result which I want is to list or select the data which is NOT exist in the second set of data (Col B) when compare to the first set of data. In my example, the result is D, E, G, and L actomactic appear in cell C3, C4, C5 and C6 accordingly. I need an Excel formula to have this result! Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200804/1 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Excel Function for select data which NOT exist from the list
Hello Biff,
Many thanks for your help! First of all, I tried the formula but failed to work. May be this is my problem of understanding the formula. In your note, it indicates that A3:A14 = rng1 & B3:B13 = rng2. As a result, in the function of COUNTIF(rng2,rng1), I convert it to COUNTIF(B3:B13,A3), am I right? Please help me on this! The second question is the function of ROW(rng1), I converted it to ROW(A3: A14), am I right? Many thanks for your advice! Wilchong T. Valko wrote: Try this: A3:A14 = rng1 B3:B13 = rng2 Enter this array formula** in C3 and copy down until you get blanks: =IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(rng2,rng1)=0)),INDEX(rng1,SMALL(IF(COUNTI F(rng2,rng1)=0,ROW(rng1)-MIN(ROW(rng1))+1),ROWS(C$3:C3))),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) There are 2 data sets are assumed running in A3 and in B3 down, viz.: In A3 down is: A, B, C, D, E, F, G, H, I, J, K and L [quoted text clipped - 11 lines] Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200804/1 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Excel Function for select data which NOT exist from the list
Here it is with the references:
=IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(B$3:B$13,A$3:A$14)=0)),INDEX(A$3:A$14,SMA LL(IF(COUNTIF(B$3:B$13,A$3:A$14)=0,ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1),ROWS(C$3:C3))),"") Be sure to enter it as an array or it won't work properly: ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" <u43231@uwe wrote in message news:83709ae42b123@uwe... Hello Biff, Many thanks for your help! First of all, I tried the formula but failed to work. May be this is my problem of understanding the formula. In your note, it indicates that A3:A14 = rng1 & B3:B13 = rng2. As a result, in the function of COUNTIF(rng2,rng1), I convert it to COUNTIF(B3:B13,A3), am I right? Please help me on this! The second question is the function of ROW(rng1), I converted it to ROW(A3: A14), am I right? Many thanks for your advice! Wilchong T. Valko wrote: Try this: A3:A14 = rng1 B3:B13 = rng2 Enter this array formula** in C3 and copy down until you get blanks: =IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(rng2,rng1)=0)),INDEX(rng1,SMALL(IF(COUNTI F(rng2,rng1)=0,ROW(rng1)-MIN(ROW(rng1))+1),ROWS(C$3:C3))),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) There are 2 data sets are assumed running in A3 and in B3 down, viz.: In A3 down is: A, B, C, D, E, F, G, H, I, J, K and L [quoted text clipped - 11 lines] Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200804/1 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Excel Function for select data which NOT exist from the list
Thank you Biff.
The formula is working, however, the formula ONLY list "D" from cell C3 to C14. Do you think the existing formula can be adjusted or revised to the result I want, that is D, E, G, and L actomactic appear in cell C3, C4, C5 and C6 accordingly and C7 to C14 are the cells not display any thing. Many thanks for your effort and time again! Wilchong T. Valko wrote: Here it is with the references: =IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(B$3:B$13,A$3:A$14)=0)),INDEX(A$3:A$14,SMA LL(IF(COUNTIF(B$3:B$13,A$3:A$14)=0,ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1),ROWS(C$3:C3))),"") Be sure to enter it as an array or it won't work properly: ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Hello Biff, Many thanks for your help! First of all, I tried the formula but failed [quoted text clipped - 31 lines] Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200804/1 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Excel Function for select data which NOT exist from the list
The formula works.
It sounds like you might have calculation set to manual. Make sure calculation is set to Automatic: ToolsOptionsCalculation tabAutomatic -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" <u43231@uwe wrote in message news:837102ca9cf99@uwe... Thank you Biff. The formula is working, however, the formula ONLY list "D" from cell C3 to C14. Do you think the existing formula can be adjusted or revised to the result I want, that is D, E, G, and L actomactic appear in cell C3, C4, C5 and C6 accordingly and C7 to C14 are the cells not display any thing. Many thanks for your effort and time again! Wilchong T. Valko wrote: Here it is with the references: =IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(B$3:B$13,A$3:A$14)=0)),INDEX(A$3:A$14,SMA LL(IF(COUNTIF(B$3:B$13,A$3:A$14)=0,ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1),ROWS(C$3:C3))),"") Be sure to enter it as an array or it won't work properly: ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Hello Biff, Many thanks for your help! First of all, I tried the formula but failed [quoted text clipped - 31 lines] Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200804/1 |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Excel Function for select data which NOT exist from the list
The named ranges are self-explanatory.
=IF(SUM(N(COUNTIF(BB,AA)=0))<ROW(A1),"",INDIRECT(" A"&SMALL(IF(COUNTIF(BB,AA)=0,ROW(AA)),ROW(A1))) ) Put this in cell C3 using Ctrl + Shift + Enter, then fill/copy down as required. The suggestions provided by T. Valko and this one do work, but they based on the examples you posted. If the return value is not in the first column you'll need to adjust the formula yourself - like the "A" bit for example to the letter for that column. The reason these are different, is there are many ways to do the same thing in XL, and we do love arguing ...er, I mean discussing which one is best :) The formula is working, however, the formula ONLY list "D" from cell C3 to C14. This indicates you have failed to understand how to enter array formulae (look it up), or have array entered all at once - not for this solution. Regards Robert McCurdy "wilchong via OfficeKB.com" <u43231@uwe wrote in message news:837102ca9cf99@uwe... Thank you Biff. The formula is working, however, the formula ONLY list "D" from cell C3 to C14. Do you think the existing formula can be adjusted or revised to the result I want, that is D, E, G, and L actomactic appear in cell C3, C4, C5 and C6 accordingly and C7 to C14 are the cells not display any thing. Many thanks for your effort and time again! Wilchong T. Valko wrote: Here it is with the references: =IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(B$3:B$13,A$3:A$14)=0)),INDEX(A$3:A$14,SMA LL(IF(COUNTIF(B$3:B$13,A$3:A$14)=0,ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1),ROWS(C$3:C3))),"") Be sure to enter it as an array or it won't work properly: ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Hello Biff, Many thanks for your help! First of all, I tried the formula but failed [quoted text clipped - 31 lines] Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200804/1 |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Excel Function for select data which NOT exist from the list
Thank Robert and Biff,
Let me study your instruction carefully first, I will let your all know the result! Many thanks, Wilson Robert McCurdy wrote: The named ranges are self-explanatory. =IF(SUM(N(COUNTIF(BB,AA)=0))<ROW(A1),"",INDIRECT( "A"&SMALL(IF(COUNTIF(BB,AA)=0,ROW(AA)),ROW(A1) ))) Put this in cell C3 using Ctrl + Shift + Enter, then fill/copy down as required. The suggestions provided by T. Valko and this one do work, but they based on the examples you posted. If the return value is not in the first column you'll need to adjust the formula yourself - like the "A" bit for example to the letter for that column. The reason these are different, is there are many ways to do the same thing in XL, and we do love arguing ...er, I mean discussing which one is best :) The formula is working, however, the formula ONLY list "D" from cell C3 to C14. This indicates you have failed to understand how to enter array formulae (look it up), or have array entered all at once - not for this solution. Regards Robert McCurdy Thank you Biff. The formula is working, however, the formula ONLY list "D" from cell C3 to [quoted text clipped - 22 lines] Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Excel Function for select data which NOT exist from the list
Hello Biff,
I have checked with my Excel spreadsheet and confirmed to you that the "calculation" in spreadsheet is set "Automatic"! The formula still doesn't work exactly! Thanks, Wilchong T. Valko wrote: The formula works. It sounds like you might have calculation set to manual. Make sure calculation is set to Automatic: ToolsOptionsCalculation tabAutomatic Thank you Biff. The formula is working, however, the formula ONLY list "D" from cell C3 to [quoted text clipped - 23 lines] Many thanks, Wilchong -- Message posted via http://www.officekb.com |
|
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Excel Function for select data which NOT exist from the list
Here's a small sample file that demonstrates this:
xExtractMissing.xls 15kb http://cjoint.com/?fcepu2wnPz -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" <u43231@uwe wrote in message news:8386ef316b61a@uwe... Hello Biff, I have checked with my Excel spreadsheet and confirmed to you that the "calculation" in spreadsheet is set "Automatic"! The formula still doesn't work exactly! Thanks, Wilchong T. Valko wrote: The formula works. It sounds like you might have calculation set to manual. Make sure calculation is set to Automatic: ToolsOptionsCalculation tabAutomatic Thank you Biff. The formula is working, however, the formula ONLY list "D" from cell C3 to [quoted text clipped - 23 lines] Many thanks, Wilchong -- Message posted via http://www.officekb.com |
|
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Excel Function for select data which NOT exist from the list
Good Morning Biff,
I got the file, I will study it carefully! Many thanks for your help! Wilchong T. Valko wrote: Here's a small sample file that demonstrates this: xExtractMissing.xls 15kb http://cjoint.com/?fcepu2wnPz Hello Biff, I have checked with my Excel spreadsheet and confirmed to you that the [quoted text clipped - 18 lines] Many thanks, Wilchong -- Message posted via http://www.officekb.com |
|
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Excel Function for select data which NOT exist from the list
Dear Biff,
Thank you so much for your Excel demo file. I finally found out what wrong with me! It is because my insufficient knowledge regarding the function "enter" procedure. The reason I did wrong is because I highlighed the whole cell range from C3 to C14 and press CTRL, SHIFT and ENTER at the same time. Before, it is the only way I know. Right now, I know CTRL, SHIFT and ENTER can enter in one cell first and copy! Actually, may I ask you one question, what is the logic in your function which can list the missing data from second set of data? I saw you have put -- infront of COUNTIF function, what is that mean? I am so appreciate your effort, time and patient! So thankful! Wilchong T. Valko wrote: Here's a small sample file that demonstrates this: xExtractMissing.xls 15kb http://cjoint.com/?fcepu2wnPz Hello Biff, I have checked with my Excel spreadsheet and confirmed to you that the [quoted text clipped - 18 lines] Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
|
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Excel Function for select data which NOT exist from the list
Actually, may I ask you one question, what is the logic in your function
which can list the missing data from second set of data? I saw you have put -- infront of COUNTIF function, what is that mean? We use COUNTIF to identify which items are missing from column B. INDEX(A$3:A$14,SMALL(IF(COUNTIF(B$3:B$13,A$3:A$14) =0,ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1),ROWS(C$3:C3))) The COUNTIF function returns an array of counts like this: COUNTIF(B$3:B$13,A3) = 1 COUNTIF(B$3:B$13,A4) = 1 COUNTIF(B$3:B$13,A5) = 2 COUNTIF(B$3:B$13,A6) = 0 COUNTIF(B$3:B$13,A7) = 0 etc etc Where the result of that count is 0 we use this expression to calculate what row the corresponding value is located on. ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1 That number is then passed to the INDEX function to produce the desired result. -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" <u43231@uwe wrote in message news:838837beccb0a@uwe... Dear Biff, Thank you so much for your Excel demo file. I finally found out what wrong with me! It is because my insufficient knowledge regarding the function "enter" procedure. The reason I did wrong is because I highlighed the whole cell range from C3 to C14 and press CTRL, SHIFT and ENTER at the same time. Before, it is the only way I know. Right now, I know CTRL, SHIFT and ENTER can enter in one cell first and copy! Actually, may I ask you one question, what is the logic in your function which can list the missing data from second set of data? I saw you have put -- infront of COUNTIF function, what is that mean? I am so appreciate your effort, time and patient! So thankful! Wilchong T. Valko wrote: Here's a small sample file that demonstrates this: xExtractMissing.xls 15kb http://cjoint.com/?fcepu2wnPz Hello Biff, I have checked with my Excel spreadsheet and confirmed to you that the [quoted text clipped - 18 lines] Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
|
#14
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Excel Function for select data which NOT exist from the list
Dear Biff,
Many thank for your explanation. Actually, another point of the Excel function is that why you put "--" before (COUNTIF(B$3:B$13,A$3:A$14)=0)), INDEX(€¦€¦€¦€¦€¦! What is the purpose of "--" if mix with Excel function! Thank for your time and effort again! Wilchong T. Valko wrote: Actually, may I ask you one question, what is the logic in your function which can list the missing data from second set of data? I saw you have put -- infront of COUNTIF function, what is that mean? We use COUNTIF to identify which items are missing from column B. INDEX(A$3:A$14,SMALL(IF(COUNTIF(B$3:B$13,A$3:A$14 )=0,ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1),ROWS(C$3:C3))) The COUNTIF function returns an array of counts like this: COUNTIF(B$3:B$13,A3) = 1 COUNTIF(B$3:B$13,A4) = 1 COUNTIF(B$3:B$13,A5) = 2 COUNTIF(B$3:B$13,A6) = 0 COUNTIF(B$3:B$13,A7) = 0 etc etc Where the result of that count is 0 we use this expression to calculate what row the corresponding value is located on. ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1 That number is then passed to the INDEX function to produce the desired result. Dear Biff, Thank you so much for your Excel demo file. I finally found out what [quoted text clipped - 28 lines] Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
|
#15
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Excel Function for select data which NOT exist from the list
The "--" converts TRUE and FALSE to 1 and 0 respectively.
This expression will return an array of either TRUE or FALSE: COUNTIF(B$3:B$13,A$3:A$14)=0 The "--" converts those either 1 or 0: --(COUNTIF(B$3:B$13,A$3:A$14)=0) Then the SUM function adds those 1s and 0s to get a total: SUM(--(COUNTIF(B$3:B$13,A$3:A$14)=0)) -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" <u43231@uwe wrote in message news:83b02c6892c7c@uwe... Dear Biff, Many thank for your explanation. Actually, another point of the Excel function is that why you put "--" before (COUNTIF(B$3:B$13,A$3:A$14)=0)), INDEX(.....! What is the purpose of "--" if mix with Excel function! Thank for your time and effort again! Wilchong T. Valko wrote: Actually, may I ask you one question, what is the logic in your function which can list the missing data from second set of data? I saw you have put -- infront of COUNTIF function, what is that mean? We use COUNTIF to identify which items are missing from column B. INDEX(A$3:A$14,SMALL(IF(COUNTIF(B$3:B$13,A$3:A$1 4)=0,ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1),ROWS(C$3:C3))) The COUNTIF function returns an array of counts like this: COUNTIF(B$3:B$13,A3) = 1 COUNTIF(B$3:B$13,A4) = 1 COUNTIF(B$3:B$13,A5) = 2 COUNTIF(B$3:B$13,A6) = 0 COUNTIF(B$3:B$13,A7) = 0 etc etc Where the result of that count is 0 we use this expression to calculate what row the corresponding value is located on. ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1 That number is then passed to the INDEX function to produce the desired result. Dear Biff, Thank you so much for your Excel demo file. I finally found out what [quoted text clipped - 28 lines] Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
|
#16
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Excel Function for select data which NOT exist from the list
Dear T. Valko,
Really thank for your help. I am thinking how to revise the existing Excel function if I want the Excel formula extracts the data which has appear in col B. In my early example, the result will be A, B, C, F, H, I, J,and K actomactic appear from the cell C3 to C10 accordingly. I need an Excel formula to have this result! It is just the OPPOSITE what the function do for "extracting missing data"! Many thanks, Wilchong T. Valko wrote: The "--" converts TRUE and FALSE to 1 and 0 respectively. This expression will return an array of either TRUE or FALSE: COUNTIF(B$3:B$13,A$3:A$14)=0 The "--" converts those either 1 or 0: --(COUNTIF(B$3:B$13,A$3:A$14)=0) Then the SUM function adds those 1s and 0s to get a total: SUM(--(COUNTIF(B$3:B$13,A$3:A$14)=0)) Dear Biff, Many thank for your explanation. Actually, another point of the Excel [quoted text clipped - 39 lines] Many thanks, Wilchong -- Message posted via http://www.officekb.com |
|
#17
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Excel Function for select data which NOT exist from the list
You can use the same formula just change:
=0 to 0 (in 2 places) =IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(rng2,rng1)0)),INDEX(rng1,SMALL(IF(COUNTI F(rng2,rng1)0,ROW(rng1)-MIN(ROW(rng1))+1),ROWS(C$3:C3))),"") Don't forget: array entered! -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" <u43231@uwe wrote in message news:83b96cc0ca763@uwe... Dear T. Valko, Really thank for your help. I am thinking how to revise the existing Excel function if I want the Excel formula extracts the data which has appear in col B. In my early example, the result will be A, B, C, F, H, I, J,and K actomactic appear from the cell C3 to C10 accordingly. I need an Excel formula to have this result! It is just the OPPOSITE what the function do for "extracting missing data"! Many thanks, Wilchong T. Valko wrote: The "--" converts TRUE and FALSE to 1 and 0 respectively. This expression will return an array of either TRUE or FALSE: COUNTIF(B$3:B$13,A$3:A$14)=0 The "--" converts those either 1 or 0: --(COUNTIF(B$3:B$13,A$3:A$14)=0) Then the SUM function adds those 1s and 0s to get a total: SUM(--(COUNTIF(B$3:B$13,A$3:A$14)=0)) Dear Biff, Many thank for your explanation. Actually, another point of the Excel [quoted text clipped - 39 lines] Many thanks, Wilchong -- Message posted via http://www.officekb.com |
|
#18
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Excel Function for select data which NOT exist from the list
Dear T. Valko,
Many thanks! It works extremely well! Thanks, Wilchong T. Valko wrote: You can use the same formula just change: =0 to 0 (in 2 places) =IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(rng2,rng1)0)),INDEX(rng1,SMALL(IF(COUNTI F(rng2,rng1)0,ROW(rng1)-MIN(ROW(rng1))+1),ROWS(C$3:C3))),"") Don't forget: array entered! Dear T. Valko, Really thank for your help. I am thinking how to revise the existing Excel [quoted text clipped - 29 lines] Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
|
#19
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Excel Function for select data which NOT exist from the list
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" <u43231@uwe wrote in message news:83ba7aadcab8c@uwe... Dear T. Valko, Many thanks! It works extremely well! Thanks, Wilchong T. Valko wrote: You can use the same formula just change: =0 to 0 (in 2 places) =IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(rng2,rng1)0)),INDEX(rng1,SMALL(IF(COUNTI F(rng2,rng1)0,ROW(rng1)-MIN(ROW(rng1))+1),ROWS(C$3:C3))),"") Don't forget: array entered! Dear T. Valko, Really thank for your help. I am thinking how to revise the existing Excel [quoted text clipped - 29 lines] Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200805/1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| DATA VALIDATION LIST- CAN I SELECT FROM LIST WITHOUT SCROLLING | Excel Worksheet Functions | |||
| Function to select random values from a list. | Excel Worksheet Functions | |||
| how do you select these data from a list? | Excel Discussion (Misc queries) | |||
| What function to select the last 3 small values from a list ? | Excel Discussion (Misc queries) | |||
| What function to select the last 3 small values from a list ? | Excel Worksheet Functions | |||
Linear Mode
