I have two worksheets (Sheet1 & Sheet 2) in my file
Sheet 1 has three column (ID, Dept, and rate)
Sheet 2 has three column (ID, Group #, and rate)
so 3 columns on each sheet (Column A, B & C)
On sheet 2, First i want to match "ID on both sheet"
Second, I want to match DEPT. in sheet 1 to match GROUP # on sheet 2
Answer would be the rate. Now that seems simple but here is the trick: 1 dept. number are different than Group #.
For example
Dept. "05" Equals Group # "50-35100" & "50-35400"
Dept 08 = 50-34200; 50-34400 (Group ID#)
010 = 50-33100
012 = 50-34100,50-34300;50-36100 and 50-36200
016 = 50-35200; 50-35300
1 dept. equals 2 or more group #.
Hence, Formula need to total up group # rates to equal one dept. ALSO, It NEED TO MATCH PRODUCT ID.
for example: dept 05 rate is $10
Where as Dept 05 equal Group 50-35100 & 50-35400 where 50-35100 = $4 & 50-35400 $6
so dept. 05 total matches with group $10 =$10
Data from Sheet 1 Below
<tbody>
</tbody>
Data from Sheet 2 below
<tbody>
</tbody>
Sheet 1 has three column (ID, Dept, and rate)
Sheet 2 has three column (ID, Group #, and rate)
so 3 columns on each sheet (Column A, B & C)
On sheet 2, First i want to match "ID on both sheet"
Second, I want to match DEPT. in sheet 1 to match GROUP # on sheet 2
Answer would be the rate. Now that seems simple but here is the trick: 1 dept. number are different than Group #.
For example
Dept. "05" Equals Group # "50-35100" & "50-35400"
Dept 08 = 50-34200; 50-34400 (Group ID#)
010 = 50-33100
012 = 50-34100,50-34300;50-36100 and 50-36200
016 = 50-35200; 50-35300
1 dept. equals 2 or more group #.
Hence, Formula need to total up group # rates to equal one dept. ALSO, It NEED TO MATCH PRODUCT ID.
for example: dept 05 rate is $10
Where as Dept 05 equal Group 50-35100 & 50-35400 where 50-35100 = $4 & 50-35400 $6
so dept. 05 total matches with group $10 =$10
Data from Sheet 1 Below
Product ID | dept. | rate |
10234 | 005 | 4.95 |
10234 | 008 | 6.89 |
10234 | 010 | 86.60 |
10234 | 012 | 2.52 |
10234 | 016 | 6.67 |
10309 | 005 | 5.01 |
10309 | 008 | 3.89 |
10309 | 010 | 94.73 |
10309 | 012 | 13.47 |
10309 | 016 | 6.67 |
10310 | 005 | 4.81 |
10310 | 008 | 4.29 |
10310 | 010 | 100.73 |
10310 | 012 | 8.93 |
10310 | 016 | 6.67 |
10359 | 005 | 5.01 |
10359 | 008 | 3.85 |
10359 | 010 | 111.33 |
10359 | 012 | 11.95 |
10359 | 016 | 6.67 |
<tbody>
</tbody>
Data from Sheet 2 below
Product ID | Group ID | Rate |
010234 | 50-34200 | 5.00 |
010234 | 50-35200 | 7.00 |
010234 | 50-35100 | 2.00 |
010234 | 50-35400 | 3.00 |
010234 | 50-33100 | 12.00 |
010234 | 50-33100 | 5.00 |
010234 | 50-33100 | 11.00 |
010234 | 50-33100 | 4.00 |
010234 | 50-33100 | 40.00 |
010234 | 50-33100 | 10.00 |
010234 | 50-33100 | 4.00 |
010234 | 50-34100 | 0.75 |
010234 | 50-34100 | 0.75 |
010234 | 50-34100 | 0.48 |
010234 | 50-34100 | 0.53 |
010234 | 50-34400 | 0.00 |
010234 | 50-34300 | 0.00 |
<tbody>
</tbody>