Create a list on multiple criteria from a dynamic master sheet

[Dee Dee]

Hi,

I have a question that I am not sure if I am explaining right. I have a full database of information and I want to make a new sheet with a list off of that database carrying over three columns of information IF one other column in the database indicates Yes. I somehow cannot think of how to ask the question to be able to look for the answer. Any assistance would be appreciated. Clarification is probably needed I know.

 

[alansidman]

Welcome DeeDee. Suggest you upload a sample data file that is representative of your actual data. Use XL2BB to upload that information. It does not need to be a lot of records 8-10 will generally suffice. Look at my signature block for instructions on how to employ XL2BB

 

[Dee Dee]

test keta.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ICOMPLAINTS #	AGGRIEVED PERSON	COMMISSARY/HQ	EMPLOYMENT STATUS	INITIAL CONTACT DATE	ASSIGNED COUNSELOR	BASIS(ES)	ISSUE(S)	HARASSMENT ALLEGED (Y/N)?	HARSSMENT QUESTIONS ISSUED (Y/N)?	DATE OF MOST RECENT ALLEGED ACT	DATE OF CLOSURE	Previous Counseling (Y/N)
2		James Brown	bottle	unemployed	8/1/20								y
3		Lucille Ball	care	secretary	1/2/20								y
4		Michael Jordan	shoe	hoe	3/4/20								y
5		Mickey Mouse	slipper	dealer	9/2/20								n
6		George Jefferson	dope	doctor	12/1/19								n
7		Louise Jefferson	can	thief	11/7/19								y
8		JJ Walker	mat	carpenter	3/2/18								y
9		Jane Doe	war	laithworker	9/30/19								y
10		Captain Ahab	dog	stevedore	9/30/18								n
Contacts (No counseling)

Cells with Data Validation
Cell	Allow	Criteria
I1:J10	List	='[Complaints Tracker - FY21.xlsx]DropDown'!#REF!
F1:F10	List	='[Complaints Tracker - FY21.xlsx]DropDown'!#REF!
D1,D11:D1048576	List	='[Complaints Tracker - FY21.xlsx]DropDown'!#REF!

 

[Dee Dee]

I would need to make a list in another sheet with columns B, C, D, and E copied over IF column M is "Y".

 

[alansidman]

VBA solution:

Option Explicit

Sub DD()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim i As Long, lr As Long, lr2 As Long
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    lr = s1.Range("B" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 2 To lr
        If s1.Range("M" & i) = "y" Then    'Note VBA is case sensitive
            lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
            s1.Range("B" & i & ":E" & i).Copy
            s2.Range("A" & lr2 + 1).PasteSpecial xlPasteValues
        End If
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "complete"

End Sub

Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

 

[Dee Dee]

I didn't get it to work. I copied from first to the last line of the code, was that right? Or was I to leave the option and end lines out?

 

[Fluff]

How about a formula

+Fluff New.xlsm
	A	B	C	D	E
1	AGGRIEVED PERSON	COMMISSARY/HQ	EMPLOYMENT STATUS	INITIAL CONTACT DATE
2	James Brown	bottle	unemployed	01/08/2020
3	Lucille Ball	care	secretary	02/01/2020
4	Michael Jordan	shoe	tailor	04/03/2020
5	Louise Jefferson	can	thief	07/11/2019
6	JJ Walker	mat	carpenter	02/03/2018
7	Jane Doe	war	laithworker	30/09/2019
8
9
10
Sheet2

Cell Formulas
Range	Formula
A2:D7	A2	=FILTER(Sheet1!B2:E100,Sheet1!M2:M100="y")
Dynamic array formulas.

 

[Dee Dee]

How about a formula

+Fluff New.xlsm
	A	B	C	D	E
1	AGGRIEVED PERSON	COMMISSARY/HQ	EMPLOYMENT STATUS	INITIAL CONTACT DATE
2	James Brown	bottle	unemployed	01/08/2020
3	Lucille Ball	care	secretary	02/01/2020
4	Michael Jordan	shoe	tailor	04/03/2020
5	Louise Jefferson	can	thief	07/11/2019
6	JJ Walker	mat	carpenter	02/03/2018
7	Jane Doe	war	laithworker	30/09/2019
8
9
10
Sheet2

Cell Formulas
Range	Formula
A2:D7	A2	=FILTER(Sheet1!B2:E100,Sheet1!M2:M100="y")
Dynamic array formulas.

This one says that the "function isn't valid". I would really appreciate if the formula can work because I am not very confident with the VBA

 

[Fluff]

Ok, that means you don't have the latest update yet. Try

+Fluff New.xlsm
	A	B	C	D
1	AGGRIEVED PERSON	COMMISSARY/HQ	EMPLOYMENT STATUS	INITIAL CONTACT DATE
2	James Brown	bottle	unemployed	01/08/2020
3	Lucille Ball	care	secretary	02/01/2020
4	Michael Jordan	shoe	tailor	04/03/2020
5	Louise Jefferson	can	thief	07/11/2019
6	JJ Walker	mat	carpenter	02/03/2018
7	Jane Doe	war	laithworker	30/09/2019
8
9
10
11
12
13
14
Sheet2

Cell Formulas
Range	Formula
A2:D10	A2	=IFERROR(INDEX(Sheet1!B$2:B$100,AGGREGATE(15,6,(ROW(Sheet1!$M$2:$M$100)-ROW(Sheet1!$M$2)+1)/(Sheet1!$M$2:$M$100="y"),ROWS(A$2:A2))),"")

 

[alansidman]

Cannot determine what is wrong in your situation. It worked perfectly with the data you provided. Do you have a second tab named "Sheet2"