How to create automated list based on criteria

[Angelfish13]

Hello,

I am currently manually creating a list in excel that is taking a very long time. I was wondering if there is a way to automate this. Please see below for an example. Thank you so much for your help!

Columns for the criteria:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Number of Orders Placed[/TD]
[/TR]
[TR]
[TD]Justin[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


List I manually enter based on columns above (another column in excel):
[TABLE="width: 500"]
<tbody>[TR]
[TD]People with Zero Orders[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The list I manually enter actually takes forever because there is hundreds of names and they are all scrambled with people who have a lot of orders. All I am doing is looking who has a zero next to their name and typing their name in another column in a list format. Is there any way to have excel do this automatically with a formula or macro? Please let me know, thanks so much for your help!

 

[L. Howard]

In another column and in the same row as Justin... =IF(B2=0,A2,"") and pull down as far as your people list goes.


Howard

 

[My Aswer Is This]

So if I understand you correctly if "John" is in column "A"

And column B next to "John" is "0" this being a actual zero you want John entered into column "E" for example
And John may be in column "A" more then once but if he ever has a empty space in column "B" next to his name you want his named shown in column "E" only once.
Now if you mean Column "B" would be empty not having a actual Zero remove the 0 in my script and just have it like: "" not "0"
If that is true try this Vba Macro:

Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim x As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
x = 1
    For i = 1 To Lastrow
        If Cells(i, 2).Value = "0" Then
            Cells(x, 5).Value = Cells(i, 1).Value
            x = x + 1
        End If
    Next
Range("E1:E" & Cells(Rows.Count, "E").End(xlUp).Row).RemoveDuplicates 1, xlNo
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

All I am doing is looking who has a zero next to their name and typing their name in another column in a list format.

Try this, copied down after adjusting the ranges to match your data.

Book1
	A	B	C	D
1	Name	Orders Placed		Zero Orders
2	Justin	2		Fred
3	Bob	3		Mary
4	Fred	0
5	Sally	1
6	Mary	0
Zero Orders

Cell Formulas
Range	Formula
D2	=IFERROR(INDEX(A$2:A$6,AGGREGATE(15,6,(ROW(A$2:A$6)-ROW(A$2)+1)/(B$2:B$6=0),ROWS(D$2:D2))),"")