Populating multiple rows from one sheet to another

[gsrlb]

Hi,

I am trying to populate a sheet B based on EMPID from Sheet A. I am doing this using VLOOKUP. Please see details of Sheet A (a sample) as below.

EMPID	Name	Project Name	Division
EMPID1	Name1	Project Name1	Division1
EMPID2	Name2	Project Name2	Division2
EMPID1	Name1	Project Name2	Division3
EMPID1	Name1	Project Name3	Division3
EMPID3	Name3	Project Name1	Division2
EMPID2	Name2	Project Name2	Division1
EMPID3	Name3	Project Name4	Division4
EMPID4	Name4	Project Name5	Division5
EMPID5	Name5	Project Name3	Division1

However, when I use VLOOKUP to lookup based on EMPID, I am not able to populate all rows. Say for example, when I lookup using EMPID for EMPID1, I wanted to ensure all rows for EMPID1 (all projects that EMPID1 is assigned to) are populated and not just the first match.

Please advise how can I achieve this?

 

[Michael M]

I think you will need a VBA

Sub MM3()
Dim ans As String
ans = InputBox("which text do you want to copy")
 With Sheets("Sheet A").UsedRange
            .AutoFilter
            .AutoFilter field:=1, Criteria1:=ans
            .Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet B").Range("A1")
            .AutoFilter
    End With
End Sub

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[gsrlb]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you @Fluff. I'm using O365.

 

[Fluff]

Thanks for that.
A formula solution

Fluff.xlsm
	A	B	C	D
1
2		EMPID1
3
4	EMPID1	Name1	Project Name1	Division1
5	EMPID1	Name1	Project Name2	Division3
6	EMPID1	Name1	Project Name3	Division3
7
Sheet2

Cell Formulas
Range	Formula
A4:D6	A4	=FILTER(Sheet1!A2:D200,Sheet1!A2:A200=B2)
Dynamic array formulas.