Coping and Removing Blank cells

T

Tcarmouche

New Member
Joined
Oct 12, 2022
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
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))),"")
 
Sort by date Sort by votes
Tcarmouche said:
Ok where am I placing this and is this doing?
Click to expand...
Fluff said:
Thanks for that.
How about
New Vanity1.xlsx
AB
5
6NAMEDETACH DATE
7ERRR1-Jan-22
8THHHH6-Jan-22
9TEST5-Jan-22
10
11
12
13
Sheet2
Cell Formulas
RangeFormula
A7:B9A7=CHOOSECOLS(FILTER(Sheet1!B2:K5001,Sheet1!P2:P5001=Sheet1!R2),1,-1)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
MyMonth=Sheet1!$P$2:$P$5000A7
NameDropList=Sheet1!$H$2:$H$8A7
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B7:B5001Dates OccurringtodaytextNO
B7:B5001Dates OccurringtomorrowtextNO
B7:B5001Expression=AND(B7>TODAY(), B7-TODAY()>=WEEKDAY(B7), B7-TODAY()<WEEKDAY(B7)+7)textNO
Click to expand...
I also don't have FILTER in my excel.
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Fluff said:
Thanks for that.
How about
New Vanity1.xlsx
AB
5
6NAMEDETACH DATE
7ERRR1-Jan-22
8THHHH6-Jan-22
9TEST5-Jan-22
10
11
12
13
Sheet2
Cell Formulas
RangeFormula
A7:B9A7=CHOOSECOLS(FILTER(Sheet1!B2:K5001,Sheet1!P2:P5001=Sheet1!R2),1,-1)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
MyMonth=Sheet1!$P$2:$P$5000A7
NameDropList=Sheet1!$H$2:$H$8A7
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B7:B5001Dates OccurringtodaytextNO
B7:B5001Dates OccurringtomorrowtextNO
B7:B5001Expression=AND(B7>TODAY(), B7-TODAY()>=WEEKDAY(B7), B7-TODAY()<WEEKDAY(B7)+7)textNO
Click to expand...
I gave you edit ability on my spreadsheet
 
Upvote 0
Ok how about
New Vanity1.xlsx
AB
6NAMEDETACH DATE
7ERRR1-Jan-22
8THHHH6-Jan-22
9TEST5-Jan-22
10  
11  
12  
13  
14  
15  
Sheet2
Cell Formulas
RangeFormula
A7:A15A7=IFERROR(INDEX(Sheet1!$B$2:$B$5001,AGGREGATE(15,6,(ROW(Sheet1!$P$2:$P$5001)-ROW(Sheet1!$P$2)+1)/(Sheet1!$P$2:$P$5001=Sheet1!$R$2),ROWS(A$7:A7))),"")
B7:B15B7=IFERROR(INDEX(Sheet1!$K$2:$K$5001,AGGREGATE(15,6,(ROW(Sheet1!$P$2:$P$5001)-ROW(Sheet1!$P$2)+1)/(Sheet1!$P$2:$P$5001=Sheet1!$R$2),ROWS(A$7:A7))),"")
Named Ranges
NameRefers ToCells
MyMonth=Sheet1!$P$2:$P$5000A7:B15
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B7:B5001Dates OccurringtodaytextNO
B7:B5001Dates OccurringtomorrowtextNO
B7:B5001Expression=AND(B7>TODAY(), B7-TODAY()>=WEEKDAY(B7), B7-TODAY()<WEEKDAY(B7)+7)textNO
 
Upvote 0
Solution
Fluff said:
Ok how about
New Vanity1.xlsx
AB
6NAMEDETACH DATE
7ERRR1-Jan-22
8THHHH6-Jan-22
9TEST5-Jan-22
10  
11  
12  
13  
14  
15  
Sheet2
Cell Formulas
RangeFormula
A7:A15A7=IFERROR(INDEX(Sheet1!$B$2:$B$5001,AGGREGATE(15,6,(ROW(Sheet1!$P$2:$P$5001)-ROW(Sheet1!$P$2)+1)/(Sheet1!$P$2:$P$5001=Sheet1!$R$2),ROWS(A$7:A7))),"")
B7:B15B7=IFERROR(INDEX(Sheet1!$K$2:$K$5001,AGGREGATE(15,6,(ROW(Sheet1!$P$2:$P$5001)-ROW(Sheet1!$P$2)+1)/(Sheet1!$P$2:$P$5001=Sheet1!$R$2),ROWS(A$7:A7))),"")
Named Ranges
NameRefers ToCells
MyMonth=Sheet1!$P$2:$P$5000A7:B15
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B7:B5001Dates OccurringtodaytextNO
B7:B5001Dates OccurringtomorrowtextNO
B7:B5001Expression=AND(B7>TODAY(), B7-TODAY()>=WEEKDAY(B7), B7-TODAY()<WEEKDAY(B7)+7)textNO
Click to expand...
YES YES!!! Thank you Thank you. It works. So I'm assuming for the following columns I'm changing (Sheet1!$P$2:$P$5001=Sheet1!$R$2),ROWS(A$7:A7))),"") the bold numbers and letters?
 
Upvote 0
You are the man. Dang I needed you 5 days ago when I was searching and trying to figure this out. I found you when I was googling, now you're my new friend :)
 
Upvote 0
Glad to help & thansk for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Macro that changes cell after each copy and paste
2
Replies
11
Views
474
PeteWright
PeteWright
Jovinchious
  • Question
Vlookup but with unique values
Replies
4
Views
134
etaf
etaf
T
  • Question
This is so slow I have to run it overnight
Replies
4
Views
273
kevin9999
K
C
  • Solved
Copy only required cells to new workbook
Replies
5
Views
173
MARK858
MARK858
B
  • Solved
Use Sheet1:D5 to find matching cell in Sheet2 Column A:A, and select cell (or row)
Replies
1
Views
85
brianv
B

Forum statistics

Threads
1,216,103
Messages
6,128,854
Members
449,472
Latest member
ebc9

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top