Formula to allocate name and count in col 'F'.

[visha_1984]

Hi Forum team,

I have just working on worksheet i need formula to Drag Allocation in DATA file Cp and Qty wise in col 'F' and insert Blank row after Cp name in col F.

I have attached sample data file. any help Greatly appriciate.

Thanks in advance.

[:) Sixthsense :)]

Based on your data size it is better to go for VBA solution.

Please confirm whether VBA solution is ok for you?

[visha_1984]

Based on your data size it is better to go for VBA solution.

Please confirm whether VBA solution is ok for you?

Hi,Sixthsense

Ok no problem .

[:) Sixthsense :)]

Try the below code….


How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

Please Login or Register  to view this content.

[visha_1984]

Hi,Sixthsense

code Allocate cp in DATA sheet correctly but need to insert row after cp name for whole row ,and one more thing after allocation code drag again cp name.

[:) Sixthsense :)]

The provided code working fine for in the file which you have attached in Post #1

[visha_1984]

The provided code working fine for in the file which you have attached in Post #1

Ok got it Sixthsense Thank u very much.

[:) Sixthsense :)]

Glad it helps you and thanks for the feedback and rep

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[visha_1984]

Hey Sixthense,

one more Q if i want to start new allocation from row number 'F3414' is it possible for next time.

[:) Sixthsense :)]

No.. lot of complication will occur since the code was written based on your initial requirement stated in post #1.

[visha_1984]

Ok No problem Thanks once again.

[:) Sixthsense :)]

If needed then let me know I will modify the code to suit your requirement.

[visha_1984]

Hi,Sixthsense
I need it because i received daily allocation 15-16 times, but if it is possible then modify it.

Good day.

[:) Sixthsense :)]

Just confirm me whether each time the data can be populated in new sheet? or it should be updated in the end of the Output Sheet?

[visha_1984]

It should be update in the end of Output sheet.

[:) Sixthsense :)]

Try the below code...

You have to select the Data range and Allocation Range Manually. Made it user defined range so than you can select your desired range on which the macro needs to be performed.

Please Login or Register  to view this content.

[visha_1984]

Great Sixthsense,

It works fantastic thanku very much.
U save My 3 workinghours per day.

cheers.'''.

All the best.

[:) Sixthsense :)]

Glad it helps you and once again thanks for the feedback

[visha_1984]

Hi Seixthsense,

I have one issue while alocating cp allocation,If in DATA sheet there is 2 or 3 Break in all col then what should i do and code allocating '-1' qty in output folder.
pls suggest

[:) Sixthsense :)]

Please show a sample workbook to know in which circumstances the provided code is getting failed and for my testing purpose too...

[visha_1984]

Please show a sample workbook to know in which circumstances the provided code is getting failed and for my testing purpose too...

attached sample workbook.

[:) Sixthsense :)]

In your data sheet select Column-A and press Ctrl+G>>Special>>Select Blanks>>Click Ok>>Press Ctrl+ - button>>Select Entire Row>>Click Ok...

Now run the provided code.

[visha_1984]

Hi Sixthsense,

Thanks for your reply
but i dont want to delete entire row ,if there is blank row then allocate CP 2 time in output sheet

in my attachment for exp- if CHACHA ENTERPRISES-PUNE_394780 - 70
then first drag 21 then 49 qty

pls check attachment for your ref

[:) Sixthsense :)]

Sorry I am not interested to do the amendments which you ask in separate intervals.

You have to manage the little things. Just duplicating the sheet and deleting the blanks is not a big task and somethings you can do it manually.

Sorry to say that I can't provide support further for this post and I hope someone will take care of your new requirements.

[visha_1984]

Hi Sixthsense,

pls help if i select range manually for second lot code should drag from selected range, but i macro code draging another range.

pls help me out i have attached sample workbook.

[:) Sixthsense :)]

Oopsss.... Just replace the below line

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

[visha_1984]

Thanks once again Sixthsense.
Good Day.