lookup names in sheet 1 (Jan 2021) from sheet 2 (Data) of only such persons who have sales greater than zero

[immy]

How can I lookup names in sheet 1 (Jan 2021) from sheet 2 (Data) of only such persons who have sales greater than zero and update the sales of other persons who are already exist in sheet 1 (Jan 2021). The persons who sales 0 should not be appeared in Sheet 1 (Jan 2021). Kindly advice.

Sheet 1				Sheet 2
Jan-21				Data
Name	Sales			Name	Sales
Kashif	100​			Aiman	0​
Shahid	50​			Ali	0​
Asif	70​			Kashif	150​
				Amir	50​
				Qazi	40​
				Asif	70​
				Shahid	50​
				Raza	100​

 

[Takae]

Hope this helps.

Sub test()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LR As Long

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

Application.ScreenUpdating = False
ws1.Range(ws1.Range("A3"), ws1.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1)).ClearContents
With ws2
    LR = .Cells(Rows.Count, 1).End(xlUp).Row
    .Range("A2").AutoFilter field:=2, Criteria1:=">0"
    If WorksheetFunction.Subtotal(3, .Range("B:B")) > 1 Then
        .Range(.Range("A3"), .Cells(LR, 2)).Copy ws1.Range("A3")
    End If
    .Range("A2").AutoFilter
End With
MsgBox "Done"
Application.ScreenUpdating = True
End Sub

 

[immy]

Hope this helps.

Sub test()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LR As Long

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

Application.ScreenUpdating = False
ws1.Range(ws1.Range("A3"), ws1.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1)).ClearContents
With ws2
    LR = .Cells(Rows.Count, 1).End(xlUp).Row
    .Range("A2").AutoFilter field:=2, Criteria1:=">0"
    If WorksheetFunction.Subtotal(3, .Range("B:B")) > 1 Then
        .Range(.Range("A3"), .Cells(LR, 2)).Copy ws1.Range("A3")
    End If
    .Range("A2").AutoFilter
End With
MsgBox "Done"
Application.ScreenUpdating = True
End Sub

Take - Thanks for your reply but i want to know that is it possible to do without VBA Code as I dont want to make it on VBA I want simple file with excel formula I will be thankful to you.

 

[Takae]

Can you insert a column on columnA of Sheet2?
Assuming that the data starts from the 3rd row on both sheets,

Sheet2 A1 =IF(C4>0,1,)+A3
Sheet1 A3 =IFERROR(VLOOKUP(ROW()-2,Sheet2!A:C,2,0),"")
Sheet1 B3 =IFERROR(VLOOKUP(ROW()-2,Sheet2!A:C,3,0),"")

 

[immy]

Can you insert a column on columnA of Sheet2?
Assuming that the data starts from the 3rd row on both sheets,

Sheet2 A1 =IF(C4>0,1,)+A3
Sheet1 A3 =IFERROR(VLOOKUP(ROW()-2,Sheet2!A:C,2,0),"")
Sheet1 B3 =IFERROR(VLOOKUP(ROW()-2,Sheet2!A:C,3,0),"")

Once again thanks for your response

can you show me the result of Sheet2 A1 =IF(C4>0,1,)+A3 as i am trying but not getting any result rest of 1

 

[immy]

Yes, It works a bit