How to autofill a table from Sheet 1 to Sheet 2 based on a condition?

[keyri]

On the same workbook, I have Sheet 1 and Sheet 2. Sheet 1 is the master list where all business gathered go into the database with the respective towns and has 6 columns. Sheet 2 is an individualized town report where A1 will have the Town name.

Sheet 1

Sheet 2

I would like to create a macro where any row information with that town name from sheet 1 will automatically transfer to sheet 2 making sure it does not repeat. I don't know how to do it.

Can anyone help?

Thanks
Keyri

 

[James006]

Hello Keyri,

If I understand correctly you do need to return many values from your 'Town' lookup ...

I would suggest to start your report in Sheet 2 in cell B2 and use following Array formula :

[TABLE="width: 84"]
<tbody>[TR]
  [TD="class: xl63, width: 84"]IF(ROWS(B$2:B2)<=COUNTIF(Sheet1!$A$2:$A$21,$A$1),INDEX(Sheet1!$B$2:$B$21,SMALL(IF(Sheet1!$A$2:$A$21=$A$1,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROWS(B$2:B2))),"")[/TD]
[/TR]
</tbody>[/TABLE]

In order to enter this formula ... you need to use simultaneously the 3 keys Control Shift Enter ... instead of the standard Enter key ...

Then ... you can copy down this formula ... in order to get all the Business Names which share the same town selected in cell A1 ...

Hope this will help