Formula to copy filtered data to another tab

[robertguy]

Hi,

I have data entered on to a tab called ‘Data’ and what I want to do is to populate the ‘Output’ tab with data filtered from the ‘Data’ tab i.e. filter on column ‘B’ (Type) for ‘Project’, Major Project’ and ‘Minor Project’ only.

Can anyone please advise me of the formulas required to copy the filtered data from the ‘Data’ tab to the ‘Output’ tab i.e. the filtered data in columns 'A' to 'C'

I have attached example file which shows the desired result on the Output’ tab.

I appreciate this could be done via macros/vba or advance filtered but in this instance a formula solution is required

Many thanks in advance for any assistance received

Regards


Rob

[AlKey]

Enter array formula in A2 and copy across and down

Please Login or Register  to view this content.

***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[PFDave]

Hi Robert

Try this code below

Please Login or Register  to view this content.

[PFDave]

Example file attached

[robertguy]

Alkey & PFDave,

many thanks for your very prompt replies it is very much appreciated.

PFDave, as the end user is not used to using macros I've opted to use Alkey solution (However, I will be using your solution myself on another project I'm working on) - many thanks.

That's said, Alkey, I've just re-read the selection criteria for the drop down selection on the 'Data' tab and it should read 'Project', 'Major Project', 'MSA Small Project', 'MSA Standard Project' and 'Programme' !! Could you advise me on how to amend your formula to accommodate the revised selection criteria.

Many thanks


Regards


Rob

[AlKey]

Try this
Please note that search function in this formula will pick up all words that contain "project". And if I understood correctly, you want to add 'Programme' as criteria.

Please Login or Register  to view this content.

[samba_ravi]

in output sheet
a2

Please Login or Register  to view this content.

try this and copy across

[PFDave]

Alkey & PFDave,

many thanks for your very prompt replies it is very much appreciated.

PFDave, as the end user is not used to using macros I've opted to use Alkey solution (However, I will be using your solution myself on another project I'm working on) - many thanks.

Glad I've been some use Rob

[AlKey]

Here is another way with criteria table
For this example I created a table in Col L L2:L6
Enter array formula in A2 on Output sheet and copy across and down.

Please Login or Register  to view this content.

v

A

B

C

D

E

F

G

H

I

J

K

L

1

Code

Type

Number

Criteria Table

2

AAA

Project

1

Project

3

BBB

Major Project

2

Major Project

4

AAA

MSA Small Project

4

MSA Small Project

5

CCC

Programme

6

MSA Standard Project

6

AAA

Project

7

Programme

7

BBB

Major Project

8

8

BBB

MSA Standard Project

10

9

BBB

Major Project

16

10

BBB

Project

18

11

CCC

Programme

19

12

AAA

Project

25

13

BBB

Programme

26

14

[robertguy]

Alkey & PFDave & nflsales

Once again folks many thanks for your suggestions, they all work as required, that said for future maintenance purposes I'm leaning towards Alkey's latest solution as it will be easier for the under users to maintain.

Thanks again all

Regards


Rob