Transport column data from a sheet to another

[l3m0npartii]

Hi,

I have two sheets in my workbook. One is linked and updated with another sheet in another file, and the second one is where I want to transport data to (from the first one).

So in the first sheet, I have a list of names along with some groups, levels, etc in which they belong to. I want to be able to write a formula where I can have a list of these names in the second sheet, however, only transporting those who are in a certain group or level. So far, what I use is the IF function such as:

*The formula is just to show you the idea, not the actual formula*

=IF(GroupnameB1=Team1,"",IF(LevelC1=5,"",NameA1))

So as you can see, I create a filter of the group, then I make the value if false to be another filter.

But here is the problem, if the person does not belong to neither Team1 nor Level 5, on the list I get in the second sheet there will be a blank.

I want to have a list of names of people who belong to team1 and level 5 but without these blanks in between.

help???

 

[AFPathfinder]

If you're looking for a simple solution, you can just use autofilter and/or sort on the second sheet to show only those that fit both criteria.

=IF(AND(B1="Team1",C1="5"),A1,"")

Advance filter may also be an option, but it's a bit difficult to use. VBA can make it easy and repeatable, but it can be a little overkill for a simple solution.

As far as I know, there isn't an easy formula that will give you a custom list without using sort/autofilter.

 

[l3m0npartii]

If you're looking for a simple solution, you can just use autofilter and/or sort on the second sheet to show only those that fit both criteria.

=IF(AND(B1="Team1",C1="5"),A1,"")

Advance filter may also be an option, but it's a bit difficult to use. VBA can make it easy and repeatable, but it can be a little overkill for a simple solution.

As far as I know, there isn't an easy formula that will give you a custom list without using sort/autofilter.

Hi,

Thanks for the help, this clearly shortens my equations. However, it still creates a blank for those names that aren't belong to the filters.

What coding would I put down for this in Macros?

 

[AFPathfinder]

I haven't tested this, but this is most likely how I would do it:

Sub NoBlanks()
    Dim i As Integer
    
    Sheets(Sheet2).Range("A1").CurrentRegion.Delete Shift:=xlUp
    
    Sheets(Sheet1).Activate
    
    Range("A1").AutoFilter Field:=2, Criteria1:="Team1"
    Range("A1").AutoFilter Field:=3, Criteria1:="5"
    Range("A1").CurrentRegion.Copy Destination:=Sheets(Sheet2).Range("A1")
End Sub