hi
I would sum values for columns SALE,RET for sheets FRU,FES ,FN,SS and put the values in sheet COLLECTION into columns SALE,RET . the matching data in sheets FRU,FES ,FN,SS depend on column B should match with column B,C,D i in sheet COLLECTION .If they are matched then pull values and merge & sum the duplicate items across multiple sheets . with ignore unmatched items .
I have data almost 1400 rows across sheets .
the result should just be in columns E,F
I would sum values for columns SALE,RET for sheets FRU,FES ,FN,SS and put the values in sheet COLLECTION into columns SALE,RET . the matching data in sheets FRU,FES ,FN,SS depend on column B should match with column B,C,D i in sheet COLLECTION .If they are matched then pull values and merge & sum the duplicate items across multiple sheets . with ignore unmatched items .
I have data almost 1400 rows across sheets .
COLLECTION (1).xlsm | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | ITEM | BR | SALE | RET | ||
2 | 1 | VEG TOMATO SS | 12 | - | ||
3 | 2 | VEG TOMATO AA | 12 | 12 | ||
4 | 3 | FR BANANA TT | 200 | 10 | ||
5 | 4 | FR APPLE LL | 100 | 20 | ||
6 | 5 | FR PEAR NN | 10 | - | ||
7 | 6 | FR BANANA QQ | 20 | - | ||
FRU |
COLLECTION (1).xlsm | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | ITEM | BR | SALE | RET | ||
2 | 1 | VEG TOMATO SS | 20 | - | ||
3 | 2 | VEG TOMATO AA | 22 | 12 | ||
4 | 3 | FR BANANA TT | 12 | 10 | ||
5 | 4 | FR APPLE LL | 12 | 2 | ||
6 | 5 | FR BANANA QQ | 12 | - | ||
FEG |
COLLECTION (1).xlsm | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | ITEM | BR | SALE | ||
2 | 1 | FR BANANA QQ | 10 | ||
3 | 2 | VEG TOMATO AA | 20 | ||
4 | 3 | FR BANANA TT | 40 | ||
5 | 4 | FR APPLE LL | 50 | ||
6 | 5 | FR PEAR NN | 5 | ||
FN |
COLLECTION (1).xlsm | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | ITEM | BR | RET | ||
2 | 1 | FR BANANA TT | 5 | ||
3 | 2 | FR APPLE LL | 2 | ||
4 | 3 | FR PEAR NN | 3 | ||
SS |
the result should just be in columns E,F
COLLECTION (1).xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | ITEM | BR | TY | OR | SALE | RET | BALANCE | ||
2 | 1 | FR | BANANA | TT | 252 | 25 | 227 | ||
3 | 2 | FR | APPLE | LL | 162 | 42 | 120 | ||
4 | 3 | FR | PEAR | NN | 15 | 3 | 12 | ||
5 | 4 | FR | BANANA | 42 | 42 | ||||
6 | 5 | VEG | TOMATO | SS | 20 | 20 | |||
7 | 6 | VEG | TOMATO | AA | 42 | 12 | 30 | ||
8 | 7 | VEG | ONION | AA1 | - | ||||
9 | 8 | VEG | ONION | AA2 | - | ||||
10 | 9 | VEG | ONION | AA3 | - | ||||
COLLECTION |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G2:G10 | G2 | =E2-F2 |