Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Excel: Populate data based on criteria [Solved/Closed]

Report
Posts
41
Registration date
Thursday June 16, 2011
Status
Member
Last seen
May 7, 2014
 -
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 -
Hello,
Good day.

I would like to populate data from 1 sheet to another sheet based on certain criterias. With reference to the sample data provided in the link below:

1. Populate data into a new sheet called "Slow Moving" when Column H is more than 90 and column D not equal to zero.

2. Populate data into a new sheet called "Non Moving" when Column G is zero and column D not equal to zero.

Thanks in advance for your help.

http://speedy.sh/WQtwf/Sample-Data.xls

Best regards,
wliang
See more
Related:

12 replies

Answer 1 / 12
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 791
row 56
col H>90 and col D<>0
to go to "SLOW"
But in this row G=0 and D is not 0 then it shd go
to "non mioving"

what do you want.

You want in both the sheets
or is this a contradiction

am I wrong ????????
Answer 2 / 12
Posts
41
Registration date
Thursday June 16, 2011
Status
Member
Last seen
May 7, 2014
Hi venkat1926,

Thanks for your reply.

If the row meets both criterias, then it should appear in both sheets.

Best regards,
wliang
Answer 3 / 12
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 791
insert the two sheets "Slow moving" and "Non Movingt"
run this macro (in the macro the worksheets name should be exactly as the sheet names) 

Sub test()
Dim j As Long, k As Long
undo
Worksheets("6200_Data").Activate

k = Range("a6").End(xlDown).Row
For j = 1 To k
If Cells(j, "H") > 90 And Cells(j, "D") <> 0 Then Cells(j, "A").EntireRow.Copy _
    Worksheets("Slow Moving").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
If Cells(j, "G") = 0 And Cells(j, "D") <> 0 Then Cells(j, "A").EntireRow.Copy _
    Worksheets("Non Moving").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Next j
Worksheets("Slow Moving").UsedRange.Columns.AutoFit
End Sub

Sub undo() 
Worksheets("slow Moving").Cells.Clear
Worksheets("Non Moving").Cells.Clear
End Sub
Answer 4 / 12
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 791
I have sent the macro test it in this sample sheet and if OK then park it in your original file and save
Answer 5 / 12
Posts
41
Registration date
Thursday June 16, 2011
Status
Member
Last seen
May 7, 2014
Hi venkat1926,

Thanks for your help. The macro is working okay now. However, I noticed that when the macro is executed, the header row and the total were copied to sheet "Slow Moving" but not to sheet "Non Moving".

How can I insert the header row to sheet "Non Moving" and omit the total row from both sheets?

I have uploaded the file and its result for your reference.

http://speedy.sh/cU3C2/Sample-Data.xls

Thanking you in advance for your help.

Best regards,
wliang
Answer 6 / 12
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 791
The macros modified. (discard old one) 

Sub test()
Dim j As Long, k As Long
undo
Worksheets("6200_Data").Activate
k = Range("a6").End(xlDown).Row
For j = 6 To k
'MsgBox j
If Cells(j, "H") > 90 And Cells(j, "D") <> 0 Then Cells(j, "A").EntireRow.Copy _
    Worksheets("Slow Moving").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
If Cells(j, "G") = 0 And Cells(j, "D") <> 0 Then Cells(j, "A").EntireRow.Copy _
    Worksheets("Non Moving").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Next j
With Range("a3").EntireRow.Copy
Worksheets("Slow Moving").Range("A1").PasteSpecial
Worksheets("Non Moving").Range("A1").PasteSpecial
End With
Worksheets("Slow Moving").UsedRange.Columns.AutoFit
Worksheets("Non Moving").UsedRange.Columns.AutoFit
End Sub


Sub undo()
Worksheets("Slow Moving").Cells.Clear
Worksheets("Non Moving").Cells.Clear
End Sub
Answer 7 / 12
Posts
41
Registration date
Thursday June 16, 2011
Status
Member
Last seen
May 7, 2014
Hi venkat1926,
Thanks for your reply. I have tried out the macro and it works now.

I still have a few questions that will need your help as I am still doing improvements on the report.

1. I want to have a subtotal of the stock value on the top of the sheet for both Slow Moving and Non Moving. And these values will need to be populated into the Summary sheet. I have recorded a macro to insert the subtotal after the data was splitted into different sheets and reference the subtotal field into the Summary. However, the field in the Summary will not change to the correct value after I ran the macro to insert the subtotal. You can see the result in the attached file.

2. Also, I will need to do a vlookup and would like to incorporate it into the macro. The column for the vlookup will be always the same column. Please advise how put this vlookup function into the macro.

http://speedy.sh/RWtMj/Sample-Data.xls

Thanks in advance for help.

Best regards,
wliang
Answer 8 / 12
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 791
I have modified slightly the macro "AddTopCol1" AND INCORPORATED THIS MACRO IN THE MACRO "test"

It is enough if you run macro "test"

I am again uploading the file and you can download the file from

http://speedy.sh/wB597/wilang-20120120-Sample-Data.xls
Answer 9 / 12
Posts
41
Registration date
Thursday June 16, 2011
Status
Member
Last seen
May 7, 2014
Hi venkat1926,
Thanks for your reply. I have tried out the macro and it returned the results desired. However, there are still some questions from my side that would need your expertise.

1. I tried to have the subtotal from Col C1 from Slow Moving sheet automatically populated into Summary sheet after macro "test" was executed. So, I entered this formula (='Slow Moving'!$C$1) into Summary sheet. However, after macro "test" was executed, the cell was referenced to C3 instead of C1. Please advise how to "fix" the referenced field in Summary sheet to always refer to C1 after macro "test" is executed.

2. I did a vlookup from Slow Moving sheet to 6810 sheet to get the data from Col I into Slow Moving sheet. I recorded a macro for this activity. However, I noticed that if the cursor was not put into the correct position, the data populated will not be correct as the macro was considering the field where the cursor is as the starting point to lookup the data in 6810 sheet.

I have upload my original file below for your reference. By the way, I have not incorporated the changes made from your previous reply into this file.

http://speedy.sh/Rwmgj/APA-Stock-Value-V1.4.xlsm

Once again, thank you in advance for your help.

Best regards,
wliang
Answer 10 / 12
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 791
sorry this morning I am confuse. there are two slow moving and two non moving. which one you are talking about.

ok now in summary sheet the entries in cell B23 and B 31 are same.Had you given the B23 as "6200_Slow_Moving_value" then it would have been easier to write the macro and also make the macro more general . similarly for B24 and B32. whild designing these matters must be kept in view

anyhow I have written a macro which either you can run independentlyl or incorporate the name of this macro in some other macro 

Sub summing()
Dim ssum(1 To 4) As Double, r As Range, ws(1 To 4) As Worksheet
Dim cfind As Range
Set ws(1) = Worksheets("6200_Slow_Moving")
Set ws(2) = Worksheets("6200_Non_Moving")
Set ws(3) = Worksheets("6810_Slow_Moving")
Set ws(4) = Worksheets("6810_Non_Moving")
For j = 1 To 4
With ws(j)

ssum(j) = WorksheetFunction.Sum(Range(.Range("C4"), .Range("C4").End(xlDown)))
End With
Next j

With Worksheets("summary")
.Range("C23") = ssum(1)
.Range("C24") = ssum(2)
.Range("C31") = ssum(3)
.Range("C32") = ssum(4)
End With
End Sub



"formula
(='Slow Moving'!$C$1)
is this correct??
should it not be
='Slow Moving'!$B$1
look into itn again.

regarding vlooup better do not incorporate it in a macro first write the macro separately and debug and if it OK incorporate it.
As I am not an expert I find it difficult to write a formula into a macro with variables.
Answer 11 / 12
Posts
41
Registration date
Thursday June 16, 2011
Status
Member
Last seen
May 7, 2014
Hi venkat1926,
I have tried out the macro provided earlier and it was working as desired. I have incorporated into other macros.

Thanks for your advice, I have modified some of the codes to make them more general to be use.

With all the guidance and help from you, the final report is considered completed now.

Thank you very much for your help and guidance.

Best regards,
wliang
Answer 12 / 12
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 791
good show. keep it up.
Ask a question