If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
compare -neg and +pos number that cancel each other in a colum
I saw your response to to Chom krosopon in July of 2005 and I noticed that the formula worked perferctly. My problem is similar to Chom's however my data is a concatenation of date and amount. For example instead of just looking for +123 and -123 I am looking for 1/2/07 +$123 and 1/2/07 -$123. Below is a copy of your response to Chom in July of 2005. Is there a formula that would indicate this? Thanks George |
#2
|
|||
|
|||
compare -neg and +pos number that cancel each other in a colum
One way to amend it to suit is illustrated in this sample:
http://www.savefile.com/files/666555 NettOffNegative_n_PositiveNos_v2.xls Assume your source concat data is within A1:A100 in Sheet1 In Sheet 1, Use Data Text to Columns to split the concat data into 2 cols, col A for the dates, col B for the amounts. Select col A, click Data Text to Columns (delimited). Click Next, check "Space" in step 2. Click Next. In step 3 of the wiz., select col A in the data preview window, check "Date", then select the correct date format from the droplist. Click Finish. Then place in C1: =IF(B1="","",COUNTIF($B$1:B1,B1)) Put in D1, array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(C1="","",IF(ISNUMBER(MATCH(-B1&"_"&C1&"_"&A1,$B$1:$B$100&"_"&$C$1:$C$100&"_"&$ A$1:$A$100,0)),"",ROW())) Select C11, copy down to D100. In Sheet 2, Put in A1 (normal ENTER): =IF(ROW()COUNT(Sheet1!$D:$D),"",INDEX(Sheet1!A:A, SMALL(Sheet1!$D:$D,ROW()))) Copy A1 to B1, then fill down to B100. Format col A as date, col B as currency to taste. Sheet2 returns the required results, ie only the o/s lines from Sheet1 with date - amounts which do not cancel each other. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "checkQ" wrote in message ... I saw your response to to Chom krosopon in July of 2005 and I noticed that the formula worked perferctly. My problem is similar to Chom's however my data is a concatenation of date and amount. For example instead of just looking for +123 and -123 I am looking for 1/2/07 +$123 and 1/2/07 -$123. Below is a copy of your response to Chom in July of 2005. Is there a formula that would indicate this? Thanks George |
#3
|
|||
|
|||
compare -neg and +pos number that cancel each other in a colum
Awsome!!
It worked perfectly Max. Thanks "Max" wrote: One way to amend it to suit is illustrated in this sample: http://www.savefile.com/files/666555 NettOffNegative_n_PositiveNos_v2.xls Assume your source concat data is within A1:A100 in Sheet1 In Sheet 1, Use Data Text to Columns to split the concat data into 2 cols, col A for the dates, col B for the amounts. Select col A, click Data Text to Columns (delimited). Click Next, check "Space" in step 2. Click Next. In step 3 of the wiz., select col A in the data preview window, check "Date", then select the correct date format from the droplist. Click Finish. Then place in C1: =IF(B1="","",COUNTIF($B$1:B1,B1)) Put in D1, array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(C1="","",IF(ISNUMBER(MATCH(-B1&"_"&C1&"_"&A1,$B$1:$B$100&"_"&$C$1:$C$100&"_"&$ A$1:$A$100,0)),"",ROW())) Select C11, copy down to D100. In Sheet 2, Put in A1 (normal ENTER): =IF(ROW()COUNT(Sheet1!$D:$D),"",INDEX(Sheet1!A:A, SMALL(Sheet1!$D:$D,ROW()))) Copy A1 to B1, then fill down to B100. Format col A as date, col B as currency to taste. Sheet2 returns the required results, ie only the o/s lines from Sheet1 with date - amounts which do not cancel each other. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "checkQ" wrote in message ... I saw your response to to Chom krosopon in July of 2005 and I noticed that the formula worked perferctly. My problem is similar to Chom's however my data is a concatenation of date and amount. For example instead of just looking for +123 and -123 I am looking for 1/2/07 +$123 and 1/2/07 -$123. Below is a copy of your response to Chom in July of 2005. Is there a formula that would indicate this? Thanks George |
#4
|
|||
|
|||
compare -neg and +pos number that cancel each other in a colum
Welcome, George.
Good to hear that. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "checkQ" wrote in message ... Awsome!! It worked perfectly Max. Thanks |
#5
|
|||
|
|||
compare -neg and +pos number that cancel each other in a colum
Hi Max,
I have a similar case, can you please help. Below is the data table I have. On the 4th column "Status", I need to know which invoices remain outstanding and which ones cancel off (squares with Credit amount). Will appreciate very much if you can teach me a quick way to do it. Customer Doc No. Amount Status Susan Inv3355 893.10 Cancels off Nelly Inv3356 250.14 Cancels off Tom Inv3357 462.91 Cancels off Nelly Cre1221 -250.14 Cancels off Nelly Inv3358 350.14 Outstanding Susan Cre1222 -893.10 Cancels off Susan Inv3359 793.10 Outstanding Tom Cre1223 -462.91 Cancels off Tom Inv3360 462.91 Outstanding Burt Cre1224 101.99 Outstanding -- Many thanks, Casper "Max" wrote: Welcome, George. Good to hear that. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "checkQ" wrote in message ... Awsome!! It worked perfectly Max. Thanks |
Thread Tools | |
Display Modes | |
|
|