Excel: Populate data based on criteria Solved/Closed

Question

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://ww31.kwszz.gce8xz.ygwt.online/?sov=96448523&hid=csokkckmioigeooo&cntrl=00000&pid=14497&redid=49184&gsid=453&campaign_id=12&p_id=14497&id=XNSX.-r49184-t453&impid=3fa95696-3c04-11e8-b7f7-aa1f778d2780

Best regards,
wliang

venkat1926 · Answer

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 ????????

wliang · Answer

Hi venkat1926,

Thanks for your reply. 

If the row meets both criterias, then it should appear in both sheets.

Best regards,
wliang

venkat1926 · Answer

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

venkat1926 · Answer

I have sent the macro test it in this sample sheet and if OK then park it in your original file and save

wliang · Answer

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

venkat1926 · Answer

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

wliang · Answer

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

venkat1926 · Answer

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

wliang · Answer

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

venkat1926 · Answer

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.

wliang · Answer

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

venkat1926 · Answer

good show. keep it up.

Excel: Populate data based on criteria

12 responses

Similar discussions

Subscribe To Our Newsletter!