Linking values from another sheet to another and adding them together and getting rid of duplicate values

[psmi123]

There are 30 functions and each one is tied to revenue. So I know how much revenue is tied to each function.

Functions	Revenue
1A	$24.00
2A	$54.00
3A	$36.00
5A	$23.00
8A	$25.00
9A	$42.00
11A	$35.00
13A	$75.00
1B	$79.00
2B	$6.00
3B	$80.00
5B	$7.00
6B	$46.00
8B	$45.00
1C	$26.00
2C	$84.00
3C	$58.00
4C	$25.00
6C	$13.00
8C	$25.00
1D	$47.00
2D	$39.00
3D	$59.00
3E	$23.00
4E	$15.00
5E	$62.00
6E	$72.00
7E	$54.00
8E	$11.00

<tbody>
</tbody>

I want to find the revenue of each sector, the sectors are made of solutions and the solutions are made of the functions. So i need to find out an approach on how to count the revenue per sector but i dont want it counting revenue for the same function more than once because there are solutions that share the same function and i dont want them being counted twice.

Sector	Sector Solution	Functions	Functions	Functions	Revenue
Auto	Sedan	1A	2A	3A
Auto	Crossover	1A	5A	2A
Auto	Bike	2A	8A	9A
Auto	Coupe	3A	11A	2A
Auto	Supercar	13A	2A	3A
Aero	Jets	1B	2B	3B
Aero	Boat Plane	2B	5B	6B
Aero	Small Planes	3B	8B	2B
Energy	Wind	1C	2C	3C
Energy	Water	4C	1C	6C
Energy	Solar	1C	8C	4C
Gas	Mining	1D	2D	3D
Technologies	Blockchain	5E	7E	3E
Technologies	IoT	4E	5E	6E
Technologies	5G	7E	8E	5E

<tbody>
</tbody>

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​	F​	G​
1​	Functions	Revenue
2​	1A	$24.00​
3​	2A	$54.00​
4​	3A	$36.00​
5​	5A	$23.00​
6​	8A	$25.00​
7​	9A	$42.00​
8​	11A	$35.00​
9​	13A	$75.00​
10​	1B	$79.00​
11​	2B	$6.00​
12​	3B	$80.00​
13​	5B	$7.00​
14​	6B	$46.00​
15​	8B	$45.00​
16​	1C	$26.00​
17​	2C	$84.00​
18​	3C	$58.00​
19​	4C	$25.00​
20​	6C	$13.00​
21​	8C	$25.00​
22​	1D	$47.00​
23​	2D	$39.00​
24​	3D	$59.00​
25​	3E	$23.00​
26​	4E	$15.00​
27​	5E	$62.00​
28​	6E	$72.00​
29​	7E	$54.00​
30​	8E	$11.00​
31​
32​
33​
34​	Sector	Sector Solution	Functions	Functions	Functions	Revenue
35​	Auto	Sedan	1A	2A	3A	114​
36​	Auto	Crossover	1A	5A	2A	101​
37​	Auto	Bike	2A	8A	9A	121​
38​	Auto	Coupe	3A	11A	2A	125​
39​	Auto	Supercar	13A	2A	3A	165​
40​	Aero	Jets	1B	2B	3B	165​
41​	Aero	Boat Plane	2B	5B	6B	59​
42​	Aero	Small Planes	3B	8B	2B	131​
43​	Energy	Wind	1C	2C	3C	168​
44​	Energy	Water	4C	1C	6C	64​
45​	Energy	Solar	1C	8C	4C	76​
46​	Gas	Mining	1D	2D	3D	145​
47​	Technologies	Blockchain	5E	7E	3E	139​
48​	Technologies	IoT	4E	5E	6E	149​
49​	Technologies	5G	7E	8E	5E	127​

<tbody>
</tbody>

F35=SUMPRODUCT(SUMIF($A$2:$A$30,C35:E35,$B$2:$B$30)) copy down

 

[psmi123]

how would i format it if i dont want it counting duplicates? For example every sector uses function 2A, meaning the revenue from 2A should be split to each sector. I dont want the revenue being counted more than once. Is there a way to do that?

 

[MickG]

With your first Table in columns "A & B" and the second Table in columns "D to I", then try this for results in column "I".

[COLOR="Navy"]Sub[/COLOR] MG11Jul28
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Tot [COLOR="Navy"]As[/COLOR] Double, ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
[COLOR="Navy"]Set[/COLOR] dic = CreateObject("scripting.dictionary")
    dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng: .Item(Dn.Value) = Dn.Offset(, 1): [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("F2", Range("F" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] ac = 0 To 2
        dic(Dn.Offset(, ac).Value) = dic(Dn.Offset(, ac).Value) + 1
    [COLOR="Navy"]Next[/COLOR] ac
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] ac = 0 To 2
        [COLOR="Navy"]If[/COLOR] .exists(Dn.Offset(, ac).Value) [COLOR="Navy"]Then[/COLOR]
            Tot = Tot + .Item(Dn.Offset(, ac).Value) / dic(Dn.Offset(, ac).Value)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] ac
        Dn.Offset(, 3).Value = Format(Tot, "$0.00"): Tot = 0
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[psmi123]

Thank you so much! So where are the variables I can change if I need to use a bigger table. Also is there a way to do this on a different page?

 

[MickG]

Try this:-
NB:- The First table is in sheet1 Columns "A & B".
The second Table is in sheet2 starting "A1"
The code should take care of the number of columns and rows.
NB:- The "Revenue" Column in sheet2 (Last Column) must have the Word "Revenue" as the header.

[COLOR=navy]Sub[/COLOR] MG11Jul59
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Tot [COLOR=navy]As[/COLOR] Double, Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] dic [COLOR=navy]As[/COLOR] Object

'[COLOR=green][B]First table "Sheet1" columns "A & B".[/B][/COLOR]
[COLOR=navy]With[/COLOR] Sheets("Sheet1")
    [COLOR=navy]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]End[/COLOR] With
[COLOR=navy]
With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
[COLOR=navy]Set[/COLOR] dic = CreateObject("scripting.dictionary")
    dic.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng: .Item(Dn.Value) = Dn.Offset(, 1): [COLOR=navy]Next[/COLOR]

'[COLOR=green][B]Second table on sheet 2[/B][/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Sheets("Sheet2").Range("A1").CurrentRegion
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    dic(Dn.Offset(, Ac).Value) = dic(Dn.Offset(, Ac).Value) + 1
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]
For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng.Columns(3).Cells
   [COLOR=navy]If[/COLOR] Not Dn.Row = 1 [COLOR=navy]Then[/COLOR]
    [COLOR=navy]For[/COLOR] Ac = 0 To Rng.Columns.Count - 1
        [COLOR=navy]If[/COLOR] .exists(Dn.Offset(, Ac).Value) [COLOR=navy]Then[/COLOR]
            Tot = Tot + .Item(Dn.Offset(, Ac).Value) / dic(Dn.Offset(, Ac).Value)
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] Ac
        Dn.Offset(, Rng.Columns.Count - 3).Value = Format(Tot, "$0.00"): Tot = 0
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick