|
find the item and supply the required quantity
hi this is the repost (simplified)
can anyone help me please, i am new to VBA I have 3 sheets (pl see at the end) Sheet1 LIST (n number of rows,keeps changing) Sheet2 slrs Sheet3 fab Sheet1 LIST column A¦..Number column B¦..ITEM column C¦..Required Quantity column D¦..store1(place the value here from store1 of SLRS) column E¦..store2 (place the value here from store2 of SLRS) column F¦..FAB (place the value here from FAB) column G¦..ETA (ETA date from FAB) sheet2 SLRS column A¦..ITEM column B¦..store1 column C¦..store2 column D¦..total column E¦..balance (first time bal=total-qty,next bal=bal-qty) column F¦..allocation column G¦..quantity (whatever you supplied to the polist) sheet3 FAB column A¦..ITEM column B¦..ETA column C¦..Total column D¦..balance column E¦..allocation column F¦. quantity take the item in clm B of POLIST and find whether we have the matching in slrs sheet if there is a matching then take the corresponding value and place it in "LIST" column D(store1) or E(store2)( based on the slrs-location ie store1 or store2),check the quantity is sufficient enough to supply the columnC ie required quantity, If not goto Fab and search for the matching device,if u find the matching take the value along with the ETA If there is no matching in both sheets,then leave it empty if qty is not enough write NA In both the cases we have to take the corresponding NUMBER and place it in allocation column. The quantity you supplied in quantity column,and the balance quantity left. And repeat the process for all the devices. The items will appear many no of times. Note: some items have the matching more than one ie if u dont find apple or the sufficient qty is not there then look for pineapple sheet "polist" Number item reqd qty store1 store2 FAB ETA 45594010 Apple 170092 170092 45600810 Apple 331068 169081 113703 20071224 48285 20080104 45613110 orange 294954 3000 159942 20080101 90876 20080104 41136 20080119 sheet"slrs" item store1 store2 total bal allocation qty Apple 339173 339173 169081 45594010 170092 0 45600810 169081 orange 3000 3000 0 45613110 3000 sheet"fab" item ETA Total bal allocation qty Apple 20071224 113703 0 45600810 113703 20080104 1279155 1230870 45600810 48285 orange 20080101 159942 0 45613110 159942 20080104 90876 0 45613110 90876 20080119 272629 231493 45613110 41136 thanks for any help Ren |
find the item and supply the required quantity
You should be able to perform your tasks without using any code.
Use Excel's help and search for worksheet functions 'IF' and 'VLOOKUP' If I understand you problem, these two should be all you need. "Ren" wrote: hi this is the repost (simplified) can anyone help me please, i am new to VBA I have 3 sheets (pl see at the end) Sheet1 LIST (n number of rows,keeps changing) Sheet2 slrs Sheet3 fab Sheet1 LIST column A¦..Number column B¦..ITEM column C¦..Required Quantity column D¦..store1(place the value here from store1 of SLRS) column E¦..store2 (place the value here from store2 of SLRS) column F¦..FAB (place the value here from FAB) column G¦..ETA (ETA date from FAB) sheet2 SLRS column A¦..ITEM column B¦..store1 column C¦..store2 column D¦..total column E¦..balance (first time bal=total-qty,next bal=bal-qty) column F¦..allocation column G¦..quantity (whatever you supplied to the polist) sheet3 FAB column A¦..ITEM column B¦..ETA column C¦..Total column D¦..balance column E¦..allocation column F¦. quantity take the item in clm B of POLIST and find whether we have the matching in slrs sheet if there is a matching then take the corresponding value and place it in "LIST" column D(store1) or E(store2)( based on the slrs-location ie store1 or store2),check the quantity is sufficient enough to supply the columnC ie required quantity, If not goto Fab and search for the matching device,if u find the matching take the value along with the ETA If there is no matching in both sheets,then leave it empty if qty is not enough write NA In both the cases we have to take the corresponding NUMBER and place it in allocation column. The quantity you supplied in quantity column,and the balance quantity left. And repeat the process for all the devices. The items will appear many no of times. Note: some items have the matching more than one ie if u dont find apple or the sufficient qty is not there then look for pineapple sheet "polist" Number item reqd qty store1 store2 FAB ETA 45594010 Apple 170092 170092 45600810 Apple 331068 169081 113703 20071224 48285 20080104 45613110 orange 294954 3000 159942 20080101 90876 20080104 41136 20080119 sheet"slrs" item store1 store2 total bal allocation qty Apple 339173 339173 169081 45594010 170092 0 45600810 169081 orange 3000 3000 0 45613110 3000 sheet"fab" item ETA Total bal allocation qty Apple 20071224 113703 0 45600810 113703 20080104 1279155 1230870 45600810 48285 orange 20080101 159942 0 45613110 159942 20080104 90876 0 45613110 90876 20080119 272629 231493 45613110 41136 thanks for any help Ren |
find the item and supply the required quantity
Sorry, but it sounds too complicates. I don't think I can help you.
"Ren" wrote: Hi TWR thanks for your reply atleast someone replied.but no thanks. VLOOKUP is not enough. if u look at my sheets,i have 3 sheets,the control should find out the exact matching and i have some more columns which i have not attached.i have clms from A to M,for the easy understanding i have given only few clms. and the data keeps changing everyday, if u look at before and after sheets,i have to fillup 4clms in first sheets and 3clms each in second and third sheets. as i am new to VBA,i donno how to pass that control systamatically.and i don't have time to go thru from the scratch to learn and apply.that's why i am asking the help. pl help me in declaring the variables and writing the macro. thanks a lot for your time Ren "TWR" wrote: You should be able to perform your tasks without using any code. Use Excel's help and search for worksheet functions 'IF' and 'VLOOKUP' If I understand you problem, these two should be all you need. "Ren" wrote: hi this is the repost (simplified) can anyone help me please, i am new to VBA I have 3 sheets (pl see at the end) Sheet1 LIST (n number of rows,keeps changing) Sheet2 slrs Sheet3 fab Sheet1 LIST column A¦..Number column B¦..ITEM column C¦..Required Quantity column D¦..store1(place the value here from store1 of SLRS) column E¦..store2 (place the value here from store2 of SLRS) column F¦..FAB (place the value here from FAB) column G¦..ETA (ETA date from FAB) sheet2 SLRS column A¦..ITEM column B¦..store1 column C¦..store2 column D¦..total column E¦..balance (first time bal=total-qty,next bal=bal-qty) column F¦..allocation column G¦..quantity (whatever you supplied to the polist) sheet3 FAB column A¦..ITEM column B¦..ETA column C¦..Total column D¦..balance column E¦..allocation column F¦. quantity take the item in clm B of POLIST and find whether we have the matching in slrs sheet if there is a matching then take the corresponding value and place it in "LIST" column D(store1) or E(store2)( based on the slrs-location ie store1 or store2),check the quantity is sufficient enough to supply the columnC ie required quantity, If not goto Fab and search for the matching device,if u find the matching take the value along with the ETA If there is no matching in both sheets,then leave it empty if qty is not enough write NA In both the cases we have to take the corresponding NUMBER and place it in allocation column. The quantity you supplied in quantity column,and the balance quantity left. And repeat the process for all the devices. The items will appear many no of times. Note: some items have the matching more than one ie if u dont find apple or the sufficient qty is not there then look for pineapple sheet "polist" Number item reqd qty store1 store2 FAB ETA 45594010 Apple 170092 170092 45600810 Apple 331068 169081 113703 20071224 48285 20080104 45613110 orange 294954 3000 159942 20080101 90876 20080104 41136 20080119 sheet"slrs" item store1 store2 total bal allocation qty Apple 339173 339173 169081 45594010 170092 0 45600810 169081 orange 3000 3000 0 45613110 3000 sheet"fab" item ETA Total bal allocation qty Apple 20071224 113703 0 45600810 113703 20080104 1279155 1230870 45600810 48285 orange 20080101 159942 0 45613110 159942 20080104 90876 0 45613110 90876 20080119 272629 231493 45613110 41136 thanks for any help Ren |
| All times are GMT +1. The time now is 05:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com