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

Excel VBA for Combining Multiple Values across multiple sheets

ajoshi76

ajoshi76

Member
Hello All,

I am looking for the Excel VBA if anyone can help me get this solved. I am trying to get the Data from Form II and using the correct logic update the 3 sheets.

Sample excel attached with all data and the easy it should update the various sheets.

Any help appreciate

Thanks
Ashish
 

Attachments

  • Sample file.xlsx
    102.2 KB · Views: 12
The Logic is in the spreadsheet on how the data should be populated using Form II data and populating all the remaining spreadsheets. The data is populated for understanding the logic completely
 
Last edited by a moderator:
Let me explain it thoroughly

The Data needs to be picked up from Form II Sheet and the 3 sheets need to be updated based on the below Logic :

Impact Assessment
Form II Sheet has the Unique Role & Phase Combinations listed in Column T & U ... The Count on T12 cells denotes the count of data below it which needs to be copied.

On Impact-Complexity Assessment sheet it needs to paste these unique records from T & U column until all unique data is pulled in this case its T14:U17 based on the count 4 - This should be pasted in Column K and L on the Impact-Complexity Assessment.

Epic Id, Epic Summary, IB or PB, Category, TShirt Size, Story Points, Complexity, # of Sprints Fields from Form II needs to be updated one by one.
For the above 4 rows pick one row Row 31 from Form II and start pasting the details on the destination sheet.
Copy Application Name for all the rows this data exists .
Once this is done repeat the same steps for all the remaining Epics on Form II until Row 130 based on Count in Row B29.


Detailed Plan
The Whole Resourcing and Location Strategy needs to be pasted in respective Columns in Detailed plan.
For the above rows pick one row Row 31 from Form II and start pasting the details on the destination sheet.
PIck all. the data in the Feature Team, Sprint, and Application Name and repeat it for all the rows of the Single Epic Selected
Once this is done repeat the same steps for all the remaining Epics on Form II until Row 130 based on Count in Row B29.


Detailed Sprint Plan
Based on Column M 31 to 130 replicate the data of Detailed plan that many times based on Columns in Detailed Sprint plan sheet.



The excel has been pre-populated with sample data to understand. the things
 
As nobody answers so that's unclear.​
Maybe someone could see the light with some obvious before workbook and expected result workbook​
with your best crystal clear and complete elaboration accordingly …​
​
 
Form II - Input sheet
Expected Result - Impact-Complexity Assessment Sheet

I have given the detailed expected result with the input worksheets having sdample data filled.


Not sure what are we looking for ?
 
Let me make it more simple :

Table 1

Role DetailsPhaseEmployee Type#Location%
IT Business AnalystAnalyzeEmployee1India100%
IT BuildDesignEmployee1London25%
IT BuildDevelopmentEmployee2India100%
IT BuildDevelopmentContractor1Poland50%
Functional TesterFunctional TestingEmployee1India100%

Table 2
EPIC IDEPIC SUMMARYIB OR PBCATEGORYT-SHIRT SIZESTORY POINTSCOMPLEXITY#SPRINTS
BR-002Dummy BR-002BothData SourcingL
107​
Complex9
BR-001Dummy BR-001BothEnrichments, Calculation & ProcessingXXL
206​
Very Complex12
BR-006Dummy BR-006BothDownstreamS
28​
Medium5

Output Expected

EPIC IDEPIC SUMMARYIB OR PBCATEGORYT-SHIRT SIZESTORY POINTSCOMPLEXITY#SPRINTSRole DetailsPhaseEmployee Type#Location%
BR-002Dummy BR-002BothData SourcingL
107​
Complex
9​
IT Business AnalystAnalyzeEmployee
1​
India
100%​
BR-002Dummy BR-002BothData SourcingL
107​
Complex
9​
IT BuildDesignEmployee
1​
London
25%​
BR-002Dummy BR-002BothData SourcingL
107​
Complex
9​
IT BuildDevelopmentEmployee
2​
India
100%​
BR-002Dummy BR-002BothData SourcingL
107​
Complex
9​
IT BuildDevelopmentContractor
1​
Poland
50%​
BR-002Dummy BR-002BothData SourcingL
107​
Complex
9​
Functional TesterFunctional TestingEmployee
1​
India
100%​
BR-001Dummy BR-001BothEnrichments, Calculation & ProcessingXXL
206​
Very Complex
12​
IT Business AnalystAnalyzeEmployee
1​
India
100%​
BR-001Dummy BR-001BothEnrichments, Calculation & ProcessingXXL
206​
Very Complex
12​
IT BuildDesignEmployee
1​
London
25%​
BR-001Dummy BR-001BothEnrichments, Calculation & ProcessingXXL
206​
Very Complex
12​
IT BuildDevelopmentEmployee
2​
India
100%​
BR-001Dummy BR-001BothEnrichments, Calculation & ProcessingXXL
206​
Very Complex
12​
IT BuildDevelopmentContractor
1​
Poland
50%​
BR-001Dummy BR-001BothEnrichments, Calculation & ProcessingXXL
206​
Very Complex
12​
Functional TesterFunctional TestingEmployee
1​
India
100%​
BR-006Dummy BR-006BothDownstreamS
28​
Medium
5​
IT Business AnalystAnalyzeEmployee
1​
India
100%​
BR-006Dummy BR-006BothDownstreamS
28​
Medium
5​
IT BuildDesignEmployee
1​
London
25%​
BR-006Dummy BR-006BothDownstreamS
28​
Medium
5​
IT BuildDevelopmentEmployee
2​
India
100%​
BR-006Dummy BR-006BothDownstreamS
28​
Medium
5​
IT BuildDevelopmentContractor
1​
Poland
50%​
BR-006Dummy BR-006BothDownstreamS
28​
Medium
5​
Functional TesterFunctional TestingEmployee
1​
India
100%​







Hope this helps .... Need help to get this output.
 
Would you be so kind to post your workbook instead of images? That would save a lot of time and hassle :)
 
pecoflyer said:
Would you be so kind to post your workbook instead of images? That would save a lot of time and hassle :)
Click to expand...
Workbook already attached in the first post ---- i tried to simplify it with the table... re-attaching the file. really need help in this. let me know if you need any information to enable you experienced people to help me please.
 

Attachments

  • Sample file.xlsx
    103.3 KB · Views: 3
I can create a VBA demonstration according to your post #8 layout but should not work with your original workbook …​
​
 
ajoshi76 said:
I know it wouldnt work as per the excel workbooks - the #8 was created to simplify the requirement.
Click to expand...

This code doesnt work which i created... And i am unable to modify the code to make it work....

>>> use code - tags <<<
Code: 
Sub ICA_Process()

Dim R_cnt, Rn_cnt, LR As Long

Application.ScreenUpdating = False

Worksheets("Form II").Select

'R_cnt = Worksheets("Form II").Range("C17").Value
R_cnt = Worksheets("Form II").Range("T12").Value
Rn_cnt = Worksheets("Form II").Range("D13").Value
'RnR_cnt = Worksheets("Form II").Range("M29").Value



If Range("C6").Value = "" Or Range("C10").Value = "" Or Range("E10").Value = "" Or Range("G10").Value = "" Or Range("I10").Value = "" Or Range("K10").Value = "" Or Range("M10").Value = "" Or Range("C10").Value < 1 Or Range("E10").Value < 1 Or Range("B29") <> Range("H29") Or Range("H29") <> Range("I29") Or Range("I29") <> Range("J29") Or Range("J29") <> Range("K29") Or Range("K29") <> Range("L29") Or Range("L29") <> Range("M29") Then
    MsgBox ("One or More Fields are missing, please fill and complete the form and then press submit button")
End
End If

If Range("C13").Value <> Range("G13").Value Or Range("G13").Value <> Range("I13").Value Or Range("I13").Value <> Range("K13").Value Or Range("K13").Value <> Range("M13").Value Then
   MsgBox ("Values in either of Role, Location or %Allocation section is missing, please fix it and resubmit")
End
End If

If Range("L13").Value < 1 Then
   MsgBox ("Location % Allocation should sum to 100%, it cannot be less than 100%, please correct it and resubmit")
   End
End If

LR = Worksheets("Impact-Complexity Assessment").Range("A4").Value



'Copying Epic Id to Impact-Complexity Assessment
Worksheets("Form II").Range("B31:B" & 130).Copy
Worksheets("Impact-Complexity Assessment").Range("B" & LR & ":B" & (LR + R_cnt) - 1).PasteSpecial xlPasteValues

'Copying Epic Summary to Impact-Complexity Assessment
Worksheets("Form II").Range("C31:C" & 130).Copy
Worksheets("Impact-Complexity Assessment").Range("C" & LR & ":C" & (LR + R_cnt) - 1).PasteSpecial xlPasteValues

'Copying IB/PB to Impact-Complexity Assessment
Worksheets("Form II").Range("H31:H" & 130).Copy
Worksheets("Impact-Complexity Assessment").Range("D" & LR & ":D" & (LR + R_cnt) - 1).PasteSpecial xlPasteValues

'Copying Category to Impact-Complexity Assessment
Worksheets("Form II").Range("I31:I" & 130).Copy
Worksheets("Impact-Complexity Assessment").Range("E" & LR & ":E" & (LR + R_cnt) - 1).PasteSpecial xlPasteValues

'Copying Application to Impact-Complexity Assessment
Worksheets("Form II").Range("C6").Copy
Worksheets("Impact-Complexity Assessment").Range("F" & LR & ":F" & (LR + R_cnt) - 1).PasteSpecial xlPasteValues

'Copying T-Shirt Size to Impact-Complexity Assessment
Worksheets("Form II").Range("J31:J" & 130).Copy
Worksheets("Impact-Complexity Assessment").Range("G" & LR & ":G" & (LR + R_cnt) - 1).PasteSpecial xlPasteValues

'Copying Story Points to Impact-Complexity Assessment
Worksheets("Form II").Range("K31:K" & 130).Copy
Worksheets("Impact-Complexity Assessment").Range("H" & LR & ":H" & (LR + R_cnt) - 1).PasteSpecial xlPasteValues

'Copying Complexity to Impact-Complexity Assessment
Worksheets("Form II").Range("L31:L" & 130).Copy
Worksheets("Impact-Complexity Assessment").Range("I" & LR & ":I" & (LR + R_cnt) - 1).PasteSpecial xlPasteValues

'Copying Sprints to Impact-Complexity Assessment
Worksheets("Form II").Range("M31:M" & 130).Copy
Worksheets("Impact-Complexity Assessment").Range("L" & LR & ":L" & (LR + R_cnt) - 1).PasteSpecial xlPasteValues

'Copying Role to Impact-Complexity Assessment
Worksheets("Form II").Range("T14:T" & 25).Copy
Worksheets("Impact-Complexity Assessment").Range("M" & LR & ":M" & (LR + R_cnt) - 1).PasteSpecial xlPasteValues

'Copying Phase to Impact-Complexity Assessment
Worksheets("Form II").Range("U14:U" & 25).Copy
Worksheets("Impact-Complexity Assessment").Range("N" & LR & ":N" & (LR + R_cnt) - 1).PasteSpecial xlPasteValues

Worksheets("Impact-Complexity Assessment").Range("P" & LR & ":p" & (LR + R_cnt) - 1).Value = "Yes"

MsgBox ("Impact Complexity Assessment Sheet Updated")


End Sub
 
Last edited by a moderator:
As a short code can mix both ranges to the result worksheet …​
​
ajoshi76 said:
And i am unable to modify the code to make it work....
Click to expand...
But are you able to fit to your real workbook any VBA demonstration made upon your post #8 layout ? …​
​
 
Marc L said:
As a short code can mix both ranges to the result worksheet …​
​

But are you able to fit to your real workbook any VBA demonstration made upon your post #8 layout ? …​
​
Click to expand...
No.... the code was for the original excel which doesnt work. For the simple Sample - didnt create as the code is roughly the same from the process perspective.
 
As I have an issue with your initial post attachment - maybe 'cause of my Excel versions older than yours - if no one else helps you by Thursday​
I will send it to a kid Excel VBA beginner as a training, his code will be uggly as using the Macro Recorder but if it works as expected …​
​
 
I dont think the excel macro recorder will work and help. But this would be a good exercise for the VBA beginner.
 
The Macro Recorder helps each time smart users, the generated code just needs some tweaks …​
​
​
 
Another unclear point according to your initial post and as you forgot to attach your workbook code in post #13 :​
as a .xlsx workbook can not contain any VBA procedure so where should be located the expected VBA procedure ?​
The .xlsx data workbook must be already opened ? …​
​
 
Marc L said:
Another unclear point according to your initial post and as you forgot to attach your workbook code in post #13 :​
as a .xlsx workbook can not contain any VBA procedure so where should be located the expected VBA procedure ?​
The .xlsx data workbook must be already opened ? …​
​
Click to expand...
As per #13, there is no code for that sample excel..... thats what i wrote in #16... Hence, the excel is XLSX extension and not XLSM.
 
Without any answer so back to a guessing challenge and as guessing can't be coding ! …​
​
 
Marc L said:
Without any answer so back to a guessing challenge and as guessing can't be coding ! …​
​
Click to expand...
okay. I attached the detailed excel showing output and inputs. i am not sure what is there to guess when the input and output are already given in so much detail plus attached an extremely simpleified version..
 
The code on the original excel has been fixed and its working on the logic. Thanks for your time and patience.
 
You must log in or register to reply here.
Back
Top