Good day all,
My next conundrum! Without going into too much detail, I have a large list of numbers on one sheet (Sheet1) that I need to copy to another sheet (Sheet2) without copying any blanks. Is there a formula that I can put into sheet2 that will automatically update without including blanks? I've attached an example workbook to help further ok, as always thanks for any assistance - Marco
Last edited by matrixpom; 04-11-2018 at 08:13 PM.
No workbook.
Dave
Use PowerQuery (Get&Transform) built-in feature of Ex2016
Sorry Guysin original post
Will have a look.
Also please don't edit posts after there have been replies. In "messes" with the logic flow of the conversation.
Withdrawn by FR.
Last edited by FlameRetired; 04-11-2018 at 08:43 PM.
In this formula the range is set from row 4 to 100. Make it larger to future-proof added data if needed.
Formula:
Please Login or Register to view this content.
This is a resource heavy formula for that many cells.
If it were me I would go with sandy's Pivot table solution and a small macro that updates upon changing the source data.
Last edited by FlameRetired; 04-11-2018 at 09:02 PM.
Thanks guys, I will give it a try later today...think I may ultimately go with the pivot table as suggested though, thanks again guys & have a great day! - Marco
@Dave,
PivotTable doesn't work in this case
You formula is doing this, treat each column individually
PowerQuery read source as table but result must be in separated column
I don't know what is better... much faster (to do ) is with your formula (except excel slow down with too many rows)
Yeah, I "stood on my head" trying to find a non-array formula. Never found it.
The only other approach I could come up with was many-step-non-automatic.
I suspect if someone with VBA skill looked at this they could make short work of it.
While going through your data, there are so many blank cells in various column & row.
You want to copy non blank data. For reduce the file / formula size, if you go with criteria then it will reduce the size. Instead of whole sheet extract non blank data.
But let me know the basic for what / which purpose of this data.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks