1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to Merge & insert below records in main record?-How Many records below main, not fixed-dynamic

Discussion in 'VBA Macros' started by Chirag R Raval, Jul 28, 2017.

  1. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear All,

    Subject:-
    How to Merge & insert below records in main record?-How Many records below main, not fixed-dynamic..


    Please refer attached an excel file...require to merge below all records with correspondence main records..to make continuation for requirement each main records must be present in 1 time only to properly subtotal ..on any column...(in other words remove problem of Blank rows with maintain record..)

    there are many this type of files generate daily from other system & repair
    (Cut-Paste below shade/unite in main record ) manually is huge time consuming & also effect on accuracy...

    need your help for some code to resolve this problem. Forever..

    & advance thanks for focus on this thread..

    Regards,
    Chirag Raval

    Attached Files:

  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,914
    Demonstrate what the output should look like. Is it simple concatenation of 2 cells? Or should it be new line within the cell etc.
  3. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear Sir,

    yes...Is it simple concatenation of 2 Or 3 cells...(Concatenate till that Sr no's End).. in short require unique record in single line & there are should Not blank line ...in whole data for just sorting & subtotaling on any heading ..

    i also attached same file with requirement in sheet 2

    Many thanks &Hope Your Co-Operation.

    Regards,
    Chirag Raval
  4. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear sir,

    There are also need to insert 2 columns
    After shade/Units heading column...
    1 should have total ordered shades & 2nd have total ordered units.

    Example :- in Shade/ Units column..5/3 means order punched for Shade no 5 & units ordered for that shade is 3.
    1 material /quality can many ordered Shade/Units.. So there are need to put columns...

    Can code run till Sr. No's end? If last 2lines deleted (which contain-Words "Total" ) which make problem for sort & sbtotal..
    If deleted.....So we can add functionality of sorting on heading "Dp" & then "Quality" (Material)..& can Dp wise Subtotal of coluns
    Total shades ordered ,total units ordered, total qty ordered..
    Hope ...due to you can help ..
    Again many thanks for your effort to understand & solve my problem..till now

    Regards,

    Chirag Raval
  5. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear sir,

    Can you focus on this?

    Regards,
    Chirag Raval
  6. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear Sir,

    Re-Attached with full requirement as demo..(Sample) in sheet 2..for understand..situation please also compare sheet 2 with sheet 1 ....sheet1, which originally daily received / generated many files from other system.

    hope someone can focus & resolve this problem cum requirement..

    Regards,
    Chirag Raval
    Last edited: Aug 1, 2017
  7. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    So far you have uploaded 3 differently named workbooks ; which one is to be used to resolve your problem / satisfy your requirements ?

    Secondly , what is your first requirement ? Can you clearly and comprehensively describe this ?

    Narayan
  8. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear Sir,

    Last attached is final.

    please refer sheet 2 example & requirement / problem there...all requirement in yellow highlighting...

    Sr 232 falls 3 line it should be 1 record-1 line so below 2's all shade/units...
    transfer in sr 232 ...& that 2 (now blank) lines should be deleted ...means sr 232 have occurrence only 1 time in whole sheet...

    2 columns should be insert after Shade/Unit columns (heading generate by code)

    for that 2 columns

    first blank (after shade/unit) should total number of shades (count) of that record (total counts of occurrence of shade/units .. (like 8/3......9/2......means 2 shades ordered (2 times shades occur) so total orders shades are 2)

    2nd blank columns should have total units (total of number after "/" )
    8/3......9/2.... after "/" shade' no 8 ordered units is 3 & shade no 9 ordered units is 2 ..total 5 units orderd...


    when report end.. delete all rows below last sr no...

    that's it..

    Regards,

    Chirag Raval
  9. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear Sir,

    Sheet 2 have just sample of requirement...
    that types data generated from other system...(sheet1) there are many instance of that type which highlighted in yellow...& there are many files generated daily...

    this is just party's ordered report only...party give order of units of particular shades..this report generate from other Order Entry System...

    if party ask us to "Please give me report as Month Wise, of our booked order"
    our work make hard to manually transfer this fallen shade/units from main record to Re-transfer that all shades is main record..

    party some time demand " how many shade & how many units also should be mentioned against each entry "

    so there are more harder our work...

    Each sr no have maxim,um 5 shade/unit entry, if more then it...its falls
    below as continuation..of that sr no..if we sort on any heading whole data
    become chaos..

    it is understandable that each sr have all shade/units without falls below...no matter how make long that record...

    so we can sort on any heading & subtotal on any column...

    just it ....concept is simple ....


    Regards,

    Chirag Raval
  10. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    Can you upload a workbook which has as much data as possible ? One or two rows of data is not adequate.

    Narayan
  11. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear sir,

    Thanks for your effort..

    Though , above last attached file's sheet no 1 is original data,
    Attached 1 another party's ordered generated from order entry system as excel file as per your requirement ..

    Regards,

    Chirag Raval

    Attached Files:

  12. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear Sir,

    You found that some serial numbers appear more then 1 times...
    due to party give order for same quality-shade in variant delivery periods
    so that record must be preserve...keep ..not be deleted ..but if that serial's
    ordered unit shades goes beyond 5 instance, that falls in next line...requirement is just fallen shades transfer in main line ...

    so actually Serial Nos not effect on our requirement ...

    request to help ..

    Regards,
    Chirag Raval
  13. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    You will have to explain everything starting from scratch.

    1. Which columns are relevant , and which need not be considered ?

    Use the column letters to explain.

    2. Certain rows have the same identical data in columns B , C and D ; for example rows 3 and 4 , rows 5 and 6 , rows 7 and 8 , rows 13 and 14 and so on. What is the significance of this , and what should be done with such rows ?

    3. Certain rows spill over 1 row ; for example rows 70 and 71 , rows 124 and 125 , rows 126 and 127 and so on. What is the significance of this , and what should be done with such rows ?

    4. Certain rows have only one entry in column E , while other rows have multiple entries ; what is the significance of this , and what should be done with each type of row ?

    Narayan
  14. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear sir,

    Thanks for your reply..
    I will try to Re-Explain from starting..

    Regards,

    Chirag Raval
  15. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear Sir,

    forget every thing...

    please refer above attached list file..

    require loop code...

    (1) loop on column "A" -start from "A2" till last cell..
    (2) found first blank on column "A" , from blank .offset right 4 (on column "E")
    (3) cut everything on that cell & paste it just above cell after that cells data

    (example-first blank found on "A71" -cut "E71"'s all data on above cell "E70" ("E70"'s data should remains -means paste after "E70"'s data this is can be called concatenate..."E71 & E70)

    (4) now just delete entire row from which we cut the data --can be say first found blank cell's entire row should be delete...
    (5) jut it..

    now re-turn on "A2" , & do above process till last cell till number found on cell

    (6) after above all process if found word "Total Qty"--delete that entire row...
    (7) if found "Total Value"-delete that entire row..


    this is first requirement of these thread ....if these completed then...

    (1) insert 2 blank columns after column "F"
    (2) Cell "G2" 's text (heading) -- should be "Total shade"
    (3) Columns "H2" 's text (heading) should be "Total Units"
    (4) "G3" have total count -of pairs in cell "E3"
    (count of instance of pair like 1/1......2/1......6/1......7/1......
    total 4 pair means total 4 shaded ordered ....
    fill this column with total --of every "cell of "E "till data end...
    (6) Cell h3 have total of value (number) found after "/"
    in every instance found in cell "E3"

    example in cell "E3" 1/1......2/1......6/1......7/1......
    number 1 found after "/" in first instance ...
    number 1 found after "/" in second instance

    in short ...cell "H3" have sum of numbers found after "/" on "E3". till data end.

    (7) ascending sort of column "F" then "B"
    (8) subtotal of columns "G", "H" & "I" at bottom ..

    that's it.

    Regards,
    Chirag Raval
  16. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    Thanks for the detailed explanation.

    Taking the first point, starting from A2 , we check whether any cell in column A is blank.

    The first such cell is A71.

    My first question is , when a cell in column A is blank , what will the other cells in columns B through H contain ?

    In row 71 , only E contains some data , but will this be the case every time ?

    Once a blank cell in column A is found , you want that the data in column E for that row should be merged with the data in column E of the previous row.

    In this case , E70 has the data : 10/1.....1/1......13/1.....3/1......6/1......
    E71 has the data : 7/1......9/1......

    After the merge has been done , E70 will contain :

    10/1.....1/1......13/1.....3/1......6/1......7/1......9/1......

    Is this correct ?

    Does this mean that after every merge of this kind , only the data in column E will be changed ; in the present example , all other data in cells A70 , B70 , C70 , D70 , F70 , G70 and H70 will remain unchanged ; is this correct ?

    Narayan
  17. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear Sir,

    Thanks ..correct...

    Perfect catch.

    Its just merging-concatenating process ...

    answer...
    "A70 , B70 , C70 , D70 , F70 , G70 and H70 will remain unchanged ; is this correct ?"

    yes because they are blanks.... & only "E" have data...and after data transfer to above cell whole line is become blank..that will be deleted..


    if "E"s cell data transfer in above cell, E become whole row empty
    because there are only "E" have data in it...
    and after transfer above...we don't need blank line...blank cell , blank block in whole database...
    ...so we can do something on that perfect range ...

    Regards,
    Chirag Ravel
  18. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    OK. Thanks for the clarification. I will upload the code to do this first point in a couple of hours.

    Once you confirm that this point has been resolved , we can go on to the next point.

    Narayan
  19. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear Sir,

    1 point....precaution.. need...

    its required? just for understanding

    loop must retune on "A2" after its 1st work of data merge above & delete the 1 blank row ..& re-start finding another blank...because you can see in above last attached excel file..Excel's Row No 249 & 250 ..(data sheet's serial no 235's below 2 blanks ) in column "A"...that both cell's opposite (row now "E249" & "E250" that should also merge in row no "E248") become may be another file have 3 or 4 blanks ..

    example-loop found row "A249" blnak....it merge E249 in E248...delete E249 & shift entire below database 1 step up..

    so now remains 1 blank after "A248" that now "E249"...

    if loop return on "A2" and then start found blanks its face " now A249" blank
    & all data (contain (Shade/Units) 7/1 ) of "E249" transfer to above "E248"..thats already -naturally part of database's serial no 235... & after transfer that to up.. delete entire row no E249... so now there are no blanks
    between "E248" & "E249" (database become continue E249 have data's serial no 236 ..

    please clarify my doubt..code just do that or anything else..?


    Regards,
    Chirag Raval
  20. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    See if this works. Run the macro named ProcessDataAndMerge.

    I have kept a copy of the original Sheet1 , so that we can run the macro more than once.

    Narayan

    Attached Files:

    Chirag R Raval likes this.
  21. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear Sir,

    Perfect.. As desired..Amazing....Great...Help...Great Code...
    many thanks for your efforts for me ..you help me amazing in my work...
    because manually prepare report for 1 party take huge time & there are many report generated daily..by me .you can understand literal manual hard work I do ..

    but now...after your help.. I can just prepare reports in seconds..

    Hope you can help me as per your post no 18 for more 2 points...

    Regards

    Chirag Raval.
  22. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    Thanks for the feedback.

    When you say two more points , are they as described in the following :
    Narayan
  23. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    If you look at the rows 179 and 180 after the merge macro has been run , their contents will be as follows :

    401/1....406/1....167/1....318/1....408/1....8/3......9/3......

    8/5......9/10.....

    What will be the contents of the cells G179 and H179 ?

    Narayan
  24. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear Sir,

    That's not matter... because after insert 2 blank new columns after column "E"
    and current columns (before insert) shifted right side (after insert )...they also should be remain in report...

    after insert 2 blanks , "F" will have count of instance & "G" will have total ordered units..

    after insert 2 blanks heading column "Qty" (columns "G" ) become column no "I") that should have subtotal of "Qty" on bottom ...

    Regards,

    Chirag Raval
  25. Chirag R Raval

    Chirag R Raval Member

    Messages:
    398
    Dear Sir,

    Actually "What will be the contents of the cells G179 and H179 ?"
    not any relation to our newly inserted columns ..it will be shift right side ..

    Regards,
    Chirag Raval

Share This Page