SUMPRODUCT DUPLICATE SUBTRACTION - don't count duplicates

This is a discussion on SUMPRODUCT DUPLICATE SUBTRACTION - don't count duplicates within the Excel Questions forums, part of the Question Forums category; Is there anyway to shorten the formula in F4 through F9 or have I got it about a short as ...

1. SUMPRODUCT DUPLICATE SUBTRACTION - don't count duplicates

Is there anyway to shorten the formula in F4 through F9 or have I got it about a short as you can get for what I'm trying to do?

=IF(F5=F4,SUMPRODUCT((A\$4:A\$9=\$G\$3)+0,(B\$4:B\$9=F4)+0,C\$4:C\$9)/2,

IF(AND(F3=F4,F4<>F5),SUMPRODUCT((A\$4:A\$9=\$G\$3)+0,(B\$4:B\$9=F4)+0,C\$4:C\$9)/2,

SUMPRODUCT((A\$4:A\$9=\$G\$3)+0,(B\$4:B\$9=F4)+0,C\$4:C\$9)))

******** ******************** ************************************************************************>
 Microsoft Excel - Book1 ___Running: xl2002 XP : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 G4G5G6G7G8G9 =

A
B
C
D
E
F
G
H
1
********
2
Data*Main*Table****Result*Table**
3
UNITCODEMAN**UNITUnit1*
4
Unit1AAA113**AAA113*
5
UNit1AAA113**AAA113*
6
Unit1AAA221**AAA221*
7
Unit1CCC331**CCC331*
8
Unit1DDD442**DDD442*
9
Unit1DDD442**DDD442*
10
********
 Sheet1 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

2. Re: SUMPRODUCT DUPLICATE SUBTRACTION - don't count duplicate

It's hard to understand what your question is. The best thing to do is to strip off the formulas and specify what musst be computed along with the desired outcome(s).

3. Re: SUMPRODUCT DUPLICATE SUBTRACTION - don't count duplicate

Aladin - On a UNIT summary sheet I'm trying to list the CODE and MANNING number associated with a UNIT from the Main Data Table.

I have the CODES listed for each UNIT in a column and want to have a formula that lists the MANNING number associated with each UNIT and CODE.

As you can see there are duplicate CODES, so this causes the MANNING numbers to be summed instead of listed, but I need then listed seperately for this application. My IF SUMPRODUCT formula up does not work either because it only works if there are an "even" number of CODES...

I have given up on returning the 'separate' CODE for each UNIT (evidently it can't be done) but would like to be able to get the MANNING number for each CODE.

If the *1MNC and *2MNC (L9 and L10)requires a very expensive formula I can put these in manually there are only 6 of them. MNC = manning not counted

******** ******************** ************************************************************************>
 Microsoft Excel - AFWUS_DTS.xls ___Running: xl2002 XP : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 F6I6F7I7F8I8F9I9F10I10F11I11 =

A
B
C
D
E
F
G
H
I
J
K
L
M
1
*************
2
****Reqular*SUMPRODUCT**"IF"*SUMPRODUCT**Would*like*something*like*this*
3
Main*Data*Table*Summary*Table*Summary*Table**
4
UNITCODEMAN*UNIT=Unit1*UNIT=Unit1*UNIT=Unit1*
5
****CODEMAN*CODEMAN*CODEMAN*
6
Unit1AAA113*AAA119*AAA114.5*AAA113*
7
UNit1AAA113*AAA119*AAA114.5*AAA113*
8
UNit1AAA113*AAA119*AAA114.5*AAA113*
9
Unit1AAA22*1*AAA220*AAA220*AAA22*1*MNC*
10
Unit1DDD44*2*DDD440*DDD440*DDD44*2*MNC*
11
Unit2DDD442**0**0****
12
*************
 Sheet1 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

4. Re: SUMPRODUCT DUPLICATE SUBTRACTION - don't count duplicate

Darn it. Why do you keep posting formulas to convey what you want? I asked you to formulate your problem in regular English. Hope you want to take notice of the request.

5. Re: SUMPRODUCT DUPLICATE SUBTRACTION - don't count duplicate

Main Data Sheet

1. In col A10:A120 I have codes such as AAA11, 11BBB, 1CCC3. There are duplicates.

2. In col D10:D120 I have 9 digit unit names, 0111ASDFG, 0222ZXCVB. There are duplicates.

3. In col F10:F120 I have manning numbers for the Code. **Note - SOME of these numbers ar prefixed with an *.

Unit summary sheets (where I want the result).

1. Unit name is in cell D12. I want the formula to reference D12.

2. In col D18:D30 I would like a formula, that can be copied down this range, that will list the Codes that are associated with the Unit in D12. I want each code listed seperately. I other words if there are three AAA11s I want them listed serately in cells D18, D19, and D20.

3. In col E18:E30 I would like the Manning numbers that is associated with the Unit in D12 and the Code (now in range D18:D30). So if AAA11 had a number of 25, the 25 would be in E18, E19, and E20.

6. Re: SUMPRODUCT DUPLICATE SUBTRACTION - don't count duplicate

Originally Posted by em
Main Data Sheet

1. In col A10:A120 I have codes such as AAA11, 11BBB, 1CCC3. There are duplicates.

2. In col D10:D120 I have 9 digit unit names, 0111ASDFG, 0222ZXCVB. There are duplicates.

3. In col F10:F120 I have manning numbers for the Code. **Note - SOME of these numbers ar prefixed with an *.

Unit summary sheets (where I want the result).

1. Unit name is in cell D12. I want the formula to reference D12.

2. In col D18:D30 I would like a formula, that can be copied down this range, that will list the Codes that are associated with the Unit in D12. I want each code listed seperately. I other words if there are three AAA11s I want them listed serately in cells D18, D19, and D20.

3. In col E18:E30 I would like the Manning numbers that is associated with the Unit in D12 and the Code (now in range D18:D30). So if AAA11 had a number of 25, the 25 would be in E18, E19, and E20.
Much better.

Given the data...

******** ******************** ************************************************************************>
 Microsoft Excel - em 1.xls ___Running: xl2000 : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 A8 =

A
B
C
D
E
F
G
8
*******
9
CODE**UNIT*MAN*
10
AAA11**Unit1*25*
11
AAA11**UNit1*30*
12
AAA11**UNit1*18*
13
AAA22**Unit1**1*
14
DDD44**Unit1**2*
15
DDD44**Unit2*22*
16
*******
 Data *

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Continued on next page...

7. Re: SUMPRODUCT DUPLICATE SUBTRACTION - don't count duplicate

Continued from the previous page...

Insert a new worksheet and name this Admin.

In A2:A3 enter:

{"Last Row in Data";"Start row in UnitSummary"}

In B2 enter:

=MATCH(REPT("z",255),Data!A:A)

In B3 enter:

=CELL("Row",UnitSummary!D12)

as in:

******** ******************** ************************************************************************>
 Microsoft Excel - em 1.xls ___Running: xl2000 : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 B2B3 =

A
B
C
D
1
****
2
Last*Row*in*Data15**
3
Start*row*in*UnitSummary12**
4
****

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Activate UnitSummary.

In D12 enter: Unit1 (a unit of interest)

In E12 enter:

=COUNTIF(INDEX(LTable,0,4),D12)

In D18 enter:

=IF(\$E\$12,INDEX(LTable,MATCH(\$D\$12,INDEX(LTable,0,4),0),1),"Does Not Exist")

In E18 enter:

=IF(D18<>"",INDEX(LTable,MATCH(\$D\$12,INDEX(LTable,0,4),0),6),"")

F18 houses an alternative to E18:

=IF(D18<>"",IF(LEFT(SETV(INDEX(LTable,MATCH(\$D\$12,INDEX(LTable,0,4),0),6)))="*",GETV()&" MNC",GETV()),"")

In D19 enter:

=IF(\$E\$12>COUNTA(\$D\$18:D18),INDEX(LTable,MATCH(\$D\$12,INDEX(LTable,0,4),0)+COUNTA(\$D\$18:D18),1),"")

In E19 enter:

=IF(D19<>"",INDEX(LTable,MATCH(\$D\$12,INDEX(LTable,0,4),0)+COUNTA(\$D\$18:D18),6),"")

F19 houses an alternative to E19:

=IF(D19<>"",IF(LEFT(SETV(INDEX(LTable,MATCH(\$D\$12,INDEX(LTable,0,4),0)+COUNTA(\$D\$18:D18),6)))="*",GETV()&" MNC",GETV()),"")

Select D19:F19 and copy down as far as needed.

******** ******************** ************************************************************************>
 Microsoft Excel - em 1.xls ___Running: xl2000 : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 E12D18E18F18D19E19F19D20E20F20D21E21F21D22E22F22D23E23F23D24E24F24D25E25F25D26E26F26D27E27 =

C
D
E
F
G
11
*****
12
*Unit15**
13
*****
14
*****
15
**AlternativeAlternative*
16
**12*
17
*CODEMANMAN*
18
*AAA112525*
19
*AAA113030*
20
*AAA111818*
21
*AAA22*1*1*MNC*
22
*DDD44*2*2*MNC*
23
**
*
*
*
24
**
*
*
*
25
**
*
*
*
26
**
*
*
*
27
**
*
**
 UnitSummary *

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Caveat. The data on the Data sheet must be sorted on the UNIT column.

8. Re: SUMPRODUCT DUPLICATE SUBTRACTION - don't count duplicate

Forgot to add the definition of LTable:

Activate Insert|Name|Define.
Enter LTable as name in the Names in Workbook box.
Enter the following in the Refers to box:

Click OK.

9. Re: SUMPRODUCT DUPLICATE SUBTRACTION - don't count duplicate

Aladin - thanks, will give try later. One, the Main Data sheet is sorted on Code, I think I can rework the formula, if not I will look at re-sorting Main Data sheet...

10. Re: SUMPRODUCT DUPLICATE SUBTRACTION - don't count duplicate

Aladin - OK, got everything working in test book.

Before I spend to much time modifying formulas--is it even possible to make these formulas work off a sorted col A (codes col)--everything else stays just like it is??

Page 1 of 3 123 Last

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•