+ Reply to Thread
Results 1 to 13 of 13

Multi sheet horizontal cutter

  1. #1
    Forum Contributor
    Join Date
    01-26-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    308

    Multi sheet horizontal cutter

    Hi guys,

    Complete VBA novice here! Just wondered if the following is possible.

    I'm looking to delete every data point which falls outside 00:05:00 - 00:00:00 (Column B) across multiple sheets (all black coloured data gets deleted). Also if there are two equal times (i.e. 143342 01:03, 652143 01:03, the value with the highest figure in A gets deleted).

    Thanks
    Mdn
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multi sheet horizontal cutter

    Hi Mdn,
    Sure it's possible, (almost) everything is possible with VBA but the problem is you first sentence under the Hi guys,
    We can start explaining but that will abracadabra to you and any modifications or exceptions means you'll be completely dependent of some guy across the www to help you at all times.

    All you need is a macro that starts at the last row and loops back until row 1 and on the way up if checks if the value in column B answers to the criteria to be deleted.

    You have to do this in reverse order because the row counter would get messed up if a row ' dissappears'

    the vba code would look something like this

    Please Login or Register  to view this content.
    But it's not tested , I'll see if I can find something and of course you will have to 'tell' the macro to process all the worksheets where applicable.

    My first tip: start learning VBA there many sites with tutorials and code samples
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multi sheet horizontal cutter

    I've read it a little better. you said "every data point which falls outside 00:05:00 - 00:00:00" and you have a series or red marked rows.

    But there are rows in black that have the same value in B which are NOT red?

    How's that for logic?

  4. #4
    Forum Contributor
    Join Date
    01-26-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    308

    Re: Multi sheet horizontal cutter

    Really appreciated Keeb; it's gonna be a steep learning curve for sure!

    As for the logic & repeating values. That's absolutely right, & that's why the macro would need an extra "step" (i.e. column A) to delete the correct duplicate. It's actually a fault in how the data is/was originally captured - hence why I'm trying to delete it

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multi sheet horizontal cutter

    Well, then the fact that you colored a number of rows red was incomplete, just a pick, well,
    Since you haven't given the whole picture, here comes more:
    - May I assume that there more columns in these worksheet besides the two you display?
    - Header row?
    - May the output be sorted in ascending time order and ascending value order? This means that all the data in that sheet will be sorted.
    In this manner you will have all the time values together and the values next to it in ascending order too, so then it's quite simple, you delete all for the same timestamp except the first. and if the value in the column next to the time is equal to the next one, one will be deleted always leaving just one value and timestamp where the value is the lowest of the same array.

    Would Appreciate more concise information I hate assumptions

  6. #6
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multi sheet horizontal cutter

    This macro will take care of it in the situation as you presented it.

    The macros is named 'SortDataMarkAndRemoveUnwanted'
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    01-26-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    308

    Re: Multi sheet horizontal cutter

    Yes you're right again I cut massive chunks of the data out leaving just the first two rows. The main reason I cut the orignal data, is because there are many more steps!

    However looking back, the form of the original data is quite important as there are headers (but the start is always consistent - begins @ row 8 (very imporant)), & I imagine the macro could effect them. Also there are some extra stages

    Below I've added the proper spreadsheet, Columns D & E are the ones that are designed for the macro

    Massively appreciated!
    Attached Files Attached Files

  8. #8
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multi sheet horizontal cutter

    Does this mean that sorting the data from row 8 down is no option?
    May the entire row be deleted?
    Is Row 8 valid for every worksheet?

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multi sheet horizontal cutter

    Another question, are Column B or Column F really Empty in your worksheets?
    This could come i handy as helper column.

  10. #10
    Forum Contributor
    Join Date
    01-26-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    308

    Re: Multi sheet horizontal cutter

    What I meant was I didn't want it sorting stuff above row 8.

    Row 8 is where the "genuine data" begins. Above this, are the headers etc

    Yes they are empty in all the worksheets, another aspect that would be useful. Looking across row 3 - if a 0 is present, the collumns surrounding it can be deleted also (looking down the columns are just errors & 0's).

    VSaCNDd5RL2c110liYt7vQ.png

    There's also third aspect to the deletion

    Every column that contains "L" or "LTP" in row 4 can be completely deleted

    I think you'd be my new best friend Keeb!!!

  11. #11
    Forum Contributor
    Join Date
    01-26-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    308

    Re: Multi sheet horizontal cutter

    I've added the exemplar version below
    Attached Files Attached Files

  12. #12
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multi sheet horizontal cutter

    This is the previous Cutter(data) with macro only one sheet, Sheet (2) is backup
    Press the green arrow
    Attached Files Attached Files

  13. #13
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multi sheet horizontal cutter

    Hi Simon,

    Minor modifications to the macro and the columns are gone too.
    See if this works for you.
    The Status Bar shows the progress while the macro is running.

    The end result is as per your Exemplar.xlsx
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Writing the loop for updating multi-table MS access file using multi-sheet excel
    By relabz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-29-2016, 03:10 PM
  2. Replies: 0
    Last Post: 11-09-2014, 02:08 AM
  3. Horizontal multi data 12 month rolling chart, multiple sheets.
    By allstar51788 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-29-2012, 08:53 AM
  4. Multi-Sheet, Multi-Table Vlookup possible?
    By cte in forum Excel General
    Replies: 6
    Last Post: 09-11-2010, 11:18 AM
  5. How to create a Multi Sheet WB from filtered Multi Sheet WB
    By CRIMEDOG in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-01-2010, 01:26 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1