| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
eliminating matched records!
hi!
sorry..the data in my earlier post was wrong and the correct one is as follows: i'm having some id in colA & numbers (may be "+" or "-") in colB as under! how can i eliminate the matched pairs of "+" & "-" with ref to the id in colA? -sample data- colA colB 111 50 112 100 113 -50 114 -100 111 -50 115 150 112 -50 114 50 114 -50 113 100 what i want is using some formula in colC can i flag off the record nos 1,5,8 & 9 the subtotal of the flagged records should be always zero! and able to retain the remaining records! -via135 -- Message posted via http://www.officekb.com |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
hi!
The simplest solution is pivot table. "via135 via OfficeKB.com д " hi! sorry..the data in my earlier post was wrong and the correct one is as follows: i'm having some id in colA & numbers (may be "+" or "-") in colB as under! how can i eliminate the matched pairs of "+" & "-" with ref to the id in colA? -sample data- colA colB 111 50 112 100 113 -50 114 -100 111 -50 115 150 112 -50 114 50 114 -50 113 100 what i want is using some formula in colC can i flag off the record nos 1,5,8 & 9 the subtotal of the flagged records should be always zero! and able to retain the remaining records! -via135 -- Message posted via http://www.officekb.com |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
|
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
Hi
One way In cell C2 enter the following and copy down =SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2)) There will be 1's against the matching items and 0's against the rest. DataFilterAutofilteruse dropdown on column C to select rows with 1 -- Regards Roger Govier "via135 via OfficeKB.com" <u23552@uwe wrote in message news:693b3ffc4e3e6@uwe... hi! i don't think so! any other help pl? -via135 wrote: hi! The simplest solution is pivot table. "via135 via OfficeKB.com ??: " hi! sorry..the data in my earlier post [quoted text clipped - 29 lines] -via135 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200611/1 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
hi Roger!
i am getting the following results: 111 50 1 112 100 1 113 -50 1 114 -100 1 111 -50 1 115 150 1 112 -50 1 114 50 2 114 -50 2 113 100 1 -via135 Roger Govier wrote: Hi One way In cell C2 enter the following and copy down =SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2)) There will be 1's against the matching items and 0's against the rest. DataFilterAutofilteruse dropdown on column C to select rows with 1 hi! [quoted text clipped - 13 lines] -via135 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200611/1 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
Hi
You cannot have entered the formula as I gave you. I get the following results 111 50 1 112 100 0 113 -50 0 114 -100 0 111 -50 1 115 150 0 112 -50 0 114 50 1 114 -50 1 113 100 0 -- Regards Roger Govier "via135 via OfficeKB.com" <u23552@uwe wrote in message news:693c38a347d8e@uwe... hi Roger! i am getting the following results: 111 50 1 112 100 1 113 -50 1 114 -100 1 111 -50 1 115 150 1 112 -50 1 114 50 2 114 -50 2 113 100 1 -via135 Roger Govier wrote: Hi One way In cell C2 enter the following and copy down =SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2)) There will be 1's against the matching items and 0's against the rest. DataFilterAutofilteruse dropdown on column C to select rows with 1 hi! [quoted text clipped - 13 lines] -via135 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200611/1 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
yes..you are absolutely right!
i have given "B2" i / o "-B2"..! one more question..the formula gives the right answer only when the each ref no in col A is having some unique value col B..! when the same value is repeated in col B for the same ref no in col A there seems to be some glitch! for example if i extend the data to A2:B15 as under.. A........B 111.... 50 112.... 100 113.... -50 114.... -100 111.... -50 115.... 150 112.... -50 114.... 50 114.... -50 113.... 100 111.... 50 113.... -100 111.... -50 114.... -50 what i am getting is A.......B........C (getting) C (expecting) 111... 50... 2.......................1 112... 100... 0......................0 113... -50... 0.......................0 114... -100... 0......................0 111... -50... 2.......................1 115... 150... 0......................0 112... -50... 0.......................0 114... 50... 2.......................1 114... -50... 1.......................1 113... 100... 1......................1 111... 50... 2.......................1 113... -100.. 1......................1 111... -50... 2.......................1 114... -50... 1.......................0 any suggestion for alteration in the function? -via135 On Nov 13, 10:53 pm, "Roger Govier" wrote: Hi You cannot have entered the formula as I gave you. I get the following results 111 50 1 112 100 0 113 -50 0 114 -100 0 111 -50 1 115 150 0 112 -50 0 114 50 1 114 -50 1 113 100 0 -- Regards Roger Govier "via135 via OfficeKB.com" <u23552@uwe wrote in messagenews:693c38a347d8e@uwe... hi Roger! i am getting the following results: 111 50 1 112 100 1 113 -50 1 114 -100 1 111 -50 1 115 150 1 112 -50 1 114 50 2 114 -50 2 113 100 1 -via135 Roger Govier wrote: Hi One way In cell C2 enter the following and copy down =SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2)) There will be 1's against the matching items and 0's against the rest. DataFilterAutofilteruse dropdown on column C to select rows with 1 hi! [quoted text clipped - 13 lines] -via135 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200611/1 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
Hi
The formula is working correctly.When you apply the Autofilter, use the dropdown on column C choose CustomGreater than0 This will then show your rows which are duplicated. Alternatively, you could wrap the formula in an IF statement =IF(SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2))0,"Duplicate","") Filter column C for Duplicate -- Regards Roger Govier "via135" wrote in message ps.com... yes..you are absolutely right! i have given "B2" i / o "-B2"..! one more question..the formula gives the right answer only when the each ref no in col A is having some unique value col B..! when the same value is repeated in col B for the same ref no in col A there seems to be some glitch! for example if i extend the data to A2:B15 as under.. A........B 111.... 50 112.... 100 113.... -50 114.... -100 111.... -50 115.... 150 112.... -50 114.... 50 114.... -50 113.... 100 111.... 50 113.... -100 111.... -50 114.... -50 what i am getting is A.......B........C (getting) C (expecting) 111... 50... 2.......................1 112... 100... 0......................0 113... -50... 0.......................0 114... -100... 0......................0 111... -50... 2.......................1 115... 150... 0......................0 112... -50... 0.......................0 114... 50... 2.......................1 114... -50... 1.......................1 113... 100... 1......................1 111... 50... 2.......................1 113... -100.. 1......................1 111... -50... 2.......................1 114... -50... 1.......................0 any suggestion for alteration in the function? -via135 On Nov 13, 10:53 pm, "Roger Govier" wrote: Hi You cannot have entered the formula as I gave you. I get the following results 111 50 1 112 100 0 113 -50 0 114 -100 0 111 -50 1 115 150 0 112 -50 0 114 50 1 114 -50 1 113 100 0 -- Regards Roger Govier "via135 via OfficeKB.com" <u23552@uwe wrote in messagenews:693c38a347d8e@uwe... hi Roger! i am getting the following results: 111 50 1 112 100 1 113 -50 1 114 -100 1 111 -50 1 115 150 1 112 -50 1 114 50 2 114 -50 2 113 100 1 -via135 Roger Govier wrote: Hi One way In cell C2 enter the following and copy down =SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2)) There will be 1's against the matching items and 0's against the rest. DataFilterAutofilteruse dropdown on column C to select rows with 1 hi! [quoted text clipped - 13 lines] -via135 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200611/1 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
hi!
i think i've not explained it well! infact my intention is not to delete the duplicate records..! i just want to eliminate the matched pairs of +ve and -ve wrt the other column! for example if i am having 4 records as under 1112........100 1112........-100 1112........-100 1113........100 i don't want to eliminate the duplicate records 2nd & 3rd instead i want to offset 1st & 2nd (reconciled ones) and identify the 3rd and 4th as unreconciled..!! hope u understand..!! regds! -via135 On Nov 14, 2:47 pm, "Roger Govier" wrote: Hi The formula is working correctly.When you apply the Autofilter, use the dropdown on column C choose CustomGreater than0 This will then show your rows which are duplicated. Alternatively, you could wrap the formula in an IF statement =IF(SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2))0,"Duplicate","") Filter column C for Duplicate -- Regards Roger Govier "via135" wrote in glegroups.com... yes..you are absolutely right! i have given "B2" i / o "-B2"..! one more question..the formula gives the right answer only when the each ref no in col A is having some unique value col B..! when the same value is repeated in col B for the same ref no in col A there seems to be some glitch! for example if i extend the data to A2:B15 as under.. A........B 111.... 50 112.... 100 113.... -50 114.... -100 111.... -50 115.... 150 112.... -50 114.... 50 114.... -50 113.... 100 111.... 50 113.... -100 111.... -50 114.... -50 what i am getting is A.......B........C (getting) C (expecting) 111... 50... 2.......................1 112... 100... 0......................0 113... -50... 0.......................0 114... -100... 0......................0 111... -50... 2.......................1 115... 150... 0......................0 112... -50... 0.......................0 114... 50... 2.......................1 114... -50... 1.......................1 113... 100... 1......................1 111... 50... 2.......................1 113... -100.. 1......................1 111... -50... 2.......................1 114... -50... 1.......................0 any suggestion for alteration in the function? -via135 On Nov 13, 10:53 pm, "Roger Govier" wrote: Hi You cannot have entered the formula as I gave you. I get the following results 111 50 1 112 100 0 113 -50 0 114 -100 0 111 -50 1 115 150 0 112 -50 0 114 50 1 114 -50 1 113 100 0 -- Regards Roger Govier "via135 via OfficeKB.com" <u23552@uwe wrote in messagenews:693c38a347d8e@uwe... hi Roger! i am getting the following results: 111 50 1 112 100 1 113 -50 1 114 -100 1 111 -50 1 115 150 1 112 -50 1 114 50 2 114 -50 2 113 100 1 -via135 Roger Govier wrote: Hi One way In cell C2 enter the following and copy down =SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2)) There will be 1's against the matching items and 0's against the rest. DataFilterAutofilteruse dropdown on column C to select rows with 1 hi! [quoted text clipped - 13 lines] -via135 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200611/1 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
Hi
I think I understand. You just want to identify those lines where the number in column A is the same, and, the sum of the numbers adjacent to them in column B equals zero. If so then in column C enter =A2*ABS(B2) In D2 enter =IF(ISEVEN(COUNTIF($D$2:$D$15,D2)),1,"") Filter on column D for values of 1 -- Regards Roger Govier "via135" wrote in message oups.com... hi! i think i've not explained it well! infact my intention is not to delete the duplicate records..! i just want to eliminate the matched pairs of +ve and -ve wrt the other column! for example if i am having 4 records as under 1112........100 1112........-100 1112........-100 1113........100 i don't want to eliminate the duplicate records 2nd & 3rd instead i want to offset 1st & 2nd (reconciled ones) and identify the 3rd and 4th as unreconciled..!! hope u understand..!! regds! -via135 On Nov 14, 2:47 pm, "Roger Govier" wrote: Hi The formula is working correctly.When you apply the Autofilter, use the dropdown on column C choose CustomGreater than0 This will then show your rows which are duplicated. Alternatively, you could wrap the formula in an IF statement =IF(SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2))0,"Duplicate","") Filter column C for Duplicate -- Regards Roger Govier "via135" wrote in glegroups.com... yes..you are absolutely right! i have given "B2" i / o "-B2"..! one more question..the formula gives the right answer only when the each ref no in col A is having some unique value col B..! when the same value is repeated in col B for the same ref no in col A there seems to be some glitch! for example if i extend the data to A2:B15 as under.. A........B 111.... 50 112.... 100 113.... -50 114.... -100 111.... -50 115.... 150 112.... -50 114.... 50 114.... -50 113.... 100 111.... 50 113.... -100 111.... -50 114.... -50 what i am getting is A.......B........C (getting) C (expecting) 111... 50... 2.......................1 112... 100... 0......................0 113... -50... 0.......................0 114... -100... 0......................0 111... -50... 2.......................1 115... 150... 0......................0 112... -50... 0.......................0 114... 50... 2.......................1 114... -50... 1.......................1 113... 100... 1......................1 111... 50... 2.......................1 113... -100.. 1......................1 111... -50... 2.......................1 114... -50... 1.......................0 any suggestion for alteration in the function? -via135 On Nov 13, 10:53 pm, "Roger Govier" wrote: Hi You cannot have entered the formula as I gave you. I get the following results 111 50 1 112 100 0 113 -50 0 114 -100 0 111 -50 1 115 150 0 112 -50 0 114 50 1 114 -50 1 113 100 0 -- Regards Roger Govier "via135 via OfficeKB.com" <u23552@uwe wrote in messagenews:693c38a347d8e@uwe... hi Roger! i am getting the following results: 111 50 1 112 100 1 113 -50 1 114 -100 1 111 -50 1 115 150 1 112 -50 1 114 50 2 114 -50 2 113 100 1 -via135 Roger Govier wrote: Hi One way In cell C2 enter the following and copy down =SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2)) There will be 1's against the matching items and 0's against the rest. DataFilterAutofilteruse dropdown on column C to select rows with 1 hi! [quoted text clipped - 13 lines] -via135 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200611/1 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
that's it!
this is what i exactly want! thks roger.. thks a lot..! -via135 Roger Govier wrote: Hi I think I understand. You just want to identify those lines where the number in column A is the same, and, the sum of the numbers adjacent to them in column B equals zero. If so then in column C enter =A2*ABS(B2) In D2 enter =IF(ISEVEN(COUNTIF($D$2:$D$15,D2)),1,"") Filter on column D for values of 1 -- Regards Roger Govier "via135" wrote in message oups.com... hi! i think i've not explained it well! infact my intention is not to delete the duplicate records..! i just want to eliminate the matched pairs of +ve and -ve wrt the other column! for example if i am having 4 records as under 1112........100 1112........-100 1112........-100 1113........100 i don't want to eliminate the duplicate records 2nd & 3rd instead i want to offset 1st & 2nd (reconciled ones) and identify the 3rd and 4th as unreconciled..!! hope u understand..!! regds! -via135 On Nov 14, 2:47 pm, "Roger Govier" wrote: Hi The formula is working correctly.When you apply the Autofilter, use the dropdown on column C choose CustomGreater than0 This will then show your rows which are duplicated. Alternatively, you could wrap the formula in an IF statement =IF(SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2))0,"Duplicate","") Filter column C for Duplicate -- Regards Roger Govier "via135" wrote in glegroups.com... yes..you are absolutely right! i have given "B2" i / o "-B2"..! one more question..the formula gives the right answer only when the each ref no in col A is having some unique value col B..! when the same value is repeated in col B for the same ref no in col A there seems to be some glitch! for example if i extend the data to A2:B15 as under.. A........B 111.... 50 112.... 100 113.... -50 114.... -100 111.... -50 115.... 150 112.... -50 114.... 50 114.... -50 113.... 100 111.... 50 113.... -100 111.... -50 114.... -50 what i am getting is A.......B........C (getting) C (expecting) 111... 50... 2.......................1 112... 100... 0......................0 113... -50... 0.......................0 114... -100... 0......................0 111... -50... 2.......................1 115... 150... 0......................0 112... -50... 0.......................0 114... 50... 2.......................1 114... -50... 1.......................1 113... 100... 1......................1 111... 50... 2.......................1 113... -100.. 1......................1 111... -50... 2.......................1 114... -50... 1.......................0 any suggestion for alteration in the function? -via135 On Nov 13, 10:53 pm, "Roger Govier" wrote: Hi You cannot have entered the formula as I gave you. I get the following results 111 50 1 112 100 0 113 -50 0 114 -100 0 111 -50 1 115 150 0 112 -50 0 114 50 1 114 -50 1 113 100 0 -- Regards Roger Govier "via135 via OfficeKB.com" <u23552@uwe wrote in messagenews:693c38a347d8e@uwe... hi Roger! i am getting the following results: 111 50 1 112 100 1 113 -50 1 114 -100 1 111 -50 1 115 150 1 112 -50 1 114 50 2 114 -50 2 113 100 1 -via135 Roger Govier wrote: Hi One way In cell C2 enter the following and copy down =SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2)) There will be 1's against the matching items and 0's against the rest. DataFilterAutofilteruse dropdown on column C to select rows with 1 hi! [quoted text clipped - 13 lines] -via135 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200611/1 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
Glad we got there in the end!!!
Thanks for the feedback. -- Regards Roger Govier "via135" wrote in message ups.com... that's it! this is what i exactly want! thks roger.. thks a lot..! -via135 Roger Govier wrote: Hi I think I understand. You just want to identify those lines where the number in column A is the same, and, the sum of the numbers adjacent to them in column B equals zero. If so then in column C enter =A2*ABS(B2) In D2 enter =IF(ISEVEN(COUNTIF($D$2:$D$15,D2)),1,"") Filter on column D for values of 1 -- Regards Roger Govier "via135" wrote in message oups.com... hi! i think i've not explained it well! infact my intention is not to delete the duplicate records..! i just want to eliminate the matched pairs of +ve and -ve wrt the other column! for example if i am having 4 records as under 1112........100 1112........-100 1112........-100 1113........100 i don't want to eliminate the duplicate records 2nd & 3rd instead i want to offset 1st & 2nd (reconciled ones) and identify the 3rd and 4th as unreconciled..!! hope u understand..!! regds! -via135 On Nov 14, 2:47 pm, "Roger Govier" wrote: Hi The formula is working correctly.When you apply the Autofilter, use the dropdown on column C choose CustomGreater than0 This will then show your rows which are duplicated. Alternatively, you could wrap the formula in an IF statement =IF(SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2))0,"Duplicate","") Filter column C for Duplicate -- Regards Roger Govier "via135" wrote in glegroups.com... yes..you are absolutely right! i have given "B2" i / o "-B2"..! one more question..the formula gives the right answer only when the each ref no in col A is having some unique value col B..! when the same value is repeated in col B for the same ref no in col A there seems to be some glitch! for example if i extend the data to A2:B15 as under.. A........B 111.... 50 112.... 100 113.... -50 114.... -100 111.... -50 115.... 150 112.... -50 114.... 50 114.... -50 113.... 100 111.... 50 113.... -100 111.... -50 114.... -50 what i am getting is A.......B........C (getting) C (expecting) 111... 50... 2.......................1 112... 100... 0......................0 113... -50... 0.......................0 114... -100... 0......................0 111... -50... 2.......................1 115... 150... 0......................0 112... -50... 0.......................0 114... 50... 2.......................1 114... -50... 1.......................1 113... 100... 1......................1 111... 50... 2.......................1 113... -100.. 1......................1 111... -50... 2.......................1 114... -50... 1.......................0 any suggestion for alteration in the function? -via135 On Nov 13, 10:53 pm, "Roger Govier" wrote: Hi You cannot have entered the formula as I gave you. I get the following results 111 50 1 112 100 0 113 -50 0 114 -100 0 111 -50 1 115 150 0 112 -50 0 114 50 1 114 -50 1 113 100 0 -- Regards Roger Govier "via135 via OfficeKB.com" <u23552@uwe wrote in messagenews:693c38a347d8e@uwe... hi Roger! i am getting the following results: 111 50 1 112 100 1 113 -50 1 114 -100 1 111 -50 1 115 150 1 112 -50 1 114 50 2 114 -50 2 113 100 1 -via135 Roger Govier wrote: Hi One way In cell C2 enter the following and copy down =SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2)) There will be 1's against the matching items and 0's against the rest. DataFilterAutofilteruse dropdown on column C to select rows with 1 hi! [quoted text clipped - 13 lines] -via135 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200611/1 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
hi!
sorry for disturbing again..! one more follow up question..pl? this formula seems to be ok for number values. if iam having text as ref in the place of number ref in col-A, is there any method to arrive at the result? -via135 Roger Govier wrote: Glad we got there in the end!!! Thanks for the feedback. -- Regards Roger Govier "via135" wrote in message ups.com... that's it! this is what i exactly want! thks roger.. thks a lot..! -via135 Roger Govier wrote: Hi I think I understand. You just want to identify those lines where the number in column A is the same, and, the sum of the numbers adjacent to them in column B equals zero. If so then in column C enter =A2*ABS(B2) In D2 enter =IF(ISEVEN(COUNTIF($D$2:$D$15,D2)),1,"") Filter on column D for values of 1 -- Regards Roger Govier "via135" wrote in message oups.com... hi! i think i've not explained it well! infact my intention is not to delete the duplicate records..! i just want to eliminate the matched pairs of +ve and -ve wrt the other column! for example if i am having 4 records as under 1112........100 1112........-100 1112........-100 1113........100 i don't want to eliminate the duplicate records 2nd & 3rd instead i want to offset 1st & 2nd (reconciled ones) and identify the 3rd and 4th as unreconciled..!! hope u understand..!! regds! -via135 On Nov 14, 2:47 pm, "Roger Govier" wrote: Hi The formula is working correctly.When you apply the Autofilter, use the dropdown on column C choose CustomGreater than0 This will then show your rows which are duplicated. Alternatively, you could wrap the formula in an IF statement =IF(SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2))0,"Duplicate","") Filter column C for Duplicate -- Regards Roger Govier "via135" wrote in glegroups.com... yes..you are absolutely right! i have given "B2" i / o "-B2"..! one more question..the formula gives the right answer only when the each ref no in col A is having some unique value col B..! when the same value is repeated in col B for the same ref no in col A there seems to be some glitch! for example if i extend the data to A2:B15 as under.. A........B 111.... 50 112.... 100 113.... -50 114.... -100 111.... -50 115.... 150 112.... -50 114.... 50 114.... -50 113.... 100 111.... 50 113.... -100 111.... -50 114.... -50 what i am getting is A.......B........C (getting) C (expecting) 111... 50... 2.......................1 112... 100... 0......................0 113... -50... 0.......................0 114... -100... 0......................0 111... -50... 2.......................1 115... 150... 0......................0 112... -50... 0.......................0 114... 50... 2.......................1 114... -50... 1.......................1 113... 100... 1......................1 111... 50... 2.......................1 113... -100.. 1......................1 111... -50... 2.......................1 114... -50... 1.......................0 any suggestion for alteration in the function? -via135 On Nov 13, 10:53 pm, "Roger Govier" wrote: Hi You cannot have entered the formula as I gave you. I get the following results 111 50 1 112 100 0 113 -50 0 114 -100 0 111 -50 1 115 150 0 112 -50 0 114 50 1 114 -50 1 113 100 0 -- Regards Roger Govier "via135 via OfficeKB.com" <u23552@uwe wrote in messagenews:693c38a347d8e@uwe... hi Roger! i am getting the following results: 111 50 1 112 100 1 113 -50 1 114 -100 1 111 -50 1 115 150 1 112 -50 1 114 50 2 114 -50 2 113 100 1 -via135 Roger Govier wrote: Hi One way In cell C2 enter the following and copy down =SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2)) There will be 1's against the matching items and 0's against the rest. DataFilterAutofilteruse dropdown on column C to select rows with 1 hi! [quoted text clipped - 13 lines] -via135 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200611/1 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
Hi
Just change the formula in C2 to =A2&ABS(B2) then, you had already worked out my error for column D, it should have read =IF(ISEVEN(COUNTIF($C$2:$C$15,C2)),1,"") -- Regards Roger Govier "via135" wrote in message ups.com... hi! sorry for disturbing again..! one more follow up question..pl? this formula seems to be ok for number values. if iam having text as ref in the place of number ref in col-A, is there any method to arrive at the result? -via135 Roger Govier wrote: Glad we got there in the end!!! Thanks for the feedback. -- Regards Roger Govier "via135" wrote in message ups.com... that's it! this is what i exactly want! thks roger.. thks a lot..! -via135 Roger Govier wrote: Hi I think I understand. You just want to identify those lines where the number in column A is the same, and, the sum of the numbers adjacent to them in column B equals zero. If so then in column C enter =A2*ABS(B2) In D2 enter =IF(ISEVEN(COUNTIF($D$2:$D$15,D2)),1,"") Filter on column D for values of 1 -- Regards Roger Govier "via135" wrote in message oups.com... hi! i think i've not explained it well! infact my intention is not to delete the duplicate records..! i just want to eliminate the matched pairs of +ve and -ve wrt the other column! for example if i am having 4 records as under 1112........100 1112........-100 1112........-100 1113........100 i don't want to eliminate the duplicate records 2nd & 3rd instead i want to offset 1st & 2nd (reconciled ones) and identify the 3rd and 4th as unreconciled..!! hope u understand..!! regds! -via135 On Nov 14, 2:47 pm, "Roger Govier" wrote: Hi The formula is working correctly.When you apply the Autofilter, use the dropdown on column C choose CustomGreater than0 This will then show your rows which are duplicated. Alternatively, you could wrap the formula in an IF statement =IF(SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2))0,"Duplicate","") Filter column C for Duplicate -- Regards Roger Govier "via135" wrote in glegroups.com... yes..you are absolutely right! i have given "B2" i / o "-B2"..! one more question..the formula gives the right answer only when the each ref no in col A is having some unique value col B..! when the same value is repeated in col B for the same ref no in col A there seems to be some glitch! for example if i extend the data to A2:B15 as under.. A........B 111.... 50 112.... 100 113.... -50 114.... -100 111.... -50 115.... 150 112.... -50 114.... 50 114.... -50 113.... 100 111.... 50 113.... -100 111.... -50 114.... -50 what i am getting is A.......B........C (getting) C (expecting) 111... 50... 2.......................1 112... 100... 0......................0 113... -50... 0.......................0 114... -100... 0......................0 111... -50... 2.......................1 115... 150... 0......................0 112... -50... 0.......................0 114... 50... 2.......................1 114... -50... 1.......................1 113... 100... 1......................1 111... 50... 2.......................1 113... -100.. 1......................1 111... -50... 2.......................1 114... -50... 1.......................0 any suggestion for alteration in the function? -via135 On Nov 13, 10:53 pm, "Roger Govier" wrote: Hi You cannot have entered the formula as I gave you. I get the following results 111 50 1 112 100 0 113 -50 0 114 -100 0 111 -50 1 115 150 0 112 -50 0 114 50 1 114 -50 1 113 100 0 -- Regards Roger Govier "via135 via OfficeKB.com" <u23552@uwe wrote in messagenews:693c38a347d8e@uwe... hi Roger! i am getting the following results: 111 50 1 112 100 1 113 -50 1 114 -100 1 111 -50 1 115 150 1 112 -50 1 114 50 2 114 -50 2 113 100 1 -via135 Roger Govier wrote: Hi One way In cell C2 enter the following and copy down =SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2)) There will be 1's against the matching items and 0's against the rest. DataFilterAutofilteruse dropdown on column C to select rows with 1 hi! [quoted text clipped - 13 lines] -via135 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200611/1 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
hi!
thks for responding again promptly! as you rightly predict i am not that much of goose! btw i am afraid that A2&ABS(B2) in col-B doesn't seems to work for the text in col-A as like A2*ABS(B2) does the trick for the number..! ex: "A" "B" "C" "D" abcd... 100.... abcd100...blank abcd... -100... abcd100...blank abcd... -100... abcd100...blank efgh....100.... efgh100....blank actually i should get abcd... -100...blank efgh....100....blank -via135 Roger Govier wrote: Hi Just change the formula in C2 to =A2&ABS(B2) then, you had already worked out my error for column D, it should have read =IF(ISEVEN(COUNTIF($C$2:$C$15,C2)),1,"") -- Regards Roger Govier "via135" wrote in message ups.com... hi! sorry for disturbing again..! one more follow up question..pl? this formula seems to be ok for number values. if iam having text as ref in the place of number ref in col-A, is there any method to arrive at the result? -via135 Roger Govier wrote: Glad we got there in the end!!! Thanks for the feedback. -- Regards Roger Govier "via135" wrote in message ups.com... that's it! this is what i exactly want! thks roger.. thks a lot..! -via135 Roger Govier wrote: Hi I think I understand. You just want to identify those lines where the number in column A is the same, and, the sum of the numbers adjacent to them in column B equals zero. If so then in column C enter =A2*ABS(B2) In D2 enter =IF(ISEVEN(COUNTIF($D$2:$D$15,D2)),1,"") Filter on column D for values of 1 -- Regards Roger Govier "via135" wrote in message oups.com... hi! i think i've not explained it well! infact my intention is not to delete the duplicate records..! i just want to eliminate the matched pairs of +ve and -ve wrt the other column! for example if i am having 4 records as under 1112........100 1112........-100 1112........-100 1113........100 i don't want to eliminate the duplicate records 2nd & 3rd instead i want to offset 1st & 2nd (reconciled ones) and identify the 3rd and 4th as unreconciled..!! hope u understand..!! regds! -via135 On Nov 14, 2:47 pm, "Roger Govier" wrote: Hi The formula is working correctly.When you apply the Autofilter, use the dropdown on column C choose CustomGreater than0 This will then show your rows which are duplicated. Alternatively, you could wrap the formula in an IF statement =IF(SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2))0,"Duplicate","") Filter column C for Duplicate -- Regards Roger Govier "via135" wrote in glegroups.com... yes..you are absolutely right! i have given "B2" i / o "-B2"..! one more question..the formula gives the right answer only when the each ref no in col A is having some unique value col B..! when the same value is repeated in col B for the same ref no in col A there seems to be some glitch! for example if i extend the data to A2:B15 as under.. A........B 111.... 50 112.... 100 113.... -50 114.... -100 111.... -50 115.... 150 112.... -50 114.... 50 114.... -50 113.... 100 111.... 50 113.... -100 111.... -50 114.... -50 what i am getting is A.......B........C (getting) C (expecting) 111... 50... 2.......................1 112... 100... 0......................0 113... -50... 0.......................0 114... -100... 0......................0 111... -50... 2.......................1 115... 150... 0......................0 112... -50... 0.......................0 114... 50... 2.......................1 114... -50... 1.......................1 113... 100... 1......................1 111... 50... 2.......................1 113... -100.. 1......................1 111... -50... 2.......................1 114... -50... 1.......................0 any suggestion for alteration in the function? -via135 On Nov 13, 10:53 pm, "Roger Govier" wrote: Hi You cannot have entered the formula as I gave you. I get the following results 111 50 1 112 100 0 113 -50 0 114 -100 0 111 -50 1 115 150 0 112 -50 0 114 50 1 114 -50 1 113 100 0 -- Regards Roger Govier "via135 via OfficeKB.com" <u23552@uwe wrote in messagenews:693c38a347d8e@uwe... hi Roger! i am getting the following results: 111 50 1 112 100 1 113 -50 1 114 -100 1 111 -50 1 115 150 1 112 -50 1 114 50 2 114 -50 2 113 100 1 -via135 Roger Govier wrote: Hi One way In cell C2 enter the following and copy down =SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2)) There will be 1's against the matching items and 0's against the rest. DataFilterAutofilteruse dropdown on column C to select rows with 1 hi! [quoted text clipped - 13 lines] -via135 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200611/1 |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
Hi
No, the result is correct. It will only put a value of 1 against entries where there are matching pairs of values. The abcd entries have 3 and the efgh has 1. All results are ODD therefore no 1 is inserted. If another entry of abcd and 100 were made, then the count would be 4, and Iseven would be satisfied so all 4 lines would have 1 against them. Having just said that, I realise that entering another line of abcd and -100 would also give that result, which would be incorrect as you need the sum of column B to be zero for matching values in column A. We don't need the concatenation column at all. Just use =IF(AND(ISEVEN(COUNTIF($A$1:$A$15,A1)), SUMPRODUCT(($A$1:$A$15=A1)*($B$1:$B$15))=0),1,"") This works for alpha or numeric in column A -- Regards Roger Govier "via135" wrote in message ups.com... hi! thks for responding again promptly! as you rightly predict i am not that much of goose! btw i am afraid that A2&ABS(B2) in col-B doesn't seems to work for the text in col-A as like A2*ABS(B2) does the trick for the number..! ex: "A" "B" "C" "D" abcd... 100.... abcd100...blank abcd... -100... abcd100...blank abcd... -100... abcd100...blank efgh....100.... efgh100....blank actually i should get abcd... -100...blank efgh....100....blank -via135 Roger Govier wrote: Hi Just change the formula in C2 to =A2&ABS(B2) then, you had already worked out my error for column D, it should have read =IF(ISEVEN(COUNTIF($C$2:$C$15,C2)),1,"") -- Regards Roger Govier "via135" wrote in message ups.com... hi! sorry for disturbing again..! one more follow up question..pl? this formula seems to be ok for number values. if iam having text as ref in the place of number ref in col-A, is there any method to arrive at the result? -via135 Roger Govier wrote: Glad we got there in the end!!! Thanks for the feedback. -- Regards Roger Govier "via135" wrote in message ups.com... that's it! this is what i exactly want! thks roger.. thks a lot..! -via135 Roger Govier wrote: Hi I think I understand. You just want to identify those lines where the number in column A is the same, and, the sum of the numbers adjacent to them in column B equals zero. If so then in column C enter =A2*ABS(B2) In D2 enter =IF(ISEVEN(COUNTIF($D$2:$D$15,D2)),1,"") Filter on column D for values of 1 -- Regards Roger Govier "via135" wrote in message oups.com... hi! i think i've not explained it well! infact my intention is not to delete the duplicate records..! i just want to eliminate the matched pairs of +ve and -ve wrt the other column! for example if i am having 4 records as under 1112........100 1112........-100 1112........-100 1113........100 i don't want to eliminate the duplicate records 2nd & 3rd instead i want to offset 1st & 2nd (reconciled ones) and identify the 3rd and 4th as unreconciled..!! hope u understand..!! regds! -via135 On Nov 14, 2:47 pm, "Roger Govier" wrote: Hi The formula is working correctly.When you apply the Autofilter, use the dropdown on column C choose CustomGreater than0 This will then show your rows which are duplicated. Alternatively, you could wrap the formula in an IF statement =IF(SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2))0,"Duplicate","") Filter column C for Duplicate -- Regards Roger Govier "via135" wrote in glegroups.com... yes..you are absolutely right! i have given "B2" i / o "-B2"..! one more question..the formula gives the right answer only when the each ref no in col A is having some unique value col B..! when the same value is repeated in col B for the same ref no in col A there seems to be some glitch! for example if i extend the data to A2:B15 as under.. A........B 111.... 50 112.... 100 113.... -50 114.... -100 111.... -50 115.... 150 112.... -50 114.... 50 114.... -50 113.... 100 111.... 50 113.... -100 111.... -50 114.... -50 what i am getting is A.......B........C (getting) C (expecting) 111... 50... 2.......................1 112... 100... 0......................0 113... -50... 0.......................0 114... -100... 0......................0 111... -50... 2.......................1 115... 150... 0......................0 112... -50... 0.......................0 114... 50... 2.......................1 114... -50... 1.......................1 113... 100... 1......................1 111... 50... 2.......................1 113... -100.. 1......................1 111... -50... 2.......................1 114... -50... 1.......................0 any suggestion for alteration in the function? -via135 On Nov 13, 10:53 pm, "Roger Govier" wrote: Hi You cannot have entered the formula as I gave you. I get the following results 111 50 1 112 100 0 113 -50 0 114 -100 0 111 -50 1 115 150 0 112 -50 0 114 50 1 114 -50 1 113 100 0 -- Regards Roger Govier "via135 via OfficeKB.com" <u23552@uwe wrote in messagenews:693c38a347d8e@uwe... hi Roger! i am getting the following results: 111 50 1 112 100 1 113 -50 1 114 -100 1 111 -50 1 115 150 1 112 -50 1 114 50 2 114 -50 2 113 100 1 -via135 Roger Govier wrote: Hi One way In cell C2 enter the following and copy down =SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2)) There will be 1's against the matching items and 0's against the rest. DataFilterAutofilteruse dropdown on column C to select rows with 1 hi! [quoted text clipped - 13 lines] -via135 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200611/1 |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
hi!
i am still getting blanks in the flag Col-C..! instead getting 1s only for first two records the matched pair in the data! the last two abcd...-100 efgh....100 are not matched pairs! -via135 Roger Govier wrote: Hi No, the result is correct. It will only put a value of 1 against entries where there are matching pairs of values. The abcd entries have 3 and the efgh has 1. All results are ODD therefore no 1 is inserted. If another entry of abcd and 100 were made, then the count would be 4, and Iseven would be satisfied so all 4 lines would have 1 against them. Having just said that, I realise that entering another line of abcd and -100 would also give that result, which would be incorrect as you need the sum of column B to be zero for matching values in column A. We don't need the concatenation column at all. Just use =IF(AND(ISEVEN(COUNTIF($A$1:$A$15,A1)), SUMPRODUCT(($A$1:$A$15=A1)*($B$1:$B$15))=0),1,"") This works for alpha or numeric in column A -- Regards Roger Govier "via135" wrote in message ups.com... hi! thks for responding again promptly! as you rightly predict i am not that much of goose! btw i am afraid that A2&ABS(B2) in col-B doesn't seems to work for the text in col-A as like A2*ABS(B2) does the trick for the number..! ex: "A" "B" "C" "D" abcd... 100.... abcd100...blank abcd... -100... abcd100...blank abcd... -100... abcd100...blank efgh....100.... efgh100....blank actually i should get abcd... -100...blank efgh....100....blank -via135 Roger Govier wrote: Hi Just change the formula in C2 to =A2&ABS(B2) then, you had already worked out my error for column D, it should have read =IF(ISEVEN(COUNTIF($C$2:$C$15,C2)),1,"") -- Regards Roger Govier "via135" wrote in message ups.com... hi! sorry for disturbing again..! one more follow up question..pl? this formula seems to be ok for number values. if iam having text as ref in the place of number ref in col-A, is there any method to arrive at the result? -via135 Roger Govier wrote: Glad we got there in the end!!! Thanks for the feedback. -- Regards Roger Govier "via135" wrote in message ups.com... that's it! this is what i exactly want! thks roger.. thks a lot..! -via135 Roger Govier wrote: Hi I think I understand. You just want to identify those lines where the number in column A is the same, and, the sum of the numbers adjacent to them in column B equals zero. If so then in column C enter =A2*ABS(B2) In D2 enter =IF(ISEVEN(COUNTIF($D$2:$D$15,D2)),1,"") Filter on column D for values of 1 -- Regards Roger Govier "via135" wrote in message oups.com... hi! i think i've not explained it well! infact my intention is not to delete the duplicate records..! i just want to eliminate the matched pairs of +ve and -ve wrt the other column! for example if i am having 4 records as under 1112........100 1112........-100 1112........-100 1113........100 i don't want to eliminate the duplicate records 2nd & 3rd instead i want to offset 1st & 2nd (reconciled ones) and identify the 3rd and 4th as unreconciled..!! hope u understand..!! regds! -via135 On Nov 14, 2:47 pm, "Roger Govier" wrote: Hi The formula is working correctly.When you apply the Autofilter, use the dropdown on column C choose CustomGreater than0 This will then show your rows which are duplicated. Alternatively, you could wrap the formula in an IF statement =IF(SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2))0,"Duplicate","") Filter column C for Duplicate -- Regards Roger Govier "via135" wrote in glegroups.com... yes..you are absolutely right! i have given "B2" i / o "-B2"..! one more question..the formula gives the right answer only when the each ref no in col A is having some unique value col B..! when the same value is repeated in col B for the same ref no in col A there seems to be some glitch! for example if i extend the data to A2:B15 as under.. A........B 111.... 50 112.... 100 113.... -50 114.... -100 111.... -50 115.... 150 112.... -50 114.... 50 114.... -50 113.... 100 111.... 50 113.... -100 111.... -50 114.... -50 what i am getting is A.......B........C (getting) C (expecting) 111... 50... 2.......................1 112... 100... 0......................0 113... -50... 0.......................0 114... -100... 0......................0 111... -50... 2.......................1 115... 150... 0......................0 112... -50... 0.......................0 114... 50... 2.......................1 114... -50... 1.......................1 113... 100... 1......................1 111... 50... 2.......................1 113... -100.. 1......................1 111... -50... 2.......................1 114... -50... 1.......................0 any suggestion for alteration in the function? -via135 On Nov 13, 10:53 pm, "Roger Govier" wrote: Hi You cannot have entered the formula as I gave you. I get the following results 111 50 1 112 100 0 113 -50 0 114 -100 0 111 -50 1 115 150 0 112 -50 0 114 50 1 114 -50 1 113 100 0 -- Regards Roger Govier "via135 via OfficeKB.com" <u23552@uwe wrote in messagenews:693c38a347d8e@uwe... hi Roger! i am getting the following results: 111 50 1 112 100 1 113 -50 1 114 -100 1 111 -50 1 115 150 1 112 -50 1 114 50 2 114 -50 2 113 100 1 -via135 Roger Govier wrote: Hi One way In cell C2 enter the following and copy down =SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2)) There will be 1's against the matching items and 0's against the rest. DataFilterAutofilteruse dropdown on column C to select rows with 1 hi! [quoted text clipped - 13 lines] -via135 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200611/1 |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
Yes you will get all blanks, because none of the collection is
satisfying the criteria. Whilst you can "see" that the first 2 entries of abcd do produce a matching pair, there are 3 entries with abcd in column A so it fails firstly because Countif for abcd is not even (3). It also fails because the Sum of values in B against lines having abcd in column A does not come to zero. We can get around this by making the formula carry out its tests as it goes down the column, as opposed to being on the whole column, but then you will only see the 1's appearing against the second of the 2 entries, i.e. the entry which creates the match, hence if you were to try to use Autofilter to view only the matching entries, it would only pick up one half of each matched pair. Perhaps you should use 2 columns, one with the formula as provided already, and one with the modified formula as below =IF(AND(ISEVEN(COUNTIF($A$1:$A1,A1)), SUMPRODUCT(($A$1:$A1=A1)*($B$1:$B1))=0),1,"") You will note that the range is now $A$1:$A1 so it will expand as it is copied down because of the relative second reference whereas the original formula uses absolutes of $A$1:$A$15 and use the whole range unaltered in each of the cells as you copy down. I hope this makes sense to you. -- Regards Roger Govier "via135" wrote in message ups.com... hi! i am still getting blanks in the flag Col-C..! instead getting 1s only for first two records the matched pair in the data! the last two abcd...-100 efgh....100 are not matched pairs! -via135 Roger Govier wrote: Hi No, the result is correct. It will only put a value of 1 against entries where there are matching pairs of values. The abcd entries have 3 and the efgh has 1. All results are ODD therefore no 1 is inserted. If another entry of abcd and 100 were made, then the count would be 4, and Iseven would be satisfied so all 4 lines would have 1 against them. Having just said that, I realise that entering another line of abcd and -100 would also give that result, which would be incorrect as you need the sum of column B to be zero for matching values in column A. We don't need the concatenation column at all. Just use =IF(AND(ISEVEN(COUNTIF($A$1:$A$15,A1)), SUMPRODUCT(($A$1:$A$15=A1)*($B$1:$B$15))=0),1,"") This works for alpha or numeric in column A -- Regards Roger Govier "via135" wrote in message ups.com... hi! thks for responding again promptly! as you rightly predict i am not that much of goose! btw i am afraid that A2&ABS(B2) in col-B doesn't seems to work for the text in col-A as like A2*ABS(B2) does the trick for the number..! ex: "A" "B" "C" "D" abcd... 100.... abcd100...blank abcd... -100... abcd100...blank abcd... -100... abcd100...blank efgh....100.... efgh100....blank actually i should get abcd... -100...blank efgh....100....blank -via135 Roger Govier wrote: Hi Just change the formula in C2 to =A2&ABS(B2) then, you had already worked out my error for column D, it should have read =IF(ISEVEN(COUNTIF($C$2:$C$15,C2)),1,"") -- Regards Roger Govier "via135" wrote in message ups.com... hi! sorry for disturbing again..! one more follow up question..pl? this formula seems to be ok for number values. if iam having text as ref in the place of number ref in col-A, is there any method to arrive at the result? -via135 Roger Govier wrote: Glad we got there in the end!!! Thanks for the feedback. -- Regards Roger Govier "via135" wrote in message ups.com... that's it! this is what i exactly want! thks roger.. thks a lot..! -via135 Roger Govier wrote: Hi I think I understand. You just want to identify those lines where the number in column A is the same, and, the sum of the numbers adjacent to them in column B equals zero. If so then in column C enter =A2*ABS(B2) In D2 enter =IF(ISEVEN(COUNTIF($D$2:$D$15,D2)),1,"") Filter on column D for values of 1 -- Regards Roger Govier "via135" wrote in message oups.com... hi! i think i've not explained it well! infact my intention is not to delete the duplicate records..! i just want to eliminate the matched pairs of +ve and -ve wrt the other column! for example if i am having 4 records as under 1112........100 1112........-100 1112........-100 1113........100 i don't want to eliminate the duplicate records 2nd & 3rd instead i want to offset 1st & 2nd (reconciled ones) and identify the 3rd and 4th as unreconciled..!! hope u understand..!! regds! -via135 On Nov 14, 2:47 pm, "Roger Govier" wrote: Hi The formula is working correctly.When you apply the Autofilter, use the dropdown on column C choose CustomGreater than0 This will then show your rows which are duplicated. Alternatively, you could wrap the formula in an IF statement =IF(SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2))0,"Duplicate","") Filter column C for Duplicate -- Regards Roger Govier "via135" wrote in glegroups.com... yes..you are absolutely right! i have given "B2" i / o "-B2"..! one more question..the formula gives the right answer only when the each ref no in col A is having some unique value col B..! when the same value is repeated in col B for the same ref no in col A there seems to be some glitch! for example if i extend the data to A2:B15 as under.. A........B 111.... 50 112.... 100 113.... -50 114.... -100 111.... -50 115.... 150 112.... -50 114.... 50 114.... -50 113.... 100 111.... 50 113.... -100 111.... -50 114.... -50 what i am getting is A.......B........C (getting) C (expecting) 111... 50... 2.......................1 112... 100... 0......................0 113... -50... 0.......................0 114... -100... 0......................0 111... -50... 2.......................1 115... 150... 0......................0 112... -50... 0.......................0 114... 50... 2.......................1 114... -50... 1.......................1 113... 100... 1......................1 111... 50... 2.......................1 113... -100.. 1......................1 111... -50... 2.......................1 114... -50... 1.......................0 any suggestion for alteration in the function? -via135 On Nov 13, 10:53 pm, "Roger Govier" wrote: Hi You cannot have entered the formula as I gave you. I get the following results 111 50 1 112 100 0 113 -50 0 114 -100 0 111 -50 1 115 150 0 112 -50 0 114 50 1 114 -50 1 113 100 0 -- Regards Roger Govier "via135 via OfficeKB.com" <u23552@uwe wrote in messagenews:693c38a347d8e@uwe... hi Roger! i am getting the following results: 111 50 1 112 100 1 113 -50 1 114 -100 1 111 -50 1 115 150 1 112 -50 1 114 50 2 114 -50 2 113 100 1 -via135 Roger Govier wrote: Hi One way In cell C2 enter the following and copy down =SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2)) There will be 1's against the matching items and 0's against the rest. DataFilterAutofilteruse dropdown on column C to select rows with 1 hi! [quoted text clipped - 13 lines] -via135 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200611/1 |
|
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
hi!
mmhhmmm! now i am getting -A-.......-B-...-C-............-D- abcd... 100..abcd100....blank abcd...-100..abcd100....1 abcd...-100..abcd100....blank efgh....100..efgh100......blank instead -A-......-B-..........-C-..........-D- abcd -100 abcd100....blank efgh 100 efgh100.....blank -via135 On Nov 17, 1:35 pm, "Roger Govier" wrote: Yes you will get all blanks, because none of the collection is satisfying the criteria. Whilst you can "see" that the first 2 entries of abcd do produce a matching pair, there are 3 entries with abcd in column A so it fails firstly because Countif for abcd is not even (3). It also fails because the Sum of values in B against lines having abcd in column A does not come to zero. We can get around this by making the formula carry out its tests as it goes down the column, as opposed to being on the whole column, but then you will only see the 1's appearing against the second of the 2 entries, i.e. the entry which creates the match, hence if you were to try to use Autofilter to view only the matching entries, it would only pick up one half of each matched pair. Perhaps you should use 2 columns, one with the formula as provided already, and one with the modified formula as below =IF(AND(ISEVEN(COUNTIF($A$1:$A1,A1)), SUMPRODUCT(($A$1:$A1=A1)*($B$1:$B1))=0),1,"") You will note that the range is now $A$1:$A1 so it will expand as it is copied down because of the relative second reference whereas the original formula uses absolutes of $A$1:$A$15 and use the whole range unaltered in each of the cells as you copy down. I hope this makes sense to you. -- Regards Roger Govier "via135" wrote in oglegroups.com... hi! i am still getting blanks in the flag Col-C..! instead getting 1s only for first two records the matched pair in the data! the last two abcd...-100 efgh....100 are not matched pairs! -via135 Roger Govier wrote: Hi No, the result is correct. It will only put a value of 1 against entries where there are matching pairs of values. The abcd entries have 3 and the efgh has 1. All results are ODD therefore no 1 is inserted. If another entry of abcd and 100 were made, then the count would be 4, and Iseven would be satisfied so all 4 lines would have 1 against them. Having just said that, I realise that entering another line of abcd and -100 would also give that result, which would be incorrect as you need the sum of column B to be zero for matching values in column A. We don't need the concatenation column at all. Just use =IF(AND(ISEVEN(COUNTIF($A$1:$A$15,A1)), SUMPRODUCT(($A$1:$A$15=A1)*($B$1:$B$15))=0),1,"") This works for alpha or numeric in column A -- Regards Roger Govier "via135" wrote in message roups.com... hi! thks for responding again promptly! as you rightly predict i am not that much of goose! btw i am afraid that A2&ABS(B2) in col-B doesn't seems to work for the text in col-A as like A2*ABS(B2) does the trick for the number..! ex: "A" "B" "C" "D" abcd... 100.... abcd100...blank abcd... -100... abcd100...blank abcd... -100... abcd100...blank efgh....100.... efgh100....blank actually i should get abcd... -100...blank efgh....100....blank -via135 Roger Govier wrote: Hi Just change the formula in C2 to =A2&ABS(B2) then, you had already worked out my error for column D, it should have read =IF(ISEVEN(COUNTIF($C$2:$C$15,C2)),1,"") -- Regards Roger Govier "via135" wrote in message roups.com... hi! sorry for disturbing again..! one more follow up question..pl? this formula seems to be ok for number values. if iam having text as ref in the place of number ref in col-A, is there any method to arrive at the result? -via135 Roger Govier wrote: Glad we got there in the end!!! Thanks for the feedback. -- Regards Roger Govier "via135" wrote in message roups.com... that's it! this is what i exactly want! thks roger.. thks a lot..! -via135 Roger Govier wrote: Hi I think I understand. You just want to identify those lines where the number in column A is the same, and, the sum of the numbers adjacent to them in column B equals zero. If so then in column C enter =A2*ABS(B2) In D2 enter =IF(ISEVEN(COUNTIF($D$2:$D$15,D2)),1,"") Filter on column D for values of 1 -- Regards Roger Govier "via135" wrote in message groups.com... hi! i think i've not explained it well! infact my intention is not to delete the duplicate records..! i just want to eliminate the matched pairs of +ve and -ve wrt the other column! for example if i am having 4 records as under 1112........100 1112........-100 1112........-100 1113........100 i don't want to eliminate the duplicate records 2nd & 3rd instead i want to offset 1st & 2nd (reconciled ones) and identify the 3rd and 4th as unreconciled..!! hope u understand..!! regds! -via135 On Nov 14, 2:47 pm, "Roger Govier" wrote: Hi The formula is working correctly.When you apply the Autofilter, use the dropdown on column C choose CustomGreater than0 This will then show your rows which are duplicated. Alternatively, you could wrap the formula in an IF statement =IF(SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2))0,"Duplicate","") Filter column C for Duplicate -- Regards Roger Govier "via135" wrote in glegroups.com... yes..you are absolutely right! i have given "B2" i / o "-B2"..! one more question..the formula gives the right answer only when the each ref no in col A is having some unique value col B..! when the same value is repeated in col B for the same ref no in col A there seems to be some glitch! for example if i extend the data to A2:B15 as under.. A........B 111.... 50 112.... 100 113.... -50 114.... -100 111.... -50 115.... 150 112.... -50 114.... 50 114.... -50 113.... 100 111.... 50 113.... -100 111.... -50 114.... -50 what i am getting is A.......B........C (getting) C (expecting) 111... 50... 2.......................1 112... 100... 0......................0 113... -50... 0.......................0 114... -100... 0......................0 111... -50... 2.......................1 115... 150... 0......................0 112... -50... 0.......................0 114... 50... 2.......................1 114... -50... 1.......................1 113... 100... 1......................1 111... 50... 2.......................1 113... -100.. 1......................1 111... -50... 2.......................1 114... -50... 1.......................0 any suggestion for alteration in the function? -via135 On Nov 13, 10:53 pm, "Roger Govier" wrote: Hi You cannot have entered the formula as I gave you. I get the following results 111 50 1 112 100 0 113 -50 0 114 -100 0 111 -50 1 115 150 0 112 -50 0 114 50 1 114 -50 1 113 100 0 -- Regards Roger Govier "via135 via OfficeKB.com" <u23552@uwe wrote in messagenews:693c38a347d8e@uwe... hi Roger!... read more |
|
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
hi!
help me out!!?? -via135 On Nov 17, 10:25 pm, "via135" wrote: hi! mmhhmmm! now i am getting -A-.......-B-...-C-............-D- abcd... 100..abcd100....blank abcd...-100..abcd100....1 abcd...-100..abcd100....blank efgh....100..efgh100......blank instead -A-......-B-..........-C-..........-D- abcd -100 abcd100....blank efgh 100 efgh100.....blank -via135 On Nov 17, 1:35 pm, "Roger Govier" wrote: Yes you will get all blanks, because none of the collection is satisfying the criteria. Whilst you can "see" that the first 2 entries of abcd do produce a matching pair, there are 3 entries with abcd in column A so it fails firstly because Countif for abcd is not even (3). It also fails because the Sum of values in B against lines having abcd in column A does not come to zero. We can get around this by making the formula carry out its tests as it goes down the column, as opposed to being on the whole column, but then you will only see the 1's appearing against the second of the 2 entries, i.e. the entry which creates the match, hence if you were to try to use Autofilter to view only the matching entries, it would only pick up one half of each matched pair. Perhaps you should use 2 columns, one with the formula as provided already, and one with the modified formula as below =IF(AND(ISEVEN(COUNTIF($A$1:$A1,A1)), SUMPRODUCT(($A$1:$A1=A1)*($B$1:$B1))=0),1,"") You will note that the range is now $A$1:$A1 so it will expand as it is copied down because of the relative second reference whereas the original formula uses absolutes of $A$1:$A$15 and use the whole range unaltered in each of the cells as you copy down. I hope this makes sense to you. -- Regards Roger Govier "via135" wrote in oglegroups.com... hi! i am still getting blanks in the flag Col-C..! instead getting 1s only for first two records the matched pair in the data! the last two abcd...-100 efgh....100 are not matched pairs! -via135 Roger Govier wrote: Hi No, the result is correct. It will only put a value of 1 against entries where there are matching pairs of values. The abcd entries have 3 and the efgh has 1. All results are ODD therefore no 1 is inserted. If another entry of abcd and 100 were made, then the count would be 4, and Iseven would be satisfied so all 4 lines would have 1 against them. Having just said that, I realise that entering another line of abcd and -100 would also give that result, which would be incorrect as you need the sum of column B to be zero for matching values in column A. We don't need the concatenation column at all. Just use =IF(AND(ISEVEN(COUNTIF($A$1:$A$15,A1)), SUMPRODUCT(($A$1:$A$15=A1)*($B$1:$B$15))=0),1,"") This works for alpha or numeric in column A -- Regards Roger Govier "via135" wrote in message roups.com... hi! thks for responding again promptly! as you rightly predict i am not that much of goose! btw i am afraid that A2&ABS(B2) in col-B doesn't seems to work for the text in col-A as like A2*ABS(B2) does the trick for the number..! ex: "A" "B" "C" "D" abcd... 100.... abcd100...blank abcd... -100... abcd100...blank abcd... -100... abcd100...blank efgh....100.... efgh100....blank actually i should get abcd... -100...blank efgh....100....blank -via135 Roger Govier wrote: Hi Just change the formula in C2 to =A2&ABS(B2) then, you had already worked out my error for column D, it should have read =IF(ISEVEN(COUNTIF($C$2:$C$15,C2)),1,"") -- Regards Roger Govier "via135" wrote in message roups.com... hi! sorry for disturbing again..! one more follow up question..pl? this formula seems to be ok for number values. if iam having text as ref in the place of number ref in col-A, is there any method to arrive at the result? -via135 Roger Govier wrote: Glad we got there in the end!!! Thanks for the feedback. -- Regards Roger Govier "via135" wrote in message roups.com... that's it! this is what i exactly want! thks roger.. thks a lot..! -via135 Roger Govier wrote: Hi I think I understand. You just want to identify those lines where the number in column A is the same, and, the sum of the numbers adjacent to them in column B equals zero. If so then in column C enter =A2*ABS(B2) In D2 enter =IF(ISEVEN(COUNTIF($D$2:$D$15,D2)),1,"") Filter on column D for values of 1 -- Regards Roger Govier "via135" wrote in message groups.com... hi! i think i've not explained it well! infact my intention is not to delete the duplicate records..! i just want to eliminate the matched pairs of +ve and -ve wrt the other column! for example if i am having 4 records as under 1112........100 1112........-100 1112........-100 1113........100 i don't want to eliminate the duplicate records 2nd & 3rd instead i want to offset 1st & 2nd (reconciled ones) and identify the 3rd and 4th as unreconciled..!! hope u understand..!! regds! -via135 On Nov 14, 2:47 pm, "Roger Govier" wrote: Hi The formula is working correctly.When you apply the Autofilter, use the dropdown on column C choose CustomGreater than0 This will then show your rows which are duplicated. Alternatively, you could wrap the formula in an IF statement =IF(SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2))0,"Duplicate","") Filter column C for Duplicate -- Regards Roger Govier "via135" wrote in glegroups.com... yes..you are absolutely right! i have given "B2" i / o "-B2"..! one more question..the formula gives the right answer only when the each ref no in col A is having some unique value col B..! when the same value is repeated in col B for the same ref no in col A there seems to be some glitch! for example if i extend the data to A2:B15 as under.. A........B 111.... 50 112.... 100 113.... -50 114.... -100 111.... -50 115.... 150 112.... -50 114.... 50 114.... -50 113.... 100 111.... 50 113.... -100 111.... -50 114.... -50 what i am getting is A.......B........C (getting) C (expecting) 111... 50... 2.......................1 112... 100... 0......................0 113... -50... 0.......................0 114... -100... 0......................0 111... -50... 2.......................1 115... 150... 0......................0 112... -50... 0.......................0 114... 50... 2.......................1 114... -50... 1.......................1 113... 100... 1......................1 111... 50... 2.......................1 113... -100.. 1......................1 111... -50... 2.......................1 114... -50... 1.......................0 any suggestion for alteration in the function? -via135 On Nov 13, 10:53 pm, "Roger Govier" wrote: Hi You cannot have entered the formula as I gave you. I get the following results 111 50 1 112 100 0 113 -50 0 114 -100 0 111 -50 1 115 150 0 112 -50 0... read more |
|
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
At the risk of this becoming the longest thread in history<g
Yes that is exactly what I said would be the result. You can have one solution, or the other which is why I suggested you have both formulae in adjacent columns and use whichever you want. Firstly, you can get rid of column C and the concatenation - we have now discarded that. If you want 1's to appear against "all" matching entries then the formula is =IF(AND(ISEVEN(COUNTIF($A$1:$A$15,A1)), SUMPRODUCT(($A$1:$A$15=A1)*($B$1:$B$15))=0),1,"") Place this in C1 With your 4 lines of data, all will remain blank because they do not satisfy the criteria. Because we are looking at ALL lines in the range abcd crops up 3 times, so there cannot be a match and efgh crops up only once, so there cannot be a match. The moment you enter another line with abcd in column A and 100 in column B, all 4 lines with abcd will show a 1 in column C because there are 2 matching pairs and the sum of their values in column B is 0. If you also put in column D the following formula =IF(AND(ISEVEN(COUNTIF($A$1:$A1,A1)), SUMPRODUCT(($A$1:$A1=A1)*($B$1:$B1))=0),1,"") then because we are only looking at lines up to and including the current line that we are on, it will put a 1 in column D when the 2 criteria are met, but it will only put it against the second of the matching pair of lines found within the range down to and including that line. Therefore you see a 1 on line 2, because line 1 and line 2 satisfy the criteria I think we need to start again from the beginning with real examples and description of exactly what you are wanting to achieve. In your first posting you said you wanted to eliminate matching records. If you do eliminate the matching records, or copy them to another sheet called Completed or Reconciled, then the first method as above will work fine. If you do not remove them, then as you add further entries of the same values in column A, obviously all of the previously marked lines will become unmarked, as there will be an odd number of entries, and the amounts in column B will not sum to 0. -- Regards Roger Govier "via135" wrote in message ups.com... hi! mmhhmmm! now i am getting -A-.......-B-...-C-............-D- abcd... 100..abcd100....blank abcd...-100..abcd100....1 abcd...-100..abcd100....blank efgh....100..efgh100......blank instead -A-......-B-..........-C-..........-D- abcd -100 abcd100....blank efgh 100 efgh100.....blank -via135 On Nov 17, 1:35 pm, "Roger Govier" wrote: Yes you will get all blanks, because none of the collection is satisfying the criteria. Whilst you can "see" that the first 2 entries of abcd do produce a matching pair, there are 3 entries with abcd in column A so it fails firstly because Countif for abcd is not even (3). It also fails because the Sum of values in B against lines having abcd in column A does not come to zero. We can get around this by making the formula carry out its tests as it goes down the column, as opposed to being on the whole column, but then you will only see the 1's appearing against the second of the 2 entries, i.e. the entry which creates the match, hence if you were to try to use Autofilter to view only the matching entries, it would only pick up one half of each matched pair. Perhaps you should use 2 columns, one with the formula as provided already, and one with the modified formula as below =IF(AND(ISEVEN(COUNTIF($A$1:$A1,A1)), SUMPRODUCT(($A$1:$A1=A1)*($B$1:$B1))=0),1,"") You will note that the range is now $A$1:$A1 so it will expand as it is copied down because of the relative second reference whereas the original formula uses absolutes of $A$1:$A$15 and use the whole range unaltered in each of the cells as you copy down. I hope this makes sense to you. -- Regards Roger Govier "via135" wrote in oglegroups.com... hi! i am still getting blanks in the flag Col-C..! instead getting 1s only for first two records the matched pair in the data! the last two abcd...-100 efgh....100 are not matched pairs! -via135 Roger Govier wrote: Hi No, the result is correct. It will only put a value of 1 against entries where there are matching pairs of values. The abcd entries have 3 and the efgh has 1. All results are ODD therefore no 1 is inserted. If another entry of abcd and 100 were made, then the count would be 4, and Iseven would be satisfied so all 4 lines would have 1 against them. Having just said that, I realise that entering another line of abcd and -100 would also give that result, which would be incorrect as you need the sum of column B to be zero for matching values in column A. We don't need the concatenation column at all. Just use =IF(AND(ISEVEN(COUNTIF($A$1:$A$15,A1)), SUMPRODUCT(($A$1:$A$15=A1)*($B$1:$B$15))=0),1,"") This works for alpha or numeric in column A -- Regards Roger Govier "via135" wrote in message roups.com... hi! thks for responding again promptly! as you rightly predict i am not that much of goose! btw i am afraid that A2&ABS(B2) in col-B doesn't seems to work for the text in col-A as like A2*ABS(B2) does the trick for the number..! ex: "A" "B" "C" "D" abcd... 100.... abcd100...blank abcd... -100... abcd100...blank abcd... -100... abcd100...blank efgh....100.... efgh100....blank actually i should get abcd... -100...blank efgh....100....blank -via135 Roger Govier wrote: Hi Just change the formula in C2 to =A2&ABS(B2) then, you had already worked out my error for column D, it should have read =IF(ISEVEN(COUNTIF($C$2:$C$15,C2)),1,"") -- Regards Roger Govier "via135" wrote in message roups.com... hi! sorry for disturbing again..! one more follow up question..pl? this formula seems to be ok for number values. if iam having text as ref in the place of number ref in col-A, is there any method to arrive at the result? -via135 Roger Govier wrote: Glad we got there in the end!!! Thanks for the feedback. -- Regards Roger Govier "via135" wrote in message roups.com... that's it! this is what i exactly want! thks roger.. thks a lot..! -via135 Roger Govier wrote: Hi I think I understand. You just want to identify those lines where the number in column A is the same, and, the sum of the numbers adjacent to them in column B equals zero. If so then in column C enter =A2*ABS(B2) In D2 enter =IF(ISEVEN(COUNTIF($D$2:$D$15,D2)),1,"") Filter on column D for values of 1 -- Regards Roger Govier "via135" wrote in message groups.com... hi! i think i've not explained it well! infact my intention is not to delete the duplicate records..! i just want to eliminate the matched pairs of +ve and -ve wrt the other column! for example if i am having 4 records as under 1112........100 1112........-100 1112........-100 1113........100 i don't want to eliminate the duplicate records 2nd & 3rd instead i want to offset 1st & 2nd (reconciled ones) and identify the 3rd and 4th as unreconciled..!! hope u understand..!! regds! -via135 On Nov 14, 2:47 pm, "Roger Govier" wrote: Hi The formula is working correctly.When you apply the Autofilter, use the dropdown on column C choose CustomGreater than0 This will then show your rows which are duplicated. Alternatively, you could wrap the formula in an IF statement =IF(SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2))0,"Duplicate","") Filter column C for Duplicate -- Regards Roger Govier "via135" wrote in glegroups.com... yes..you are absolutely right! i have given "B2" i / o "-B2"..! one more question..the formula gives the right answer only when the each ref no in col A is having some unique value col B..! when the same value is repeated in col B for the same ref no in col A there seems to be some glitch! for example if i extend the data to A2:B15 as under.. A........B 111.... 50 112.... 100 113.... -50 114.... -100 111.... -50 115.... 150 112.... -50 114.... 50 114.... -50 113.... 100 111.... 50 113.... -100 111.... -50 114.... -50 what i am getting is A.......B........C (getting) C (expecting) 111... 50... 2.......................1 112... 100... 0......................0 113... -50... 0.......................0 114... -100... 0......................0 111... -50... 2.......................1 115... 150... 0......................0 112... -50... 0.......................0 114... 50... 2.......................1 114... -50... 1.......................1 113... 100... 1......................1 111... 50... 2.......................1 113... -100.. 1......................1 111... -50... 2.......................1 114... -50... 1.......................0 any suggestion for alteration in the function? -via135 On Nov 13, 10:53 pm, "Roger Govier" wrote: Hi You cannot have entered the formula as I gave you. I get the following results 111 50 1 112 100 0 113 -50 0 114 -100 0 111 -50 1 115 150 0 112 -50 0 114 50 1 114 -50 1 113 100 0 -- Regards Roger Govier "via135 via OfficeKB.com" <u23552@uwe wrote in messagenews:693c38a347d8e@uwe... hi Roger!... read more |
|
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
hi!
i don't want the matched - unmatched entries.. infact i want the *reconciled* - *unreconciled* entries! i think there is a lot of diff btw matching & *reconciling*.!! catch my point..!! -via135 - On Nov 18, 11:47 pm, "Roger Govier" wrote: At the risk of this becoming the longest thread in history<g Yes that is exactly what I said would be the result. You can have one solution, or the other which is why I suggested you have both formulae in adjacent columns and use whichever you want. Firstly, you can get rid of column C and the concatenation - we have now discarded that. If you want 1's to appear against "all" matching entries then the formula is =IF(AND(ISEVEN(COUNTIF($A$1:$A$15,A1)), SUMPRODUCT(($A$1:$A$15=A1)*($B$1:$B$15))=0),1,"") Place this in C1 With your 4 lines of data, all will remain blank because they do not satisfy the criteria. Because we are looking at ALL lines in the range abcd crops up 3 times, so there cannot be a match and efgh crops up only once, so there cannot be a match. The moment you enter another line with abcd in column A and 100 in column B, all 4 lines with abcd will show a 1 in column C because there are 2 matching pairs and the sum of their values in column B is 0. If you also put in column D the following formula =IF(AND(ISEVEN(COUNTIF($A$1:$A1,A1)), SUMPRODUCT(($A$1:$A1=A1)*($B$1:$B1))=0),1,"") then because we are only looking at lines up to and including the current line that we are on, it will put a 1 in column D when the 2 criteria are met, but it will only put it against the second of the matching pair of lines found within the range down to and including that line. Therefore you see a 1 on line 2, because line 1 and line 2 satisfy the criteria I think we need to start again from the beginning with real examples and description of exactly what you are wanting to achieve. In your first posting you said you wanted to eliminate matching records. If you do eliminate the matching records, or copy them to another sheet called Completed or Reconciled, then the first method as above will work fine. If you do not remove them, then as you add further entries of the same values in column A, obviously all of the previously marked lines will become unmarked, as there will be an odd number of entries, and the amounts in column B will not sum to 0. -- Regards Roger Govier "via135" wrote in oglegroups.com... hi! mmhhmmm! now i am getting -A-.......-B-...-C-............-D- abcd... 100..abcd100....blank abcd...-100..abcd100....1 abcd...-100..abcd100....blank efgh....100..efgh100......blank instead -A-......-B-..........-C-..........-D- abcd -100 abcd100....blank efgh 100 efgh100.....blank -via135 On Nov 17, 1:35 pm, "Roger Govier" wrote: Yes you will get all blanks, because none of the collection is satisfying the criteria. Whilst you can "see" that the first 2 entries of abcd do produce a matching pair, there are 3 entries with abcd in column A so it fails firstly because Countif for abcd is not even (3). It also fails because the Sum of values in B against lines having abcd in column A does not come to zero. We can get around this by making the formula carry out its tests as it goes down the column, as opposed to being on the whole column, but then you will only see the 1's appearing against the second of the 2 entries, i.e. the entry which creates the match, hence if you were to try to use Autofilter to view only the matching entries, it would only pick up one half of each matched pair. Perhaps you should use 2 columns, one with the formula as provided already, and one with the modified formula as below =IF(AND(ISEVEN(COUNTIF($A$1:$A1,A1)), SUMPRODUCT(($A$1:$A1=A1)*($B$1:$B1))=0),1,"") You will note that the range is now $A$1:$A1 so it will expand as it is copied down because of the relative second reference whereas the original formula uses absolutes of $A$1:$A$15 and use the whole range unaltered in each of the cells as you copy down. I hope this makes sense to you. -- Regards Roger Govier "via135" wrote in oglegroups.com... hi! i am still getting blanks in the flag Col-C..! instead getting 1s only for first two records the matched pair in the data! the last two abcd...-100 efgh....100 are not matched pairs! -via135 Roger Govier wrote: Hi No, the result is correct. It will only put a value of 1 against entries where there are matching pairs of values. The abcd entries have 3 and the efgh has 1. All results are ODD therefore no 1 is inserted. If another entry of abcd and 100 were made, then the count would be 4, and Iseven would be satisfied so all 4 lines would have 1 against them. Having just said that, I realise that entering another line of abcd and -100 would also give that result, which would be incorrect as you need the sum of column B to be zero for matching values in column A. We don't need the concatenation column at all. Just use =IF(AND(ISEVEN(COUNTIF($A$1:$A$15,A1)), SUMPRODUCT(($A$1:$A$15=A1)*($B$1:$B$15))=0),1,"") This works for alpha or numeric in column A -- Regards Roger Govier "via135" wrote in message roups.com... hi! thks for responding again promptly! as you rightly predict i am not that much of goose! btw i am afraid that A2&ABS(B2) in col-B doesn't seems to work for the text in col-A as like A2*ABS(B2) does the trick for the number..! ex: "A" "B" "C" "D" abcd... 100.... abcd100...blank abcd... -100... abcd100...blank abcd... -100... abcd100...blank efgh....100.... efgh100....blank actually i should get abcd... -100...blank efgh....100....blank -via135 Roger Govier wrote: Hi Just change the formula in C2 to =A2&ABS(B2) then, you had already worked out my error for column D, it should have read =IF(ISEVEN(COUNTIF($C$2:$C$15,C2)),1,"") -- Regards Roger Govier "via135" wrote in message roups.com... hi! sorry for disturbing again..! one more follow up question..pl? this formula seems to be ok for number values. if iam having text as ref in the place of number ref in col-A, is there any method to arrive at the result? -via135 Roger Govier wrote: Glad we got there in the end!!! Thanks for the feedback. -- Regards Roger Govier "via135" wrote in message roups.com... that's it! this is what i exactly want! thks roger.. thks a lot..! -via135 Roger Govier wrote: Hi I think I understand. You just want to identify those lines where the number in column A is the same, and, the sum of the numbers adjacent to them in column B equals zero. If so then in column C enter =A2*ABS(B2) In D2 enter =IF(ISEVEN(COUNTIF($D$2:$D$15,D2)),1,"") Filter on column D for values of 1 -- Regards Roger Govier "via135" wrote in message groups.com... hi! i think i've not explained it well! infact my intention is not to delete the duplicate records..! i just want to eliminate the matched pairs of +ve and -ve wrt the other column! for example if i am having 4 records as under 1112........100 1112........-100 1112........-100 1113........100 i don't want to eliminate the duplicate records 2nd & 3rd instead i want to offset 1st & 2nd (reconciled ones) and identify the 3rd and 4th as unreconciled..!! hope u understand..!! regds! -via135 On Nov 14, 2:47 pm, "Roger Govier" wrote: Hi The formula is working correctly.When you apply the Autofilter, use the dropdown on column C choose CustomGreater than0 This will then show your rows which are duplicated. Alternatively, you could wrap the formula in an IF statement =IF(SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2))0,"Duplicate","") Filter column C for Duplicate -- Regards Roger Govier "via135" wrote in glegroups.com... yes..you are absolutely right! i have given "B2" i / o "-B2"..! one more question..the formula gives the right answer only when the each ref no in col A is having some unique value col B..! when the same value is... read more |
|
#23
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
Hi
Then I think your solution lies with a VBA approach. I have no further suggestions to make. -- Regards Roger Govier "via135" wrote in message ps.com... hi! i don't want the matched - unmatched entries.. infact i want the *reconciled* - *unreconciled* entries! i think there is a lot of diff btw matching & *reconciling*.!! catch my point..!! -via135 - On Nov 18, 11:47 pm, "Roger Govier" wrote: At the risk of this becoming the longest thread in history<g Yes that is exactly what I said would be the result. You can have one solution, or the other which is why I suggested you have both formulae in adjacent columns and use whichever you want. Firstly, you can get rid of column C and the concatenation - we have now discarded that. If you want 1's to appear against "all" matching entries then the formula is =IF(AND(ISEVEN(COUNTIF($A$1:$A$15,A1)), SUMPRODUCT(($A$1:$A$15=A1)*($B$1:$B$15))=0),1,"") Place this in C1 With your 4 lines of data, all will remain blank because they do not satisfy the criteria. Because we are looking at ALL lines in the range abcd crops up 3 times, so there cannot be a match and efgh crops up only once, so there cannot be a match. The moment you enter another line with abcd in column A and 100 in column B, all 4 lines with abcd will show a 1 in column C because there are 2 matching pairs and the sum of their values in column B is 0. If you also put in column D the following formula =IF(AND(ISEVEN(COUNTIF($A$1:$A1,A1)), SUMPRODUCT(($A$1:$A1=A1)*($B$1:$B1))=0),1,"") then because we are only looking at lines up to and including the current line that we are on, it will put a 1 in column D when the 2 criteria are met, but it will only put it against the second of the matching pair of lines found within the range down to and including that line. Therefore you see a 1 on line 2, because line 1 and line 2 satisfy the criteria I think we need to start again from the beginning with real examples and description of exactly what you are wanting to achieve. In your first posting you said you wanted to eliminate matching records. If you do eliminate the matching records, or copy them to another sheet called Completed or Reconciled, then the first method as above will work fine. If you do not remove them, then as you add further entries of the same values in column A, obviously all of the previously marked lines will become unmarked, as there will be an odd number of entries, and the amounts in column B will not sum to 0. -- Regards Roger Govier "via135" wrote in oglegroups.com... hi! mmhhmmm! now i am getting -A-.......-B-...-C-............-D- abcd... 100..abcd100....blank abcd...-100..abcd100....1 abcd...-100..abcd100....blank efgh....100..efgh100......blank instead -A-......-B-..........-C-..........-D- abcd -100 abcd100....blank efgh 100 efgh100.....blank -via135 On Nov 17, 1:35 pm, "Roger Govier" wrote: Yes you will get all blanks, because none of the collection is satisfying the criteria. Whilst you can "see" that the first 2 entries of abcd do produce a matching pair, there are 3 entries with abcd in column A so it fails firstly because Countif for abcd is not even (3). It also fails because the Sum of values in B against lines having abcd in column A does not come to zero. We can get around this by making the formula carry out its tests as it goes down the column, as opposed to being on the whole column, but then you will only see the 1's appearing against the second of the 2 entries, i.e. the entry which creates the match, hence if you were to try to use Autofilter to view only the matching entries, it would only pick up one half of each matched pair. Perhaps you should use 2 columns, one with the formula as provided already, and one with the modified formula as below =IF(AND(ISEVEN(COUNTIF($A$1:$A1,A1)), SUMPRODUCT(($A$1:$A1=A1)*($B$1:$B1))=0),1,"") You will note that the range is now $A$1:$A1 so it will expand as it is copied down because of the relative second reference whereas the original formula uses absolutes of $A$1:$A$15 and use the whole range unaltered in each of the cells as you copy down. I hope this makes sense to you. -- Regards Roger Govier "via135" wrote in oglegroups.com... hi! i am still getting blanks in the flag Col-C..! instead getting 1s only for first two records the matched pair in the data! the last two abcd...-100 efgh....100 are not matched pairs! -via135 Roger Govier wrote: Hi No, the result is correct. It will only put a value of 1 against entries where there are matching pairs of values. The abcd entries have 3 and the efgh has 1. All results are ODD therefore no 1 is inserted. If another entry of abcd and 100 were made, then the count would be 4, and Iseven would be satisfied so all 4 lines would have 1 against them. Having just said that, I realise that entering another line of abcd and -100 would also give that result, which would be incorrect as you need the sum of column B to be zero for matching values in column A. We don't need the concatenation column at all. Just use =IF(AND(ISEVEN(COUNTIF($A$1:$A$15,A1)), SUMPRODUCT(($A$1:$A$15=A1)*($B$1:$B$15))=0),1,"") This works for alpha or numeric in column A -- Regards Roger Govier "via135" wrote in message roups.com... hi! thks for responding again promptly! as you rightly predict i am not that much of goose! btw i am afraid that A2&ABS(B2) in col-B doesn't seems to work for the text in col-A as like A2*ABS(B2) does the trick for the number..! ex: "A" "B" "C" "D" abcd... 100.... abcd100...blank abcd... -100... abcd100...blank abcd... -100... abcd100...blank efgh....100.... efgh100....blank actually i should get abcd... -100...blank efgh....100....blank -via135 Roger Govier wrote: Hi Just change the formula in C2 to =A2&ABS(B2) then, you had already worked out my error for column D, it should have read =IF(ISEVEN(COUNTIF($C$2:$C$15,C2)),1,"") -- Regards Roger Govier "via135" wrote in message roups.com... hi! sorry for disturbing again..! one more follow up question..pl? this formula seems to be ok for number values. if iam having text as ref in the place of number ref in col-A, is there any method to arrive at the result? -via135 Roger Govier wrote: Glad we got there in the end!!! Thanks for the feedback. -- Regards Roger Govier "via135" wrote in message roups.com... that's it! this is what i exactly want! thks roger.. thks a lot..! -via135 Roger Govier wrote: Hi I think I understand. You just want to identify those lines where the number in column A is the same, and, the sum of the numbers adjacent to them in column B equals zero. If so then in column C enter =A2*ABS(B2) In D2 enter =IF(ISEVEN(COUNTIF($D$2:$D$15,D2)),1,"") Filter on column D for values of 1 -- Regards Roger Govier "via135" wrote in message groups.com... hi! i think i've not explained it well! infact my intention is not to delete the duplicate records..! i just want to eliminate the matched pairs of +ve and -ve wrt the other column! for example if i am having 4 records as under 1112........100 1112........-100 1112........-100 1113........100 i don't want to eliminate the duplicate records 2nd & 3rd instead i want to offset 1st & 2nd (reconciled ones) and identify the 3rd and 4th as unreconciled..!! hope u understand..!! regds! -via135 On Nov 14, 2:47 pm, "Roger Govier" wrote: Hi The formula is working correctly.When you apply the Autofilter, use the dropdown on column C choose CustomGreater than0 This will then show your rows which are duplicated. Alternatively, you could wrap the formula in an IF statement =IF(SUMPRODUCT(($A$2:$A$11=A2)*($B$2:$B$11=-B2))0,"Duplicate","") Filter column C for Duplicate -- Regards Roger Govier "via135" wrote in glegroups.com... yes..you are absolutely right! i have given "B2" i / o "-B2"..! one more question..the formula gives the right answer only when the each ref no in col A is having some unique value col B..! when the same value is... read more |
|
#24
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
thks Roger!
for the professional approach! i'm not well versed with VBA..!! ok..leave it! thks again for sincere responding! -via135 On Nov 19, 5:18 pm, "Roger Govier" wrote: Hi Then I think your solution lies with a VBA approach. I have no further suggestions to make. -- Regards Roger Govier "via135" wrote in glegroups.com... hi! i don't want the matched - unmatched entries.. infact i want the *reconciled* - *unreconciled* entries! i think there is a lot of diff btw matching & *reconciling*.!! catch my point..!! -via135 - On Nov 18, 11:47 pm, "Roger Govier" wrote: At the risk of this becoming the longest thread in history<g Yes that is exactly what I said would be the result. You can have one solution, or the other which is why I suggested you have both formulae in adjacent columns and use whichever you want. Firstly, you can get rid of column C and the concatenation - we have now discarded that. If you want 1's to appear against "all" matching entries then the formula is =IF(AND(ISEVEN(COUNTIF($A$1:$A$15,A1)), SUMPRODUCT(($A$1:$A$15=A1)*($B$1:$B$15))=0),1,"") Place this in C1 With your 4 lines of data, all will remain blank because they do not satisfy the criteria. Because we are looking at ALL lines in the range abcd crops up 3 times, so there cannot be a match and efgh crops up only once, so there cannot be a match. The moment you enter another line with abcd in column A and 100 in column B, all 4 lines with abcd will show a 1 in column C because there are 2 matching pairs and the sum of their values in column B is 0. If you also put in column D the following formula =IF(AND(ISEVEN(COUNTIF($A$1:$A1,A1)), SUMPRODUCT(($A$1:$A1=A1)*($B$1:$B1))=0),1,"") then because we are only looking at lines up to and including the current line that we are on, it will put a 1 in column D when the 2 criteria are met, but it will only put it against the second of the matching pair of lines found within the range down to and including that line. Therefore you see a 1 on line 2, because line 1 and line 2 satisfy the criteria I think we need to start again from the beginning with real examples and description of exactly what you are wanting to achieve. In your first posting you said you wanted to eliminate matching records. If you do eliminate the matching records, or copy them to another sheet called Completed or Reconciled, then the first method as above will work fine. If you do not remove them, then as you add further entries of the same values in column A, obviously all of the previously marked lines will become unmarked, as there will be an odd number of entries, and the amounts in column B will not sum to 0. -- Regards Roger Govier "via135" wrote in oglegroups.com... hi! mmhhmmm! now i am getting -A-.......-B-...-C-............-D- abcd... 100..abcd100....blank abcd...-100..abcd100....1 abcd...-100..abcd100....blank efgh....100..efgh100......blank instead -A-......-B-..........-C-..........-D- abcd -100 abcd100....blank efgh 100 efgh100.....blank -via135 On Nov 17, 1:35 pm, "Roger Govier" wrote: Yes you will get all blanks, because none of the collection is satisfying the criteria. Whilst you can "see" that the first 2 entries of abcd do produce a matching pair, there are 3 entries with abcd in column A so it fails firstly because Countif for abcd is not even (3). It also fails because the Sum of values in B against lines having abcd in column A does not come to zero. We can get around this by making the formula carry out its tests as it goes down the column, as opposed to being on the whole column, but then you will only see the 1's appearing against the second of the 2 entries, i.e. the entry which creates the match, hence if you were to try to use Autofilter to view only the matching entries, it would only pick up one half of each matched pair. Perhaps you should use 2 columns, one with the formula as provided already, and one with the modified formula as below =IF(AND(ISEVEN(COUNTIF($A$1:$A1,A1)), SUMPRODUCT(($A$1:$A1=A1)*($B$1:$B1))=0),1,"") You will note that the range is now $A$1:$A1 so it will expand as it is copied down because of the relative second reference whereas the original formula uses absolutes of $A$1:$A$15 and use the whole range unaltered in each of the cells as you copy down. I hope this makes sense to you. -- Regards Roger Govier "via135" wrote in oglegroups.com... hi! i am still getting blanks in the flag Col-C..! instead getting 1s only for first two records the matched pair in the data! the last two abcd...-100 efgh....100 are not matched pairs! -via135 Roger Govier wrote: Hi No, the result is correct. It will only put a value of 1 against entries where there are matching pairs of values. The abcd entries have 3 and the efgh has 1. All results are ODD therefore no 1 is inserted. If another entry of abcd and 100 were made, then the count would be 4, and Iseven would be satisfied so all 4 lines would have 1 against them. Having just said that, I realise that entering another line of abcd and -100 would also give that result, which would be incorrect as you need the sum of column B to be zero for matching values in column A. We don't need the concatenation column at all. Just use =IF(AND(ISEVEN(COUNTIF($A$1:$A$15,A1)), SUMPRODUCT(($A$1:$A$15=A1)*($B$1:$B$15))=0),1,"") This works for alpha or numeric in column A -- Regards Roger Govier "via135" wrote in message roups.com... hi! thks for responding again promptly! as you rightly predict i am not that much of goose! btw i am afraid that A2&ABS(B2) in col-B doesn't seems to work for the text in col-A as like A2*ABS(B2) does the trick for the number..! ex: "A" "B" "C" "D" abcd... 100.... abcd100...blank abcd... -100... abcd100...blank abcd... -100... abcd100...blank efgh....100.... efgh100....blank actually i should get abcd... -100...blank efgh....100....blank -via135 Roger Govier wrote: Hi Just change the formula in C2 to =A2&ABS(B2) then, you had already worked out my error for column D, it should have read =IF(ISEVEN(COUNTIF($C$2:$C$15,C2)),1,"") -- Regards Roger Govier "via135" wrote in message roups.com... hi! sorry for disturbing again..! one more follow up question..pl? this formula seems to be ok for number values. if iam having text as ref in the place of number ref in col-A, is there any method to arrive at the result? -via135 Roger Govier wrote: Glad we got there in the end!!! Thanks for the feedback. -- Regards Roger Govier "via135" wrote in message roups.com... that's it! this is what i exactly want! thks roger.. thks a lot..! -via135 Roger Govier wrote: Hi I think I understand. You just want to identify those lines where the number in column A is the same, and, the sum of the numbers adjacent to them in column B equals zero. If so then in column C enter =A2*ABS(B2) In D2 enter =IF(ISEVEN(COUNTIF($D$2:$D$15,D2)),1,"") Filter on column D for values of 1 -- Regards Roger Govier "via135" wrote in message groups.com.... hi! i think i've not explained it well! infact my intention is not to delete the duplicate records..! i just want to eliminate the matched pairs of +ve and -ve wrt the other column! for example if i am having 4 records as under 1112........100 1112........-100 1112........-100 1113........100 i don't want to eliminate the duplicate records 2nd & 3rd instead i want to offset 1st & 2nd (reconciled ones) and identify the 3rd and 4th as unreconciled..!! hope u understand..!! regds! -via135 On Nov 14, 2:47 pm, "Roger Govier" wrote: Hi The formula is working correctly.When you... read more |
|
#25
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
eliminating matched records!
Hi
If you want to send me a copy of your real data and an explanation of what you are trying to do, I will try and write some code for you. To email direct, remove NOSPAM from my address. -- Regards Roger Govier "via135" wrote in message oups.com... thks Roger! for the professional approach! i'm not well versed with VBA..!! ok..leave it! thks again for sincere responding! -via135 On Nov 19, 5:18 pm, "Roger Govier" wrote: Hi Then I think your solution lies with a VBA approach. I have no further suggestions to make. -- Regards Roger Govier "via135" wrote in glegroups.com... hi! i don't want the matched - unmatched entries.. infact i want the *reconciled* - *unreconciled* entries! i think there is a lot of diff btw matching & *reconciling*.!! catch my point..!! -via135 - On Nov 18, 11:47 pm, "Roger Govier" wrote: At the risk of this becoming the longest thread in history<g Yes that is exactly what I said would be the result. You can have one solution, or the other which is why I suggested you have both formulae in adjacent columns and use whichever you want. Firstly, you can get rid of column C and the concatenation - we have now discarded that. If you want 1's to appear against "all" matching entries then the formula is =IF(AND(ISEVEN(COUNTIF($A$1:$A$15,A1)), SUMPRODUCT(($A$1:$A$15=A1)*($B$1:$B$15))=0),1,"") Place this in C1 With your 4 lines of data, all will remain blank because they do not satisfy the criteria. Because we are looking at ALL lines in the range abcd crops up 3 times, so there cannot be a match and efgh crops up only once, so there cannot be a match. The moment you enter another line with abcd in column A and 100 in column B, all 4 lines with abcd will show a 1 in column C because there are 2 matching pairs and the sum of their values in column B is 0. If you also put in column D the following formula =IF(AND(ISEVEN(COUNTIF($A$1:$A1,A1)), SUMPRODUCT(($A$1:$A1=A1)*($B$1:$B1))=0),1,"") then because we are only looking at lines up to and including the current line that we are on, it will put a 1 in column D when the 2 criteria are met, but it will only put it against the second of the matching pair of lines found within the range down to and including that line. Therefore you see a 1 on line 2, because line 1 and line 2 satisfy the criteria I think we need to start again from the beginning with real examples and description of exactly what you are wanting to achieve. In your first posting you said you wanted to eliminate matching records. If you do eliminate the matching records, or copy them to another sheet called Completed or Reconciled, then the first method as above will work fine. If you do not remove them, then as you add further entries of the same values in column A, obviously all of the previously marked lines will become unmarked, as there will be an odd number of entries, and the amounts in column B will not sum to 0. -- Regards Roger Govier "via135" wrote in oglegroups.com... hi! mmhhmmm! now i am getting -A-.......-B-...-C-............-D- abcd... 100..abcd100....blank abcd...-100..abcd100....1 abcd...-100..abcd100....blank efgh....100..efgh100......blank instead -A-......-B-..........-C-..........-D- abcd -100 abcd100....blank efgh 100 efgh100.....blank -via135 On Nov 17, 1:35 pm, "Roger Govier" wrote: Yes you will get all blanks, because none of the collection is satisfying the criteria. Whilst you can "see" that the first 2 entries of abcd do produce a matching pair, there are 3 entries with abcd in column A so it fails firstly because Countif for abcd is not even (3). It also fails because the Sum of values in B against lines having abcd in column A does not come to zero. We can get around this by making the formula carry out its tests as it goes down the column, as opposed to being on the whole column, but then you will only see the 1's appearing against the second of the 2 entries, i.e. the entry which creates the match, hence if you were to try to use Autofilter to view only the matching entries, it would only pick up one half of each matched pair. Perhaps you should use 2 columns, one with the formula as provided already, and one with the modified formula as below =IF(AND(ISEVEN(COUNTIF($A$1:$A1,A1)), SUMPRODUCT(($A$1:$A1=A1)*($B$1:$B1))=0),1,"") You will note that the range is now $A$1:$A1 so it will expand as it is copied down because of the relative second reference whereas the original formula uses absolutes of $A$1:$A$15 and use the whole range unaltered in each of the cells as you copy down. I hope this makes sense to you. -- Regards Roger Govier "via135" wrote in oglegroups.com... hi! i am still getting blanks in the flag Col-C..! instead getting 1s only for first two records the matched pair in the data! the last two abcd...-100 efgh....100 are not matched pairs! -via135 Roger Govier wrote: Hi No, the result is correct. It will only put a value of 1 against entries where there are matching pairs of values. The abcd entries have 3 and the efgh has 1. All results are ODD therefore no 1 is inserted. If another entry of abcd and 100 were made, then the count would be 4, and Iseven would be satisfied so all 4 lines would have 1 against them. Having just said that, I realise that entering another line of abcd and -100 would also give that result, which would be incorrect as you need the sum of column B to be zero for matching values in column A. We don't need the concatenation column at all. Just use =IF(AND(ISEVEN(COUNTIF($A$1:$A$15,A1)), SUMPRODUCT(($A$1:$A$15=A1)*($B$1:$B$15))=0),1,"") This works for alpha or numeric in column A -- Regards Roger Govier "via135" wrote in message roups.com... hi! thks for responding again promptly! as you rightly predict i am not that much of goose! btw i am afraid that A2&ABS(B2) in col-B doesn't seems to work for the text in col-A as like A2*ABS(B2) does the trick for the number..! ex: "A" "B" "C" "D" abcd... 100.... abcd100...blank abcd... -100... abcd100...blank abcd... -100... abcd100...blank efgh....100.... efgh100....blank actually i should get abcd... -100...blank efgh....100....blank -via135 Roger Govier wrote: Hi Just change the formula in C2 to =A2&ABS(B2) then, you had already worked out my error for column D, it should have read =IF(ISEVEN(COUNTIF($C$2:$C$15,C2)),1,"") -- Regards Roger Govier "via135" wrote in message roups.com... hi! sorry for disturbing again..! one more follow up question..pl? this formula seems to be ok for number values. if iam having text as ref in the place of number ref in col-A, is there any method to arrive at the result? -via135 Roger Govier wrote: Glad we got there in the end!!! Thanks for the feedback. -- Regards Roger Govier "via135" wrote in message roups.com... that's it! this is what i exactly want! thks roger.. thks a lot..! -via135 Roger Govier wrote: Hi I think I understand. You just want to identify those lines where the number in column A is the same, and, the sum of the numbers adjacent to them in column B equals zero. If so then in column C enter =A2*ABS(B2) In D2 enter =IF(ISEVEN(COUNTIF($D$2:$D$15,D2)),1,"") Filter on column D for values of 1 -- Regards Roger Govier "via135" wrote in message groups.com... hi! i think i've not explained it well! infact my intention is not to delete the duplicate records..! i just want to eliminate the matched pairs of +ve and -ve wrt the other column! for example if i am having 4 records as under 1112........100 1112........-100 1112........-100 1113........100 i don't want to eliminate the duplicate records 2nd & 3rd instead i want to offset 1st & 2nd (reconciled ones) and identify the 3rd and 4th as unreconciled..!! hope u understand..!! regds! -via135 On Nov 14, 2:47 pm, "Roger Govier" wrote: Hi The formula is working correctly.When you... read more |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| eliminating records! | Excel Worksheet Functions | |||
| Check and Remove Records | Excel Discussion (Misc queries) | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
| Top 90% of records - Bring out number | Excel Discussion (Misc queries) | |||
| Deleting specific records | Excel Discussion (Misc queries) | |||
Linear Mode
