Coping and Removing Blank cells

[Tcarmouche]

I have 2 formulas that I need to combine into 1 can anyone help. Here’s the scenario: If Sheet1 cell P2 equals Sheet1 cell R2, copy cell B2 into Sheet2 cell A7. However when I go further down the sheet on Sheet1 (i.e. B17) I don’t want it to copy onto sheet2 in the same location as sheet1, I need it to copy one under the other (I.e. copy onto Sheet2 A7 then A8 and so on)

=IF(Sheet1.!P2=Sheet1|$R$2,Sheet11B2,"")

=IFERROR(INDEX$A$1:$A$5000,SMALL(IF($A$1:$A$5000<>"",ROW($A$1:$A$5000)-
ROW(SA$1)+1),ROW(A7))),"")

 

[Fluff]

Did you copy the formula all the way down to row 5001? If so that's the reason.

 

[Tcarmouche]

Yes I did. Should I not have?

 

[Fluff]

That really depends on your data, if you are only ever going to get 100 results, then copy it down to about row 200.

 

[Tcarmouche]

Ok I will do that. Thanks again.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Tcarmouche]

Is there a way to auto sort a sheet in date order.
Ex: Column A -C will sort in date order based off of the dates in Column C.

From this:

Column A	Column B	Column C
Name	Rank	Date
Test One	P1	11-Dec-22
Test Three	P3	8-Jul-22
Test Two	P2	2-May-22

To This:

Column A	Column B	Column C
Name	Name	Name
Test Two	P2	2-May-22
Test Three	P3	8-Jul-22
Test One	P1	11-Dec-22

My true ranges on my sheet1 are A-L (Row 1 is the header) and the date data is in Column K

 

[Fluff]

As you already have a thread running for this question, you need to stick to it. Thanks