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]

Can you post some sample data along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Tcarmouche]

Can you post some sample data along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I can’t use that program on a government computer.

 

[Tcarmouche]

I can’t use that program on a government computer.

I can email it to you.

 

[Fluff]

I can’t use that program on a government computer.

Ok, fair enough. Are you trying to match all of col P to R2, or match P2 to R2 P3 to R3 etc?

 

[Tcarmouche]

Ok, fair enough. Are you trying to match all of col P to R2, or match P2 to R2 P3 to R3 etc?

Can you send you my spreadsheet in a email?

 

[Fluff]

No you cannot, however you could upload it to a share site such as oneDrive, Dropbox, GoogleDrive, then mark for sharing & post the link you are given to the thread.

 

[Tcarmouche]

Ok, fair enough. Are you trying to match all of col P to R2, or match P2 to R2 P3 to R3 etc?

No you cannot, however you could upload it to a share site such as oneDrive, Dropbox, GoogleDrive, then mark for sharing & post the link you are given to the thread.

New Vanity1.xlsx

Sheet1 STATUS,APPLICANT NAME,RANK,EMAIL,DATE APPLICATION RECEIVED,DATE OF LAST COMMUNICATION,PENDING-NOTES,LOGGED BY,CONTROL DATE,NEED HOUSING BY DATE,DETACH DATE,REFERRED BY,Column L Month,Number,Month,Names Complete,TERRELL CARMOUCHE,O5,3,1-Jan-22,1-Jan-22,ACummings,31-Dec-22,31-Dec-22,20-Oct-...

docs.google.com

 

[Fluff]

Thanks for that.
How about

New Vanity1.xlsx
	A	B
5
6	NAME	DETACH DATE
7	ERRR	1-Jan-22
8	THHHH	6-Jan-22
9	TEST	5-Jan-22
10
11
12
13
Sheet2

Cell Formulas
Range	Formula
A7:B9	A7	=CHOOSECOLS(FILTER(Sheet1!B2:K5001,Sheet1!P2:P5001=Sheet1!R2),1,-1)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
MyMonth	=Sheet1!$P$2:$P$5000	A7
NameDropList	=Sheet1!$H$2:$H$8	A7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B7:B5001	Dates Occurring	today	text	NO
B7:B5001	Dates Occurring	tomorrow	text	NO
B7:B5001	Expression	=AND(B7>TODAY(), B7-TODAY()>=WEEKDAY(B7), B7-TODAY()<WEEKDAY(B7)+7)	text	NO

 

[Tcarmouche]

Thanks for that.
How about

New Vanity1.xlsx
	A	B
5
6	NAME	DETACH DATE
7	ERRR	1-Jan-22
8	THHHH	6-Jan-22
9	TEST	5-Jan-22
10
11
12
13
Sheet2

Cell Formulas
Range	Formula
A7:B9	A7	=CHOOSECOLS(FILTER(Sheet1!B2:K5001,Sheet1!P2:P5001=Sheet1!R2),1,-1)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
MyMonth	=Sheet1!$P$2:$P$5000	A7
NameDropList	=Sheet1!$H$2:$H$8	A7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B7:B5001	Dates Occurring	today	text	NO
B7:B5001	Dates Occurring	tomorrow	text	NO
B7:B5001	Expression	=AND(B7>TODAY(), B7-TODAY()>=WEEKDAY(B7), B7-TODAY()<WEEKDAY(B7)+7)	text	NO

Ok where am I placing this and is this doing?