| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find the matching and supply the quantity
hi,anyone can help me pl
sheet1(polist) Number item reqd qty store1 store2 FAB ETA 45594010 Apple 170092 45600810 Apple 331068 45613110 orange 294954 sheet2(slrs) item store1 store2 total bal allocation qty Apple 339173 339173 orange 20 3000 3020 sheet3(fab) item ETA Total bal allocation qty Apple 20071224 113703 20080104 1279155 orange 20080101 159942 20080104 90876 20080119 272629 after the allocation,the sheets will look like this sheet1(polist) Number item reqd qty store1 store2 FAB ETA 45594010 Apple 170092 170092 45600810 Apple 331068 169081 113703 20071224 48285 20080104 45613110 orange 294954 20 3000 159942 20080101 90876 20080104 41136 20080119 sheet3(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 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 column G¦..ETA sheet2 SLRS column A¦..ITEM column B¦..store1 column C¦..store2 column D¦..total column E¦..balance column F¦..allocation column G¦..quantity sheet3 FAB column A¦..ITEM column B¦..ETA (its nothing but the date of arrival) 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 the rows more 150 to 200,i need to match for all these rows with multiple number of rows in sheet2 and sheet3 and it changes everyday.it takes more than one day to do this work for me. any help highly appreciated.i am very nocice to this VBA macro Ren |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find the matching and supply the quantity
this is the code i got and it will compare sheet1 and sheet2,
but i want the control to go through sheet1 and then to sheet2. and the item apple will appear many items,so when the item appears second time,it should automatically to the next row to enter the quantity. Sub allocation() With Sheets("polist") Sh1LastRow = .Cells(Rows.Count, "B").End(xlUp).Row Set Sh1Range = .Range("B2:B" & Sh1LastRow) End With With Sheets("slrs") Sh2LastRow = .Cells(Rows.Count, "A").End(xlUp).Row Set Sh2Range = .Range("A2:A" & Sh2LastRow) End With With Sheets("FAB") Sh3LastRow = .Cells(Rows.Count, "A").End(xlUp).Row Set Sh3Range = .Range("A2:A" & Sh3LastRow) End With For Each sh1cell In Sh1Range Set c = Sh2Range.Find( _ what:=sh1cell, LookIn:=xlValues) If c Is Nothing Then sh1cell.Interior.ColorIndex = 4 sh1cell.Offset(0, 1).Interior.ColorIndex = 4 Else If sh1cell.Offset(0, 1) < c.Offset(0, 3) Then sh1cell.Offset(0, 2).Value = sh1cell.Offset(0, 1) c.Offset(0, 6).Value = sh1cell.Offset(0, 1) c.Offset(0, 4).FormulaR1C1 = "=RC[-1]-RC[2]" c.Offset(0, 5).Value = sh1cell.Offset(0, -1) Else If sh1cell.Offset(0, 2) c.Offset(0, 3) Then sh1cell.Offset(0, 2).Value = c.Offset(0, 3) c.Offset(0, 6).Value = sh1cell.Offset(0, 1) c.Offset(0, 4).FormulaR1C1 = "=RC[-1]-RC[2]" c.Offset(0, 5).Value = sh1cell.Offset(0, -1) End If End If End If Next sh1cell End Sub "Ren" wrote: hi,anyone can help me pl sheet1(polist) Number item reqd qty store1 store2 FAB ETA 45594010 Apple 170092 45600810 Apple 331068 45613110 orange 294954 sheet2(slrs) item store1 store2 total bal allocation qty Apple 339173 339173 orange 20 3000 3020 sheet3(fab) item ETA Total bal allocation qty Apple 20071224 113703 20080104 1279155 orange 20080101 159942 20080104 90876 20080119 272629 after the allocation,the sheets will look like this sheet1(polist) Number item reqd qty store1 store2 FAB ETA 45594010 Apple 170092 170092 45600810 Apple 331068 169081 113703 20071224 48285 20080104 45613110 orange 294954 20 3000 159942 20080101 90876 20080104 41136 20080119 sheet3(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 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 column G¦..ETA sheet2 SLRS column A¦..ITEM column B¦..store1 column C¦..store2 column D¦..total column E¦..balance column F¦..allocation column G¦..quantity sheet3 FAB column A¦..ITEM column B¦..ETA (its nothing but the date of arrival) 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 the rows more 150 to 200,i need to match for all these rows with multiple number of rows in sheet2 and sheet3 and it changes everyday.it takes more than one day to do this work for me. any help highly appreciated.i am very nocice to this VBA macro Ren |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do I find quantity between some dates? | Excel Discussion (Misc queries) | |||
| Need a formula to increase quantity of one cell until the quantity ofanother is exceeded | Excel Discussion (Misc queries) | |||
| supply chart with names on price or quantity | Charts and Charting in Excel | |||
| find the item and supply the required quantity | Excel Programming | |||
| find the matching and allocate the required quantity | Excel Programming | |||
Linear Mode
