| 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 |
| 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 | |||
Hybrid Mode
