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

Repeat the cell value on the basis of line items available.

Status
Not open for further replies.
Sanoj

Sanoj

Member
Hi All,

I am seriously stuck up with a macro coding. I have attached a sample file where in the Template sheet is the input which I receive.
Where From Column C and onward there is a code (here its from C2, D2, E2 & F2).
This codes should get pasted on a new output sheet from A2 under the heading GL, number of times we have text in input file A3 onwards (hence put formula in cell E1 of count if =COUNTIF(A3:A10000,"*"), and should repeat the step for C2, D2, E2 & F2 and so on if text exists.

Then in sheet out put it should also paste the respective GL values as is.

For the reference I have attached a sample sheet, I thought is would be an easy task for me but now I am like banging my head :confused:.

Somebody please help me out with this coding task.
 

Attachments

  • Template.xlsx
    16.1 KB · Views: 11
Explain about Template & output sheet data.
>In Output sheet : How many time GL code required. (I think all four GL code required each 37 (37*4=148) times.
>column "B", "C", "E" "F" is blank
>Column "D" Amount : mentioned amount pull from where?
>Column "G" "H" "I" : Data pull from where?
>what is co-relation between
Amount Text Cost Center WBS
 
Thanks for your response, AVK answering to your queries:

>In Output sheet : How many time GL code required. (I think all four GL code required each 37 (37*4=148) times. - Yes but 37 is not constant and here its 4 GL may lead to 6 sometimes.

>column "B", "C", "E" "F" is blank - Yes, in output sheet it should always be blank

>Column "D" Amount : mentioned amount pull from where? - Its from the input sheet for C2 (eg. 613560 all amounts should be pasted as is C3 to C39 and for 606150 its D3 to D39 and so on....)

>Column "G" "H" "I" : Data pull from where? - its combination of text from input file LEASEPLAN_CH439GK_03/2017 is formula =B1&"_"&A3&"_"&D1 that is Cell B1 which is a list drop down its fixed + Immatriculation col A+ col D1 date.

>what is co-relation between amount Text Cost Center WBS - There is no co-relation but if the Col B text contains "." in input sheet then it should reflect in col I in output sheet else should come on Col H.

Please let me know if I have made all your queries clear if not let me know will try to explain if required.
 
Maybe,

In "Output" Sheet E2, formula copy down :

=IFERROR(INDEX(Template!$C$2:$F$2,,INT((ROWS($1:1)-1)/Template!$E$1)+1),"")

Regards
Bosco
 
@bosco_yip

No in "Output" Sheet E col should always be blank. Where I am stuck up with is output sheets col A & D, rest I believe could manage.
 
Last edited:
Hi !

I find no logic between Output and Template worksheets !

So no arm, no chocolate ! (Means no logic, no help …)

For example in Output!D2 where come numbers within formula ?
From Hell ?‼

An accurate Output worksheet according to Template worksheet
is necessary for trying to foresee your need …
Marc Hell
​
 
Marc L said:
Hi !

I find no logic between Output and Template worksheets !

So no arm, no chocolate ! (Means no logic, no help …)

For example in Output!D2 where come numbers within formula ?
From Hell ?‼

An accurate Output worksheet according to Template worksheet
is necessary for trying to foresee your need …
Marc Hell
​
Click to expand...

No not at all from Hell :) the value of output!D2 is from the Template!C3:C39 which was for the header 613560 in template!c2
similarly for Template!D2 repeated the value in Output! as many times as the line item is available in Template file and copied the entire value of D2 and so on...

Anyhow, I just wanted to know how we could repeat the Template!C2, D2, E2 and F2 as many times as the number is mentioned in Template!E1 in another sheet Output! A2 one after another.

So, now where is the chocolate???? :cool:

Rgds,
Sanoj
 
Last edited:
bosco_yip said:
Maybe,

In "Output" Sheet E2, formula copy down :

=IFERROR(INDEX(Template!$C$2:$F$2,,INT((ROWS($1:1)-1)/Template!$E$1)+1),"")

Regards
Bosco
Click to expand...
No in "Output" Sheet E col should always be blank. Where I am stuck up with is output sheets col A & D, rest I believe could manage.

Rgds,
Sanoj
 
AVK said:
Explain about Template & output sheet data.
>In Output sheet : How many time GL code required. (I think all four GL code required each 37 (37*4=148) times.
>column "B", "C", "E" "F" is blank
>Column "D" Amount : mentioned amount pull from where?
>Column "G" "H" "I" : Data pull from where?
>what is co-relation between
Amount Text Cost Center WBS
Click to expand...

Thanks for your response, AVK answering to your queries:

>In Output sheet : How many time GL code required. (I think all four GL code required each 37 (37*4=148) times. - Yes but 37 is not constant and here its 4 GL may lead to 6 sometimes.

>column "B", "C", "E" "F" is blank - Yes, in output sheet it should always be blank

>Column "D" Amount : mentioned amount pull from where? - Its from the input sheet for C2 (eg. 613560 all amounts should be pasted as is C3 to C39 and for 606150 its D3 to D39 and so on....)

>Column "G" "H" "I" : Data pull from where? - its combination of text from input file LEASEPLAN_CH439GK_03/2017 is formula =B1&"_"&A3&"_"&D1 that is Cell B1 which is a list drop down its fixed + Immatriculation col A+ col D1 date.

>what is co-relation between amount Text Cost Center WBS - There is no co-relation but if the Col B text contains "." in input sheet then it should reflect in col I in output sheet else should come on Col H.

Please let me know if I have made all your queries clear if not let me know will try to explain if required.
 
Please don't quote the just preceding entire post !

Sanoj said:
the value of output!D2 is from the Template!C3:C39
Click to expand...
All range cells are equal to 0.00 in your attachment ‼

So when result worksheet is very not accurate according to source data,
help may not come, don't be surprised …

bosco, good luck !
​
 
Marc L said:
Please don't quote the just preceding entire post !


All range cells are equal to 0.00 in your attachment ‼

So when result worksheet is very not accurate according to source data,
help may not come, don't be surprised …

bosco, good luck !
​
Click to expand...
It is accurate It could be 0.00 or some other values, for example if you check D3 to D39 there are values this is how the file is.
I worked on it and its working as expected. The value should get pasted one after another in Output sheet column D.

Here is the code.

Code: 
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("Template")
Set pasteSheet = Worksheets("Working")

Sheets("Template").Select
copySheet.Range("C3:C3000").Copy
pasteSheet.Cells(Rows.count, 4).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True

copySheet.Range("D3:D3000").Copy
pasteSheet.Cells(Rows.count, 4).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True

copySheet.Range("E3:E3000").Copy
pasteSheet.Cells(Rows.count, 4).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True

copySheet.Range("F3:F3000").Copy
pasteSheet.Cells(Rows.count, 4).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True

Now only thing what I am looking for is the values should repeat for C2, D2, E2 & F2 in output sheet in column A.
 
Status
Not open for further replies.
Back
Top