# Populating multiple rows from one sheet to another



## gsrlb (Dec 19, 2022)

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.




EMPIDNameProject NameDivisionEMPID1Name1Project Name1Division1EMPID2Name2Project Name2Division2EMPID1Name1Project Name2Division3EMPID1Name1Project Name3Division3EMPID3Name3Project Name1Division2EMPID2Name2Project Name2Division1EMPID3Name3Project Name4Division4EMPID4Name4Project Name5Division5EMPID5Name5Project Name3Division1


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 (Dec 19, 2022)

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 (Dec 19, 2022)

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 (Dec 19, 2022)

Fluff said:


> 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 (Dec 20, 2022)

Thanks for that.
A formula solution
Fluff.xlsmABCD12EMPID134EMPID1Name1Project Name1Division15EMPID1Name1Project Name2Division36EMPID1Name1Project Name3Division37Sheet2Cell FormulasRangeFormulaA4:D6A4=FILTER(Sheet1!A2:D200,Sheet1!A2:A200=B2)Dynamic array formulas.


----------

