I need an array formula

[Gillman]

Hi there,

I have a tab of data in columns A:X, around 1,000 lines. In columns Z:AJ, I have formulae that look at this data and in particular, in column AI, for example, the formula returns "Money" for around 60 of the 1,000 lines (it various monthly).

What I want to do is set up a formula on a separate tab, which returns the data in columns A:X from the main tab if "Money", for example is set as the criterion. The problem is, "Money" may appear randomly in rows, 1, 60, 500, etc and if I use the formula =IF('Maintab'!AI1="MONEY",'Currenttab'!A1,"") I will have hundreds of blank lines - so I need a formula that I can drag down and will populate when I dump the new data onto the main tab.
So, I was thinking an array formulae might be necessary here.

What I currently do now is filter on "Money" in column AI on the main tab and manually have to copy this across to the other tab.

If anyone is able to help, they would really be a life saver.

I'd appreciate any help anyone is able to offer.

Cheers,
Darren

 

[Damon Ostrander]

Hi Darren,

I think the easy way would simply be to run this macro:

Sub CopyMoneyRows()
   'Copies all rows columns A:X that have "Money" in column AI from active worksheet
   'to new worksheet
   
   Dim sRow    As Long        'row number on source worksheet
   Dim dRow    As Long        'row number on destination worksheet
   Dim SrcWS   As Worksheet   'the source (initially active) worksheet
   
   Set SrcWS = ActiveSheet
   
   'create new blank worksheet (note that this becomes the active sheet)
   Worksheets.Add after:=SrcWS
   
   dRow = 1    'assume 1 header row
   
   With SrcWS
   
      .Range("A1:X1").Copy Destination:=Range("A1")
   
      For sRow = 1 To .Range("AI65536").End(xlUp).Row
         If UCase(.Cells(sRow, "AI")) = "MONEY" Then
            dRow = dRow + 1
            .Range(.Cells(sRow, "A"), .Cells(sRow, "X")).Copy Destination:=Cells(dRow, "A")
         End If
      Next sRow
   
   End With
End Sub

Keep Excelling.

Damon

 

[Gillman]

Hi Damon,

Thank you very much for that. I apologise, but I failed to mention that the main data tab is used to feed several other tabs, based on other criterion. And all these tabs have formulae in columns after X, so I cannot have new tabs being created.

I scoured the internet last night and found a formula:

=INDEX('Monthly Atex Data'!$A$5:$AQ$3004,SMALL(IF(('Monthly Atex Data'!$AQ$3:$AQ$3004=$A$1),ROW(5:3004)),ROW(5:5)),COLUMNS($A:A))

It is returning the data, however what it returns is random and not necessarily only rows that have "money" in column AQ.

Would you be able to help me get this right, or any there any other formulae I should be looking at?

Many thanks for your reply and that macro will actually be of use to me in other areas.

Cheers,
Darren