+ Reply to Thread
Results 1 to 12 of 12

Data Merging & Distributions

  1. #1
    Registered User
    Join Date
    07-22-2016
    Location
    Cordova
    MS-Off Ver
    2007
    Posts
    44

    Data Merging & Distributions

    Hi Guys ,
    I have a workbook consist of 71 worksheet ,

    In the first column of each worksheet i have a unique serial number that duplicate for all rows in the same sheet

    In the second column of each worksheet i have a date which take from like 12/31/2017

    Now i need to delete all rows from all worksheet that contain these dates in second column
    "3/15/2017" "3/27/2017" "5/17/2017" "9/13/2017" "12/5/2017"


    As i said before each worksheet has unique serial number in first column EXCEPT the last worksheet "sheet 71"

    "sheet 71" contain a mix of all unique numbers that exist in all other worksheet , now i would like to distribute the content of "sheet 71" to other sheets depending on Unique Serial Number

    So matched Unique Serial Number will be the key factor for distibutions and merging data , How can i do that in a quick way ?

    Thanks

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Data Merging & Distributions

    Q1 - does every row always have a date in second column? (Are there any empty cells in that column?)
    Q2 - which is the last column containing data
    Q3 - will sheet 71 be empty after macro is run?
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Registered User
    Join Date
    07-22-2016
    Location
    Cordova
    MS-Off Ver
    2007
    Posts
    44

    Re: Data Merging & Distributions

    A1 - Yes all rows in each worksheet have a date , and there is no empty cell in Second column
    A2 - For all worksheet , Column L is the last Column
    A3 - Yes its preferred to be empty but it's only one sheet and i can do this manually so , no problem with that issue

    Thanks for your response Kev

  4. #4
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Data Merging & Distributions

    Thank you for answering my questions - I will post a solution tomorrow (UK)

  5. #5
    Registered User
    Join Date
    07-22-2016
    Location
    Cordova
    MS-Off Ver
    2007
    Posts
    44

    Re: Data Merging & Distributions

    I really appreciate your help in resolving the problem , and i will check out your answer tomorrow , Have a good night

  6. #6
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Data Merging & Distributions

    I have ignored the 2nd half of the problem for the moment
    - dates cause a few problems
    - the 2nd half is easily resolved if you can get the first bit to work!

    Test in attached workbook first
    - amend dates in sheet "DatesToRemove"
    - run with {CTRL} K
    - check sheet "RowsDeleted" to see if OK

    For testing:
    - inserted sheet "RowsDeleted" where deleted rows are dumped
    - makes it easier to see what is going on
    - delete the sheet and the relevant bits of code after testing
    - Test Data comprises 5 sheets + Sheet "71" + Sheets for entering "DatesToRemove"
    - rows conditionally formatted to make it easier to see the matches

    Then test in a copy of your workbook
    - let me know how you get on

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-22-2016
    Location
    Cordova
    MS-Off Ver
    2007
    Posts
    44

    Re: Data Merging & Distributions

    The result for my first request is Awesome and Perfect .. it works fine and all deleted rows have been moved to "RowsDeleted" sheet .. now what's the next step to distribute rows of "sheet 71" to other worksheet ?

  8. #8
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Data Merging & Distributions

    Did you test it on your workbook?
    Did you need to modify anything to make it work?

    I will add code to answer your 2nd question tomorrow

  9. #9
    Registered User
    Join Date
    07-22-2016
    Location
    Cordova
    MS-Off Ver
    2007
    Posts
    44

    Re: Data Merging & Distributions

    Yes , I did test on my workbook and i did not expect that it would work from the first time , but the result was so good so there's no need to modify or add anything else except to thank you again
    I will wait your answer for 2nd question .. I am not in a hurry of my matter
    Last edited by Sasha 99; 01-21-2018 at 08:28 PM.

  10. #10
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Data Merging & Distributions

    That is useful, I am busy on something else today.

  11. #11
    Registered User
    Join Date
    07-22-2016
    Location
    Cordova
    MS-Off Ver
    2007
    Posts
    44

    Re: Data Merging & Distributions

    OK , take your time and i will post the second half of problem in new thread after marking this as solved
    Thanks

  12. #12
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Data Merging & Distributions

    You jumped the gun a bit there

    The solution to the 2nd problem has been posted on your other thread

+ 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. Replies: 1
    Last Post: 11-02-2017, 09:20 PM
  2. Replies: 0
    Last Post: 05-28-2014, 12:50 PM
  3. Merging: displaying which sheet data comes from when merging sheets
    By zed commander in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-12-2012, 12:20 PM
  4. Replies: 4
    Last Post: 03-26-2012, 03:48 AM
  5. Split data range into four distributions
    By jeffreybrown in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 05-29-2011, 08:45 AM
  6. problems with distributions
    By cbrochu2 in forum Excel General
    Replies: 1
    Last Post: 02-04-2010, 06:27 PM
  7. [SOLVED] Using RAND() with Different Distributions!?
    By bj in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-06-2005, 06:05 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