• 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...

  • 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

C

Chirag R Raval

Member
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
 

Attachments

  • INSERT MAIN RECORDS BELOW CORRESPONDING DATA IN MAIN RACORD.xlsx
    34.8 KB · Views: 2
Demonstrate what the output should look like. Is it simple concatenation of 2 cells? Or should it be new line within the cell etc.
 
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
 

Attachments

  • PLEASE REFER SHEET 2- INSERT MAIN RECORDS BELOW CORRESPONDING DATA IN MAIN RACORD.xlsx
    36.8 KB · Views: 3
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
 
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
 

Attachments

  • CONCATENATE BELOW CORRESPONDING WITH MAIN RECORD+SHADE COUNT+TOTAL UNITS.XLSX
    37.8 KB · Views: 5
Last edited:
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
 
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
 
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
 
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
 

Attachments

  • PARTY BOOKING.xlsx
    27.6 KB · Views: 1
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 

Attachments

  • PARTY BOOKING.xlsm
    48.4 KB · Views: 3
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.
 
Hi ,

Thanks for the feedback.

When you say two more points , are they as described in the following :
(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 ..
Click to expand...
Narayan
 
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
 
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
 
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
 
You must log in or register to reply here.
Back
Top