abdo meghari
Well-known Member
- Joined
- Aug 3, 2021
- Messages
- 531
- Office Version
- 2019
Hello
I would populate data in RESULT sheet based on arranged data in PR sheet . should brings data columns D,E from PR sheet and brings QTY from STOCK sheet and put to adjacent cell for brought QTY from PR sheet based on matching BRAND column between two sheets.
I have about 3000 items for each sheet .
this is simple data
should be
I would populate data in RESULT sheet based on arranged data in PR sheet . should brings data columns D,E from PR sheet and brings QTY from STOCK sheet and put to adjacent cell for brought QTY from PR sheet based on matching BRAND column between two sheets.
I have about 3000 items for each sheet .
this is simple data
av.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | DATE | CLIENT NO | INVOICE NO | BRAND | QTY | UNIT PRICE | TOTAL | ||
2 | 01/05/2021 | CUS-BS-1 | INV-BS-1 | 1200R20 G580 JAP | 1,000.00 | 2,500.00 | 2,500,000.00 | ||
3 | 01/06/2021 | CUS-BS-1 | INV-BS-1 | 1200R20 G580 THI | 10.00 | 2,660.00 | 26,600.00 | ||
4 | 01/08/2021 | CUS-BS-1 | INV-BS-1 | 1200R20 R187 THI | 10.00 | 2,450.00 | 24,500.00 | ||
5 | 01/09/2021 | CUS-BS-2 | INV-BS-2 | 1200R24 G580 JAP | 5.00 | 3,000.00 | 15,000.00 | ||
6 | 01/12/2021 | CUS-BS-2 | INV-BS-2 | 1400R20 VSJ JAP | 12.00 | 4,000.00 | 48,000.00 | ||
7 | 02/12/2021 | CUS-BS-3 | INV-BS-3 | 1400R20 R180 JAP | 11.00 | 4,000.00 | 44,000.00 | ||
PR |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G2:G7 | G2 | =E2*F2 |
av.xlsm | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | ITEM | BRAND | REF NO | BATCH | QTY | ||
2 | 1 | 1200R20 R187 THI | REF D#LL | BVGT602 | 4,000.00 | ||
3 | 2 | 1400R20 VSJ JAP | REF D#SS | BVGT604 | 1,000.00 | ||
4 | 3 | 1200R24 G580 JAP | REF D#KK | BVGT603 | 5,000.00 | ||
5 | 4 | 1200R20 G580 JAP | REF D#NN | BVGT600 | 2,000.00 | ||
6 | 5 | 1200R20 G580 THI | REF D#MM | BVGT601 | 3,000.00 | ||
7 | 6 | 1200R24 G580 THI | REF D#KK | BVGT603 | 5,000.00 | ||
STOCK |
should be
av.xlsm | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | ITEM | BRAND | QTY | QTY1 | ||
2 | 1 | 1200R20 G580 JAP | 1,000.00 | 2,000.00 | ||
3 | 2 | 1200R20 G580 THI | 10.00 | 3,000.00 | ||
4 | 3 | 1200R20 R187 THI | 10.00 | 4,000.00 | ||
5 | 4 | 1200R24 G580 JAP | 5.00 | 5,000.00 | ||
6 | 5 | 1400R20 VSJ JAP | 12.00 | 1,000.00 | ||
7 | 6 | 1400R20 R180 JAP | 11.00 | 0.00 | ||
8 | 7 | 1200R24 G580 THI | 0.00 | 5,000.00 | ||
RESULT |
Last edited: