| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract select group of numbers
I have a large column of numbers containing both positive and negative
amounts. I created the absolute value of these numbers in a separate column. Sorting the absolute value indicates I have offsetting amounts in the original number column.How can I extract only those values that are not offset? For example, Amt = 1,000.00 Offset = -1,000.00 Also, some of the amounts have multiples of the same number. For example after sorting I'll have: 15,000.00 15,000.00 15,000.00 .. .. .. 15,000.00 -15,000.00 -15,000.00 -15,000.00 .. .. .. -15,000.00 525.00 475.00 125,000.00 450,000.00 100.00 75.00 .. .. .. Thanks for your help. JHL |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract select group of numbers
With the Company ID in A1:A20, Amts in B1:B20 and with a list of Ids in
E2:E7, then F2 =IF(SUMIF($A$2:$A$20,$E2,$B$2:$B$20)<0,SUMIF($A$2 :$A$20,$E2,$B$2:$B$20),"") Watch the wrapping, and copy down. You'll have to change the ranges to suite. Regards Peter "JHL" wrote: I have a large column of numbers containing both positive and negative amounts. I created the absolute value of these numbers in a separate column. Sorting the absolute value indicates I have offsetting amounts in the original number column.How can I extract only those values that are not offset? For example, Amt = 1,000.00 Offset = -1,000.00 Also, some of the amounts have multiples of the same number. For example after sorting I'll have: 15,000.00 15,000.00 15,000.00 .. .. .. 15,000.00 -15,000.00 -15,000.00 -15,000.00 .. .. .. -15,000.00 525.00 475.00 125,000.00 450,000.00 100.00 75.00 .. .. .. Thanks for your help. JHL |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract select group of numbers
Thank you for your response. I'm not sure what the "id" is since, I didn't
have an id nor did you give an example if I need to create one. Maybe, I need add to my original comment. Where I gave the examples of the numbers AFTER sorting on the absolute value, I want to to be able to extract just these amounts from my example. 525.00 475.00 125,000.00 450,000.00 100.00 75.00 "Billy Liddel" wrote in message ... With the Company ID in A1:A20, Amts in B1:B20 and with a list of Ids in E2:E7, then F2 =IF(SUMIF($A$2:$A$20,$E2,$B$2:$B$20)<0,SUMIF($A$2 :$A$20,$E2,$B$2:$B$20),"") Watch the wrapping, and copy down. You'll have to change the ranges to suite. Regards Peter "JHL" wrote: I have a large column of numbers containing both positive and negative amounts. I created the absolute value of these numbers in a separate column. Sorting the absolute value indicates I have offsetting amounts in the original number column.How can I extract only those values that are not offset? For example, Amt = 1,000.00 Offset = -1,000.00 Also, some of the amounts have multiples of the same number. For example after sorting I'll have: 15,000.00 15,000.00 15,000.00 .. .. .. 15,000.00 -15,000.00 -15,000.00 -15,000.00 .. .. .. -15,000.00 525.00 475.00 125,000.00 450,000.00 100.00 75.00 .. .. .. Thanks for your help. JHL |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract select group of numbers
Sorry
I just assumed that the numbers refered to a client, try this. =IF(COUNTIF($A$2:$A$500,A2)-COUNTIF($A$2:$A$500,-A2)=1,A2,"") You do not need the absolute list. Enter in B2 say, and copy down. Peter "JHL" wrote: Thank you for your response. I'm not sure what the "id" is since, I didn't have an id nor did you give an example if I need to create one. Maybe, I need add to my original comment. Where I gave the examples of the numbers AFTER sorting on the absolute value, I want to to be able to extract just these amounts from my example. 525.00 475.00 125,000.00 450,000.00 100.00 75.00 "Billy Liddel" wrote in message ... With the Company ID in A1:A20, Amts in B1:B20 and with a list of Ids in E2:E7, then F2 =IF(SUMIF($A$2:$A$20,$E2,$B$2:$B$20)<0,SUMIF($A$2 :$A$20,$E2,$B$2:$B$20),"") Watch the wrapping, and copy down. You'll have to change the ranges to suite. Regards Peter "JHL" wrote: I have a large column of numbers containing both positive and negative amounts. I created the absolute value of these numbers in a separate column. Sorting the absolute value indicates I have offsetting amounts in the original number column.How can I extract only those values that are not offset? For example, Amt = 1,000.00 Offset = -1,000.00 Also, some of the amounts have multiples of the same number. For example after sorting I'll have: 15,000.00 15,000.00 15,000.00 .. .. .. 15,000.00 -15,000.00 -15,000.00 -15,000.00 .. .. .. -15,000.00 525.00 475.00 125,000.00 450,000.00 100.00 75.00 .. .. .. Thanks for your help. JHL |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel formula to extract a group of data from another list | Excel Worksheet Functions | |||
| How do I select a number from a specific group of numbers? | Excel Worksheet Functions | |||
| How can I convert a group of numbers to a group of letters? | Excel Worksheet Functions | |||
| Can I select and Group Autoshapes in Excel 97 ? | New Users to Excel | |||
| I can't select/edit objects. It says [Group] on the header bar. | Excel Discussion (Misc queries) | |||
Linear Mode
