SUMPRODUCT DUPLICATE SUBTRACTION - don't count duplicates

[itr674]

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	UNIT	CODE	MAN	*	*	UNIT	Unit1	*
4	Unit1	AAA11	3	*	*	AAA11	3	*
5	UNit1	AAA11	3	*	*	AAA11	3	*
6	Unit1	AAA22	1	*	*	AAA22	1	*
7	Unit1	CCC33	1	*	*	CCC33	1	*
8	Unit1	DDD44	2	*	*	DDD44	2	*
9	Unit1	DDD44	2	*	*	DDD44	2	*
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.

[Aladin Akyurek]

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).

[itr674]

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

UNIT

CODE

MAN

*

UNIT=

Unit1

*

UNIT=

Unit1

*

UNIT=

Unit1

*

5

*

*

*

*

CODE

MAN

*

CODE

MAN

*

CODE

MAN

*

6

Unit1

AAA11

3

*

AAA11

9

*

AAA11

4.5

*

AAA11

3

*

7

UNit1

AAA11

3

*

AAA11

9

*

AAA11

4.5

*

AAA11

3

*

8

UNit1

AAA11

3

*

AAA11

9

*

AAA11

4.5

*

AAA11

3

*

9

Unit1

AAA22

*1

*

AAA22

0

*

AAA22

0

*

AAA22

*1*MNC

*

10

Unit1

DDD44

*2

*

DDD44

0

*

DDD44

0

*

DDD44

*2*MNC

*

11

Unit2

DDD44

2

*

*

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.

[Aladin Akyurek]

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.

[itr674]

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.

[Aladin Akyurek]

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...

[Aladin Akyurek]

Continued from the previous page...

Insert a new worksheet and name this Admin.

Activate 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*Data	15	*	*
3	Start*row*in*UnitSummary	12	*	*
4	*	*	*	*
Admin *

[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()),"")

Formulas in F requires themorefunc.xlladd-in.

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	*	Unit1	5	*	*
13	*	*	*	*	*
14	*	*	*	*	*
15	*	*	Alternative	Alternative	*
16	*	*	1	2	*
17	*	CODE	MAN	MAN	*
18	*	AAA11	25	25	*
19	*	AAA11	30	30	*
20	*	AAA11	18	18	*
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.

[Aladin Akyurek]

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:

=Data!$A$10:INDEX(Data!$F:$F,Admin!$B$2)

Click OK.

[itr674]

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...

[itr674]

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??