need help with formula checking dupt ids & positive or negat

[YANECKC]

I HAVE SPREADSHEET WITH 9 COLUMNS.
IN COLUMN C (I HAVE ID CODES).
IN COLUMN E (I HAVE QUANTITIES).
IN COLUMN B (I OVERLAYED A FORMULA LOOKING FOR DUPTLICATE ID CODES. A TRUE WOULD APPEAR IF DUPLICATE ID CODES WERE IN COLUMN C. IF NONE A FALSE WOULD APPEAR. HERE IS THE FORMULA I INPUT IN COLUMN B
=COUNTIF($C$1:$C$3800,C2)>1

IT WORKS BOTH I WANT TO MODIFY THAT FORMULA TO SAY IF DUPLICATE ID CODE IN COLUMN C AND QUANTITIES IN COLUMN E ARE NEGATIVE TEXT WOULD APPEAR AS "DUPT NEGATIVES"

IF DUPLICATE ID CODE IN COLUMN C AND QUANTITIES IN COLUMN E ARE POSITIVE TEXT WOULD APPEAR AS "DUPT POSITIVES"

IF DUPLICATE ID CODE IN COLUMN C AND QUANTITIES IN COLUMN E ARE POSITIVES AND NEGATIVES TEXT WOULD APPEAR AS "DUPLICATE BOTH"

IF NO DUPLICATE ID CODE IN COLUMN C TEXT WOULD APPEAR AS "NO DUPLICATES"

THANK YOU FOR YOUR ANTICIPATED RESPONSE
YANECKC
This message was edited by YANECKC on 2002-10-09 16:48

 

[Mark W.]

On 2002-10-08 19:26, YANECKC wrote:
I HAVE SPREADSHEET WITH 9 COLUMNS.
IN COLUMN I (I HAVE ID CODES).
IN COLUMN E (I HAVE QUANTITIES).
IN COLUMN B (I OVERLAYED A FORMULA LOOKING FOR DUPTLICATE ID CODES. A TRUE WOULD APPEAR IF DUPLICATE ID CODES WERE IN COLUMN I. IF NONE A FALSE WOULD APPEAR. HERE IS THE FORMULA I INPUT IN COLUMN B
=COUNTIF($C$1:$C$3800,C2)>1...

If your ID CODES are in column I then why does your formula in column B reference column C?

 

[YANECKC]

YOUR ARE RIGHT ! TYPEO ERROR.
COLUMN C HAS THE ID CODES. SORRY

 

[YANECKC]

TO MARK OR ANYBODY

NOW THAT YOU KNOW I MADE A MISTAKE AND ID CODE IS IN COLUMN C

HOW WOULD YOU MODIFY THE FORMULA OF
=COUNTIF($C$1:$C$3800,C2)>1

THANK YOU FOR YOUR ANTICIPATED RESPONSE
YANECKC

 

[zzydhf]

This formula just check the current row for the value in E:
=IF(COUNTIF($C$2:$C$8,C2)>1,IF(E2<0,"Negative comment","True"),"False")

This formula check the total values for all ids that are the same:
=IF(COUNTIF($C$2:$C$8,C2)>1,IF(SUMIF($C$2:$C$8,C2,$E$2:$E$8)<0,"Negative Total","True"),"False")

 

[Scott R]

On 2002-10-08 19:26, YANECKC wrote:
I HAVE SPREADSHEET WITH 9 COLUMNS.
IN COLUMN I (I HAVE ID CODES).
IN COLUMN E (I HAVE QUANTITIES).
IN COLUMN B (I OVERLAYED A FORMULA LOOKING FOR DUPTLICATE ID CODES. A TRUE WOULD APPEAR IF DUPLICATE ID CODES WERE IN COLUMN I. IF NONE A FALSE WOULD APPEAR. HERE IS THE FORMULA I INPUT IN COLUMN B
=COUNTIF($C$1:$C$3800,C2)>1
IT WORKS BOTH I WANT TO MODIFY THAT FORMULA TO SAY IF DUPLICATE ID CODE IN COLUMN I AND QUANTITIES IN COLUMN E ARE NEGATIVE TEXT WOULD APPEAR AS "DUPT NEGATIVES"
IF DUPLICATE ID CODE IN COLUMN I AND QUANTITIES IN COLUMN E ARE POSITIVE TEXT WOULD APPEAR AS "DUPT POSITIVES"
IF DUPLICATE ID CODE IN COLUMN I AND QUANTITIES IN COLUMN E ARE POSITIVES AND NEGATIVES TEXT WOULD APPEAR AS "DUPLICATE BOTH"
IF NO DUPLICATE ID CODE IN COLUMN I TEXT WOULD APPEAR AS "NO DUPLICATES"

THANK YOU FOR YOUR ANTICIPATED RESPONSE
YANECKC

This gets it done but it's very slow if you're evaluating 3800 records. Performance improvements anybody...? Enter this in B2 as array formula and copy down as necessary. Replace the $10 with $3800 or however many records you're evaluating. I'm assuming that a number >= 0 is positive:

=IF(COUNTIF($I$2:$I$10,I2)>1,CHOOSE((MIN(IF(I2=I$2:I$10,IF(E$2:E$10>=0,1,-1)))+MAX(IF(I2=I$2:I$10,IF(E$2:E$10>=0,1,-1))))*0.5+2,"-","+/-","+"),"None")

 

[YANECKC]

My original formula works to identify duplicate ID CODES.

=COUNTIF($C$1:$C$3800,C2)>1

I want to modify that formula in column B to say if duplicate ID CODE in column C and quantities in column E are both negative text would appear as "dupt negatives"

If duplicate ID CODE in column C and quantities in column E are both positive text would appear as "dupt positives"

If duplicate ID CODE in column C and quantities in column E are positives and negatives text would appear as "duplicate both"

If no duplicate ID CODE in column C text would appear as "no duplicates"

ZZYDHF's Second formula is close. The formula should check all ids for duplicates but it should not total the net value.

Using test data it should look like below
I only show columns A B C E

A/C DETAIL ID CODES Q U A N T I T Y
1 Dupt Pos A001470 50
2 Dupt Pos A001470 7,100
1 Dupt Both A001590 64,500
2 Dupt Both A001590 -11,600
1 Dupt Neg A002222 -78,567
2 Dupt Neg A002222 -1,990
1 No Dupt A234555 -4,456
1 Dupt Pos A345555 234,000
2 Dupt Pos A345555 23,450
2 No Dupt A456345 789,000
1 Dupt Both A998990 16,500
2 Dupt Both A998990 -81,600


thank you for your anticipated response
yaneckc

 

[YANECKC]

I AM STILL LOOKING FOR RESOLUTION IN MODIFING MY FORMULA.

IF THE LAST TEST SAMPLE CAN NOT BE DONE MAYBE THE MY FORMULA CAN BE MODIFIED TO LOOK FOR THE DUPTLICATED ID CODE THEN LOOK IN COLUMN E
IN BOTH ROWS. IF IT FINDS THAT IT IS A DUPT ID CODE AND ONE OF THE AMOUNTS IN COLUMN E IS POSITVE AND ONE OF THE AMOUNTS IS NEGATIVE THE WORDS "1POS1NEG" WILL APPEAR IN COLUMN B WHERE THE FORMULA IS.

IF DUPT ID CODE AND BOTH AMOUNTS IN COLUMN E ARE NEGATIVE THE WORDS "2NEG" WILL APPEAR IN COLUMN B WHERE THE FORMULA IS.

IF DUPT ID CODE AND BOTH AMOUNTS IN COLUMN E ARE POSITIVE THE WORDS "2POS" WILL APPEAR IN COLUMN B WHERE THE FORMULA IS.

IF NO DUPTLICATE ID CODE IS FOUND THE WORDS "NO DUPT" WILL APPEAR IN COLUMN B WHERE THE FORMULA IS.

ANY HELP WOULD BE APPRECIATED.
YANECKC

 

[Aladin Akyurek]

Yaneck,

Pas de majuscule, s'il te plait.

Voir si ca te convient...

aaPos&Negs YANECKC.xls
	A	B	C	D
1	A/C	DETAIL	IDCODES	QUANTITY
2			A001470	50
3	2	2Pos0Neg	A001470	7,100
4			A001590	64,500
5	2	1Pos1Neg	A001590	-11,600
6			A002222	-78,567
7	2	0Pos2Neg	A002222	-1,990
8	1	0Pos1Neg	A234555	-4,456
9			A345555	234,000
10	2	2Pos0Neg	A345555	23,450
11	1	1Pos0Neg	A456345	789,000
12			A998990	16,500
13	2	1Pos1Neg	A998990	-81,600
Sheet1

Salut

 

[YANECKC]

To Aladin Akyurek

That was exactly what I was looking for.
Thank you so much for all your help and
keep up the great job you are doing.

Désolé de l'utilisation de majuscules. Je suis nouvel à tout ce poster sur les conseils de message d'internet. Merci pour toute votre aide. Garder en haut le grand travail! De nouveau merci.

Yaneck