LinkBack URL
About LinkBacks
Is there a way to populate sheet2 from sheet1 while removing blank rows?
Is there a way to populate sheet2 from sheet1 while removing blank rows?
Hi, gz3s36!
One option with formulae. Blessings!
On your Expected Results sheet enter formula in A1 and drag formula across and down
***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Formula:
Please Login or Register to view this content.
For Excel (2010, 2013, 2016) use this regular formula
Formula:Please Login or Register to view this content.
Last edited by AlKey; 12-20-2017 at 02:40 PM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Is there a way for it to not return 0 for blank cells?Originally Posted by AlKey
On your Expected Results sheet enter formula in A1 and drag formula across and down
***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Formula:Please Login or Register to view this content.
For Excel (2010, 2013, 2016) use this regular formula
Formula:Please Login or Register to view this content.
Yes there is a way
For array formula
Formula:Please Login or Register to view this content.
For non-array
Formula:Please Login or Register to view this content.
Just aren't having any luck.
Luck? This is nothing to do with luck.
Here is an attached file with two tables. On the left with array formula on the right is non-array.
Here is a VBA solution
How to install your new code
Please Login or Register to view this content.
- Copy the Excel VBA code
- Select the workbook in which you want to store the Excel VBA code
- Press Alt+F11 to open the Visual Basic Editor
- Choose Insert > Module
- Edit > Paste the macro into the module that appeared
- Close the VBEditor
- Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
To run the Excel VBA code:
- Press Alt-F8 to open the macro list
- Select a macro in the list
- Click the Run button
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
I can't make it work. Either solution.
Your issue is that you don't have one consistent column to be evaluated. Suggest you reformat your spreadsheet to have at least one column to evaluate. It would have been helpful from the start if you had presented a sample workbook that was representative of your actual needs. Based upon your original presentation, both solutions would work. Help us to help you in the future by presenting representative samples. We cannot read your mind or see your screen.
This code tests for any blank rows. If the entire row is blank, then it copies the data in columns A:S to Sheet2. You have a link to an outside worksheet. This interferes with the code. You may wish to break the link before running the code.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks