Posts by COGICPENNY

My problem is in 2 parts.

• Each supplier listed in the tab "IT App Dev" (Column B) needs to be matched with a Commodity Code.
• The Commodity Code is determined by VLOOKUP the SubCategory Code "IT App Dev" (Column K) in the tab "Phase 2 Taxonomy".
• For this SubCategory there are two (2) commodity codes: 80101507 Consulting and 81111509 Application Development Services
• So there would be two (2) rows for each Supplier, the first row would return the value 80101507 Consulting in Column AB, and the commodity description in AC

I created a SAMPLE OUTLOOK FILE tab to see what I'm trying to do.

Unsure if I needed a macro first to create the extra rows then do a lookup using INDEX or something?

Really appreciate the help.

Background: The pink and blue shaded cells are commodity codes that have leading zeroes or are alpha-numeric codes. Our system rejects these types of codes and we require either a replacement code from the CONSOLIDATED TAXONOMY FILE or if no other alternative code is available, we have to generate a new commodity code (I will do this part). The lookup value is the NEW COMMODITY NAME (Column H), the results from the formula can be placed horizontally beginning at (Column U) ALTERNATE VISION COMMODITY CODE in the “ASM TAXONOMY FILE 040616”.

The range where the possible return matches is can be found in the tab “CONSOLIDATED TAXONOMY FILE”, using COMMODITY (Column G) and returning the value COMMODITY CODE (Column H). There is a possibility that there is only 1 commodity code choice which may be the leading zero or alpha-numeric code we are already using – I need to validate this.

I'm trying (without success) to consolidate two worksheets in two separate tabs within the same file. They have come from the same source file that was too large to export all of FY2014 data, so it was broken up into semi-year (Q1-Q2, and Q3-Q4). They have the same exact structure/format, however trying to consolidate them is not working.

The Q1-Q2 file: A1..X553210 with a header row
The Q3-Q4 file: A1..X619378 with a header row

I tried using the following Code I found off the internet, which DID create a new tab called "Consolidated", but it only copied and pasted the Q3-Q4 file cell for cell and row for row (duplicate):

Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

Re: If date between 2 values, return text value

Perrrrrfect! It worked! Thank you!

I have 50 rows of data representing each US state with their project end dates calculated. I need help to return a value in the "Fiscal Quarter" column that looks at the project end date range and assign it a fiscal quarter (i.e. 1Q2011, 2Q2011, etc), and have attached the file.

Thank you.
Penny

Re: Compute Project Start/End Date

I viewed the excel worksheet attachment and the set-up is almost what I'm looking for, but the Project End date calculation isn't correct because Row 2 alone (State of California) would take 3.31 months to complete, bringing the Project End date somewhere around May, 2011 "2Q2011", based on the assumption that 9 contacts can be made per week. Penny

Re: Compute Project Start/End Date

I realized what I did which might make it clearer (and I apologize, I'm still learning):

9 contacts per week (fixed amount)
4.33 weeks in a month
Row 2 = 129 total contacts
So 129/(9*4.33) = 3.31 months to complete, but I want to be able to easily play with the number "9" represents (my processing efficiency/number of contacts I can close a week)

Should I have kept the data in week format so it would be 129/9 = 14.33?

And the dates don't have to be firm/strict because they represent a general guideline of how long it would take to complete the task.

The final column "placement", I want to return a value where if the project completion date falls between 01/01 - 03/31/2011 it says "1Q2011", and so on.

Thanks and sorry for the confusion,
Penny

Re: Compute Project Start/End Date

I think the data (1:08:36) is correct because I end up with a timeframe close to what I had tried to calculate in Excel saying 2.13 years. I just need to understand for my education, the clarification on what you mean by "if you work for 3.43 40 hour weeks on 120 contracts" how that equals the 9 contracts/week for 120 contacts is calculated, and is that number adjusted for each row since the # of total contacts differs for each state? Thanks.

Re: Compute Project Start/End Date

Thank you for responding to my inquiry. I think your worksheet was well thought out and excellent work. However, what I'm looking to do perhaps may be easier if I add a column (even if hidden) which shows the number of contacts I can commit to making a week (my number was 9 in my original thread), divided into the amount of total contacts it shows how many "weeks" it would take for me to make all the calls to the total contact.

Then I want to calculate if the first row of data (Row 2) takes say 3.43 weeks to make 120 contacts, I want to establish the project start date as of 02/01/2011, and go forward 3.43 weeks (calculation based on # total contacts/# contacts per week, will end on "xx/xx/2011", which also serves as my Project Start date for Row 3 and so on.

Hope I made sense.
Penny

I need help computing the Project Start Dates for each US state marketing campaign, under the Attached File "Strategic Plan" tab.

Column E: Total Number of State Contacts
Column F: Estimated Processing Time - calculated as =E2/(9*4.33) meaning, I could process 9 contacts per week in a given month, resulting in an answer in months.
Column G: Project Begin Date: If I started my first state (Row 2), on February 1, 2011 (provided it's a weekday, because it will only be workdays), when would I complete the project (February 1, 2011 + results of Column F)
Column H: Project End Date: What date will the project be completed if began on February 1, 2011 for Row 2 and so on?

Column G and H are dependent upon each others similar to that in a Gannt's predecessor task, where Column G (Project Start Date) is at 02/01/2011, the processing time is "x.xx weeks", giving a task completion date of xx/xx/2011, which date also serves as Column G (Project Start Date) of Row 3, and the process continues until all the row data has been completed.

Thanks,
Penny