Excel-me-do - Teh Forum

<body> <div id="header"> <span class="left"> <span class="back"><a href="lthread_list.php?webtag=DEFAULT"><span class="image mobile_back" title="Back"></span></a></span> <span class="image mobile_logo" title="Beehive Forum Logo"></span> </span> <ul> <li> <a class="reply_all" href="lpost.php?webtag=DEFAULT&reply_to=41876.0&return_msg=41876.1"> <span class="image mobile_reply_all"></span><span class="text">Reply to All</span> </a> </li> <li> <a class="navigation" href="#"> <span class="image mobile_navigation"></span><span class="text">Show messages</span> </a> </li> <li> <a class="main" href="#"><span class="image mobile_menu" title="Menu"></span> </a> </li> </ul> </div> <div class="menu main"> <ul> <li><a href="lthread_list.php?webtag=DEFAULT">Messages</a></li> <li><a href="lpm.php?webtag=DEFAULT">Inbox</a></li> <li><a href="lsearch.php?webtag=DEFAULT">Search</a></li> <li><a href="llogon.php?webtag=DEFAULT">Login</a></li> </ul> </div> <div class="menu navigation"><a href="lmessages.php?webtag=DEFAULT&msg=41876.1">1–10</a><a href="lmessages.php?webtag=DEFAULT&msg=41876.11">11–17</a></div> <div id="page_content"> <h3 class="thread_title"><a href="index.php?webtag=DEFAULT&amp;msg=41876.1">Excel-me-do</a> </h3> <div id="messages" data-navigation="41876_1_17_10"> <a name="a41876_1"></a><div class="message" id="message_41876_1"> <div class="message_header"> <div class="message_from"> From: ANT_THOMAS<span class="message_time">24 Jan 2017 18:24</span> <div class="clearer"></div> </div><div class="message_to">To: ALL<span class="message_count">1 of 17</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=41876.1">41876.1</a></div> <div class="message_body"> New job, new sets of data to manipulate.<br /><br /> The proper answer to this question is to speak to the people who control the main software and tell them to make it generate the dataset in a better way, but I'll wait a while before I start making those demands.<br /><br /> So, my colleague generates a big spreadsheet with quite a lot of data (CSV format, ~8000 rows). The problem is, the software generating it adds text based page breaks/headers, because it generates the report as if it is to be printed. Colleague in question manually goes through the data and removes 5 rows at a time, every ~100 rows - once a week - crazy.<br /><br /> The page breaks are a very regular format, and it took me 5 minutes to very nearly solves his problem with filters. Unfortunately it's not so simple.<br /><br /> The rows being removed are basically as follows in column A<br /><br /> Row 1 - (blank)<br /> Row 2 - Regular text 1<br /> Row 3 - Regular text 2<br /> Row 4 - Regular text 3<br /> Row 5 - Regular text 4<br /><br /> Filtering or deleting rows 2-5 is very easy. The problem is I need to remove the Blank row without removing all other Blank rows. If I filter out Blanks that removes Blank rows which are essential to the function of the data.<br /><br /> Can anyone suggest a way to delete all 5 rows easily?<br /> My thought is to select Row 5 of the set and somehow delete that plus the previous 4 rows. My Excel and Google skills are currently coming up short.</div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=41876.1&return_msg=41876.1" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="41876.1"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a41876_2"></a><div class="message" id="message_41876_2"> <div class="message_header"> <div class="message_from"> From: CHYRON (DSMITHHFX)<span class="message_time">24 Jan 2017 19:39</span> <div class="clearer"></div> </div><div class="message_to">To: ANT_THOMAS <span><span class="image post_read" title="Read: 24 Jan 2017 19:52"></span></span> <span class="message_count">2 of 17</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=41876.2">41876.2</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=41876.1#a41876_1" target="_self">41876.1</a></div> <div class="message_body"> Do want to filter it out or delete after the fact?<br /><br /> If it's the latter you could run a regex on the csv text file?</div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=41876.2&return_msg=41876.1" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="41876.2"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a41876_3"></a><div class="message" id="message_41876_3"> <div class="message_header"> <div class="message_from"> From: ANT_THOMAS<span class="message_time">24 Jan 2017 19:55</span> <div class="clearer"></div> </div><div class="message_to">To: CHYRON (DSMITHHFX) <span><span class="image post_read" title="Read: 25 Jan 2017 01:21"></span></span> <span class="message_count">3 of 17</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=41876.3">41876.3</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=41876.1#a41876_2" target="_self">41876.2</a></div> <div class="message_body"> I want to remove those matching rows. I was able to filter out the 4 text filled rows, then copy the rest of the data into a new sheet. Or just delete them from the original data. Just unable to get rid of the blank row without removing all blank rows elsewhere. <br /> <br /> Regex on the csv file is a good suggestion, rather than editing in Excel.<div class="edit_text">EDITED: 24 Jan 2017 19:57 by ANT_THOMAS</div> </div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=41876.3&return_msg=41876.1" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="41876.3"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a41876_4"></a><div class="message" id="message_41876_4"> <div class="message_header"> <div class="message_from"> From: Peter (BOUGHTONP)<span class="message_time">24 Jan 2017 23:20</span> <div class="clearer"></div> </div><div class="message_to">To: ANT_THOMAS <span><span class="image post_read" title="Read: 24 Jan 2017 23:38"></span></span> <span class="message_count">4 of 17</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=41876.4">41876.4</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=41876.1#a41876_1" target="_self">41876.1</a></div> <div class="message_body"> <i>> ... but I'll wait a while before I start making those demands.</i> <p>Every time I've decided "I'll wait till I'm less new / more settled / etc" I've later regretted it.</p></div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=41876.4&return_msg=41876.1" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="41876.4"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a41876_5"></a><div class="message" id="message_41876_5"> <div class="message_header"> <div class="message_from"> From: ANT_THOMAS<span class="message_time">24 Jan 2017 23:40</span> <div class="clearer"></div> </div><div class="message_to">To: Peter (BOUGHTONP) <span><span class="image post_read" title="Read: 25 Jan 2017 22:44"></span></span> <span class="message_count">5 of 17</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=41876.5">41876.5</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=41876.1#a41876_4" target="_self">41876.4</a></div> <div class="message_body"> You make a good point. I have only been in the job for 2 weeks, and I should be having some formal training on the MRP package in question in a few weeks so I'll definitely bring it up then. In the meantime I'll look at a regex option. </div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=41876.5&return_msg=41876.1" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="41876.5"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a41876_6"></a><div class="message" id="message_41876_6"> <div class="message_header"> <div class="message_from"> From: koswix<span class="message_time">25 Jan 2017 00:31</span> <div class="clearer"></div> </div><div class="message_to">To: ANT_THOMAS <span><span class="image post_read" title="Read: 25 Jan 2017 06:39"></span></span> <span class="message_count">6 of 17</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=41876.6">41876.6</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=41876.1#a41876_5" target="_self">41876.5</a></div> <div class="message_body"> MRP <span class="emoticon e_happycry" title=":'D"><span class="e__">:'D</span></span><div class="edit_text">EDITED: 25 Jan 2017 00:32 by KOSWIX</div> </div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=41876.6&return_msg=41876.1" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="41876.6"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a41876_7"></a><div class="message" id="message_41876_7"> <div class="message_header"> <div class="message_from"> From: ANT_THOMAS<span class="message_time">25 Jan 2017 14:58</span> <div class="clearer"></div> </div><div class="message_to">To: ALL<span class="message_count">7 of 17</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=41876.7">41876.7</a></div> <div class="message_body"> Fixed. I made it so all the blank rows were no longer essential. Converted some other data that wasn't required to blank, played with the set in stone formula and after a filter, a few deletions, pasting a new formula, we now have a way to save plenty of time.<br /> <br /> <img src="http://marcgg.com/assets/blog/automation-win.png" alt="automation-win.png" /></div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=41876.7&return_msg=41876.1" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="41876.7"> <span class="rating">+1/1</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a41876_8"></a><div class="message" id="message_41876_8"> <div class="message_header"> <div class="message_from"> From: ANT_THOMAS<span class="message_time">25 Jan 2017 14:59</span> <div class="clearer"></div> </div><div class="message_to">To: koswix <span><span class="image post_read" title="Read: 25 Jan 2017 16:44"></span></span> <span class="message_count">8 of 17</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=41876.8">41876.8</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=41876.1#a41876_6" target="_self">41876.6</a></div> <div class="message_body"> We have 4 systems:<br /> MRP<br /> ERP <br /> Warehouse/stock management <br /> Invoice management <br /> <br /> ......!</div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=41876.8&return_msg=41876.1" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="41876.8"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a41876_9"></a><div class="message" id="message_41876_9"> <div class="message_header"> <div class="message_from"> From: koswix<span class="message_time">25 Jan 2017 16:46</span> <div class="clearer"></div> </div><div class="message_to">To: ANT_THOMAS <span><span class="image post_read" title="Read: 25 Jan 2017 17:58"></span></span> <span class="message_count">9 of 17</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=41876.9">41876.9</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=41876.1#a41876_8" target="_self">41876.8</a></div> <div class="message_body"> Had to do a course in 4th year on supply chain management. Essentially it was a course on "look at how these companies all fucked themselves by trying to implement erp systems <span class="emoticon e_happycry" title=":'D"><span class="e__">:'D</span></span> " <div class="edit_text">EDITED: 25 Jan 2017 16:46 by KOSWIX</div> </div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=41876.9&return_msg=41876.1" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="41876.9"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a41876_10"></a><div class="message" id="message_41876_10"> <div class="message_header"> <div class="message_from"> From: koswix<span class="message_time">25 Jan 2017 16:51</span> <div class="clearer"></div> </div><div class="message_to">To: ANT_THOMAS <span><span class="image post_read" title="Read: 25 Jan 2017 17:58"></span></span> <span class="message_count">10 of 17</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=41876.10">41876.10</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=41876.1#a41876_8" target="_self">41876.8</a></div> <div class="message_body"> Wait, why are they running all those systems? I thought the whole point of erp was to do all that shit in one place? </div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=41876.10&return_msg=41876.1" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="41876.10"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div></div> <div class="message_page_footer"> <ul> <li class="right_col"><form accept-charset="utf-8" method="get" action="lmessages.php" target="_self"><input type="hidden" name="webtag" id="webtag" class="bhinputtext" value="DEFAULT" dir="ltr" /><input type="hidden" name="msg" id="msg" class="bhinputtext" value="41876.11" dir="ltr" /><input type="submit" name="submit" id="keep_reading" value="Keep reading…" class="button" /></form></li> </ul> </div> </div> </body>