fetch the data from sheet2 & place it in sheet1 based on the locat

[Ren]

hi,
i have two sheets, i want the item to be matched and quantity should be
placed in store1 or store2 of sheet1 based on the location in sheet2
how can i include this inside a For Each loop??
or If else condition


item requiredQty store1 store2
apple 6
apple 7
kiwi 5
guava 10

item store1 store2 total
orange 3 2 5
apple 5 8 13
guava 10 12 22


thanks
ren

 

[Joel]

Sub GetData()

RowCount = 2
With Sheets("Sheet1")
Do While .Range("A" & RowCount) <> ""
NewItem = .Range("A" & RowCount)
With Sheets("Sheet2")
Set c = .Columns("A:A").Find(what:=NewItem, LookIn:=xlValues)
If Not c Is Nothing Then
Store1 = c.Offset(0, 1)
Sheets("Sheet1").Range("C" & RowCount) = Store1
Store2 = c.Offset(0, 2)
Sheets("Sheet1").Range("D" & RowCount) = Store2
End If
End With
RowCount = RowCount + 1
Loop
End With

End Sub

 

[Ren]

Hi Joel, thank you so much for your time,
the code is working, but my challenge is
when the item is found it should match the required quantity(sheet1) with the
total quantity of sheet2.(to check whether enough qty is available)

sheet1ex:apple appears many times)

item requiredqty store1 store2
apple 6 5 1
apple 4 0 4 (when the apple appears next time or
any no of times)

sheet2(ex:apple appears only once)
when the item matched for second time,it should take the quantity from the
balance quantity,and edit the quantity supplied and left in next row as
following

item store1 store2 total qtysupplied balanceleft

apple 5 10 15 6 9
4 5

thank you so much joel
Ren

 

[Ren]

Hi Joel,

i have added 2 more lines of code, it works for the following condition ie
when
sheet1 reqdqty is greater than the totalqty of sheet2
but i want to insert the row when second time the item appears.
and i donno what to write when the sheet1 reqdqty is less than the totalqty
of sheet2
one more thing is if the item is not available in sheet2 i want that control
to go and search in sheet3 (which i have not mentioned)

Sub getdata()

RowCount = 2
With Sheets("Sheet1")
Do While .Range("A" & RowCount) <> ""
newitem = .Range("A" & RowCount)
With Sheets("Sheet2")
Set c = .Columns("A:A").Find(what:=newitem, LookIn:=xlValues)
If c Is Nothing Then
Range("A" & RowCount).Interior.ColorIndex = 4
ElseIf Range("A" & RowCount).Offset(0, 1) > c.Offset(0, 3) Then

store1 = c.Offset(0, 1)
Sheets("Sheet1").Range("C" & RowCount) = store1
Store2 = c.Offset(0, 2)
Sheets("Sheet1").Range("D" & RowCount) = Store2
ElseIf Range("A" & RowCount).Offset(0, 1) < c.Offset(0, 3) Then
........
........'i donno what to write here
........'if the quantity is not enough or the item is not there
then goto sheet3

End If
End With
RowCount = RowCount + 1
Loop
End With
End Sub

 

[Joel]

I made some changes to the code. These changes are not complete because you
didn't give me enough instructions what you wanted done.

Don't understand your remark when you want to insert a new row.

Some how you need to check if quantity has been pulled when an item appears
more than once. You should have new columns on sheet2 indicating the
quantity used.

Sub getdata()

RowCount = 2
With Sheets("Sheet1")
Do While .Range("A" & RowCount) <> ""
newitem = .Range("A" & RowCount)
With Sheets("Sheet2")
Set c = .Columns("A:A").Find(what:=newitem, _
LookIn:=xlValues)
End With

If c Is Nothing Then
QuantNeeded = .Range("B" & RowCount)

Else
Store1 = c.Offset(0, 1)
Store2 = c.Offset(0, 2)
.Range("C" & RowCount) = Store1
.Range("D" & RowCount) = Store2

If .Range("B" & RowCount) >= _
(Store1 + Store2) Then

QuantNeeded = 0
Else
QuantNeeded = .Range("B" & RowCount) - _
(Store1 + Store2)
End If
End If
If QuantNeeded > 0 Then
With Sheets("Sheet3")
Set c = .Columns("A:A").Find(what:=newitem, _
LookIn:=xlValues)
End With
If c Is Nothing Then
.Range("A" & RowCount). _
Interior.ColorIndex = 4
Else
Store1 = c.Offset(0, 1)
Store2 = c.Offset(0, 2)
End If
End If
RowCount = RowCount + 1
Loop
End With
End Sub

 

[Ren]

Hi Joel,
first of all thanks a lot for your time.

let me explain to you what i want to do.
there are 3 sheets
sheet1: (items occurs many times) total 7 columns

No item requiredQty store1 store2 store3 ETA
2 apple 6
5 orange 4
9 kiwi 5
7 guava 3
5 apple 7

sheet2item occurs only one time)total 7 columns
item store1 store2 total qtysupplied balance No
orange 3 2 5
apple 5 8 13
guava 10 12 22

sheet3item occurs only one time) total 6 columns
item ETA store3(total) qtysupplied balance No
guava
apple
kiwi
pineapple

what i have to do is take an item from the sheet1 and search for the item in
sheet2.
if the item is found, check against the required quantity and the quantity
available in column total of sheet2.
supply the required qty to sheet1, if the qty is not enought then go and
search in sheet3.
when we supply the quantity to sheet1 we should check from where we are
supplying the quantity from store1 or store2.
after supplying the reqd qty, if anything left then keep that balance qty in
Balance column.
in sheet2 from where we supply the qty, in that row in the column No, we
have to enter the No(first colm in sheet1) of the item to where we have
supplied.
in sheet3 if the matching is found, we have to chk with the total and along
with the qty we have to take ETA of the item and enter in sheet1.

Note: when the item occurs second time or nth time, it has to go check for
the qty in the column Balance.
and enter the qtysupplied, balance and No in the second row(we have to add a
row)

if you don't mind
please give me your email address so that i can send you the excel sheets
or you can drop a mail to me at
(e-mail address removed)
i have left with two more days to complete this project.

thank you very for your time
Ren