I need a formula to reflect value in column A in sheet 1 if column B in that sheet is blank. I need those value of column A to be fetched to sheet 2
out of all rows only rows with blank cells in column B have to appear
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
REFLECT DATA WITH BLANK CELLS
0
Discussion
Hi Ravi
Currently you can choose between a macro solution from Willie and my array formula solution (for later versions of Excel). Over to you...
p. s. Please don't use all CAPS for the title of your question - it looks like you're shouting at us!
Currently you can choose between a macro solution from Willie and my array formula solution (for later versions of Excel). Over to you...
p. s. Please don't use all CAPS for the title of your question - it looks like you're shouting at us!
John_Ru (rep: 6142) Apr 14, '23 at 1:36 pm
@RAVI96716,
Does John's solution or mine meet your needs? If so, please mark the solution as Selected Answer.
Thank you.
Does John's solution or mine meet your needs? If so, please mark the solution as Selected Answer.
Thank you.
WillieD24 (rep: 547) Apr 15, '23 at 8:21 am
@Willie - the wording of the question gives me the feeling that the likely outcome will be no response. Hope I'm wrong and Ravi assesses / decides to select an answer.
John_Ru (rep: 6142) Apr 15, '23 at 6:06 pm
@Willie - looks like I was right :(
John_Ru (rep: 6142) Apr 18, '23 at 3:18 pm
@John,
Yes, that does seem to be the case. That also seems to be the case with classic80srock. I guess, for some, it is too much work to give a simple "thank you".
Also, congratulations on cracking 5000 !!
Yes, that does seem to be the case. That also seems to be the case with classic80srock. I guess, for some, it is too much work to give a simple "thank you".
Also, congratulations on cracking 5000 !!
WillieD24 (rep: 547) Apr 22, '23 at 12:14 pm
@Willie
Sadly you're right about some users (and it tries your patience but I give them" the benefit of the doubt" and try to help). Luckily other users are responsive and appreciative- without them I'd never have reached 5,000!
Sadly you're right about some users (and it tries your patience but I give them" the benefit of the doubt" and try to help). Luckily other users are responsive and appreciative- without them I'd never have reached 5,000!
John_Ru (rep: 6142) Apr 22, '23 at 4:07 pm
Answers
0
Hello RAVI96716 and welcome to the forum.
You didn't provide a sample file but I think I understand what you are hoping to achieve.
In the file attached, the macro will loop through each used cell in Sheet1-Column A and if the adjacent cell in Sheet1-Column B is empty, copy the value of Sheet1-Column A to Sheet2-Column A
If this is solves your quetion, please mark the answer as selected
Cheers :-)
0
Hi Ravi and welcome to the Forum
If you have a recent version of Excel (365, 2021, 2019, web, Android orPad), you can use the FILTER function within a special type of formula called an Array formula. The function is described in Don's tutorial here: FILTER() Function Excel - Power Lookup for the Future
That does what filtering would do but just by typing a formula. (Note that in Excel 365, you only have to enter the formula but in other version you must use Ctrl+Shift+Enter to add an array formula.)
In the attached demo file, sheet 1 has the row number (like Row 2) in column A but some rows in column B have no data.
In (only) yellow cell A2 of sheet 2, the array formula is:
=FILTER(Sheet1!A2:A21,ISBLANK(Sheet1!B2:B21))and the values automatically "spill" into the blank cells below.
You'd need to change the ranges in bold to suit your case but that equation means "filter the range A2 to A21 but only show what's in A if column B is blank".
Currently the demo will show on sheet 2:
Row 4
Row 7
Row 10
Row 11
Row 14
Row 15
Row 18
Row 19
Row 20which matches where B is blank on sheet 1 currently but if you add blanks or remove them in column B (or change values in A), the sheet 2 data will alter dynamically to suit.
Hope this helps. If so, please remember to mark this Answer as Selected (or choose the solution from Willie which should work with all versions of Excel).