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

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

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

Welcome, George.
Good to hear that.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"checkQ" wrote in message
...
Awsome!!
It worked perfectly Max. Thanks

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