I am trying to find a way to enter a blank row after every other cell with a text is detected. I can do this manually but when i have data with hundreds of rows, it just gets to tedious. I attached a excel file that hopefully illustrates the goal i am trying to achieve. Thank you in advance.
Perhaps this will help.
1. Convert the range A2:B10 into an Excel table
2. Fill in the blanks in A2:A10 by utilizing Find &Select > Go To Special > Blanks > =(up arrow) and Enter
3. Load into power query (From Table/Range on Data tab)
4. Use the following M-code:Note that the code is explained in the linked video: https://www.youtube.com/watch?v=Wq8Iuuw7DFE&t=22s
Please Login or Register to view this content.
5. Conditionally format the Room# column (J) using: =J3=J2 (Font = white)
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Re: Enter blank row after group of cells or empty cell is detected.
a bit shorter M
Original Expected Result Room # Room Room # Room Room # Room a1 kitchen a1 kitchen a1 kitchen a2 bedroom a3 living room a2 bedroom a2 bedroom dining room a4 stairs a3 living room a3 living room a5 restroom dining room dining room powder a6 lobby a4 stairs a4 stairs a5 restroom a5 restroom powder powder a6 lobby a6 lobby
Please Login or Register to view this content.
Last edited by sandy666; 06-08-2023 at 08:29 PM. Reason: update tables in the post
Here is also a solution with a formula:
I'm not sure the formula could be simpler, but it works: Please tryFormula:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks