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

immy

New Member
Joined
Jan 20, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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 1Sheet 2
Jan-21
Data
NameSalesNameSales
Kashif
100​
Aiman
0​
Shahid
50​
Ali
0​
Asif
70​
Kashif
150​
Amir
50​
Qazi
40​
Asif
70​
Shahid
50​
Raza
100​
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Takae

Well-known Member
Joined
Jul 1, 2015
Messages
723
Hope this helps.
VBA Code:
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

New Member
Joined
Jan 20, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Hope this helps.
VBA Code:
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

Well-known Member
Joined
Jul 1, 2015
Messages
723
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

New Member
Joined
Jan 20, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,489
Messages
5,636,626
Members
416,932
Latest member
mm07

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top