'Turn' data in part of a row and calculate which column it needs to go in

D

ddander54

Board Regular
Joined
Oct 18, 2012
Messages
97
I have multiple rows of data that I need to reformat to match another much larger spreadsheet's layout.
Sorry about all the dots, I don't know how else to somewhat display the data in columns

What I have is Total hours in Skill labor and a StartDate where I calculated the Duration from the EndDate:
ProjNum....StartDate....EndDate....Duration(Months)....Skill1....Skill2....Skill3.... etc...
123...........Aug2017.......Aug2017............1..........................12...........8..........45
456...........Sep2017.......Dec 2017...........4.........................100..........0.........200
789...........Sep2017.......Apr 2018............8.........................88............64.......128

What I need is (insert rows between Project Numbers, then skill divided by Duration and evenly disperse that in each date column starting on the StartDate for the Duration):
ProjNum....Skill....Aug17....Sep17....Oct17....Nov17....Dec17....Jan18....Feb18....Mar18.....etc....
123
................Skill1......12.........0...........
................Skill2.......8..........0...........
................Skill3.......45.........0..........
456
................Skill1.......0........25..........25........25...........25.........0.....
................Skill2.......0.........0............0..........0.............0..........0.....
................Skill3.......0........50..........50..........50...........50.........0....
789
................Skill1.......0.......11...........11.........11...........11.........11........11.........11....0
................Skill2.......0.......8.............8...........8.............8...........8..........8..........8....0
................Skill3.......0.......16..........16.........16...........16.........16........16.........16....0

Thanks,
Don
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
ddander54 said:
I have multiple rows of data that I need to reformat to match another much larger spreadsheet's layout.
Sorry about all the dots, I don't know how else to somewhat display the data in columns

What I have is Total hours in Skill labor and a StartDate where I calculated the Duration from the EndDate:
ProjNum....StartDate....EndDate....Duration(Months)....Skill1....Skill2....Skill3.... etc...
123...........Aug2017.......Aug2017............1..........................12...........8..........45
456...........Sep2017.......Dec 2017...........4.........................100..........0.........200
789...........Sep2017.......Apr 2018............8.........................88............64.......128

What I need is (insert rows between Project Numbers, then skill divided by Duration and evenly disperse that in each date column starting on the StartDate for the Duration):
ProjNum....Skill....Aug17....Sep17....Oct17....Nov17....Dec17....Jan18....Feb18....Mar18.....etc....
123
................Skill1......12.........0...........
................Skill2.......8..........0...........
................Skill3.......45.........0..........
456
................Skill1.......0........25..........25........25...........25.........0.....
................Skill2.......0.........0............0..........0.............0..........0.....
................Skill3.......0........50..........50..........50...........50.........0....
789
................Skill1.......0.......11...........11.........11...........11.........11........11.........11....0
................Skill2.......0.......8.............8...........8.............8...........8..........8..........8....0
................Skill3.......0.......16..........16.........16...........16.........16........16.........16....0

Thanks,
Don
Click to expand...

I have a solution but the output would be in another sheet. Assuming table 1 is Sheet1 columns A:G:

in Sheet2:

A2 = 1
A3 = IF(B2<3,A2,A2+1) *** adjust 3 to the number of skills
B2 = 1
B3 = IF(B2<3,B2+1,1) *** adjust 3 to the number of skills
C2 = OFFSET(Sheet1!A$1,$A2,0)
D2 = OFFSET(Sheet1!B$1,$A2,0)
E2 = OFFSET(Sheet1!C$1,$A2,0)
F2 = OFFSET(Sheet1!D$1,$A2,0)
G2 = OFFSET(Sheet1!$D$1,0,$B2)
H2 = IF(AND(H$1>=$D2,H$1<=$E2),OFFSET(Sheet1!$D$1,MATCH($C2,Sheet1!$A$2:$A$4,0),MATCH($G2,Sheet1!$E$1:$Z$1,0))/$F2,0) *** this you can drag to the right

P.S. make sure that the dates are actual dates, Aug 2017 should be 7/1/2017..
 
Last edited:
Upvote 0
Assuming columns 2 & 3 of your data are actual dates, try this for results on sheet2.
Code: 
[COLOR="Navy"]Sub[/COLOR] MG04Aug25
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, oMax [COLOR="Navy"]As[/COLOR] Date, oMin [COLOR="Navy"]As[/COLOR] Date, Dts [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dt [COLOR="Navy"]As[/COLOR] Date, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, nAc [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] vAc [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
Ray = Cells(1).CurrentRegion
[COLOR="Navy"]Set[/COLOR] Rng = Range("B2").Resize(UBound(Ray, 1) - 1)
 
 oMin = Application.Min(Rng)
  oMax = Application.Max(Rng.Offset(, 1))
   Dts = DateDiff("m", oMin, oMax) + 3
     ReDim nRay(1 To Dts, 1 To 1)
      nRay(1, 1) = "ProjNum": nRay(2, 1) = "Skill"
     
      [COLOR="Navy"]For[/COLOR] Ac = 3 To Dts
           Dt = DateAdd("m", Ac - 3, oMin)
           nRay(Ac, 1) = Format(Dt, "mmm-yyyy")
           Dic(CDbl(DateValue(nRay(Ac, 1)))) = Ac
      [COLOR="Navy"]Next[/COLOR] Ac
      
      [COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
           [COLOR="Navy"]For[/COLOR] vAc = 5 To UBound(Ray, 2)
                ReDim Preserve nRay(1 To Dts, 1 To UBound(nRay, 2) + 1)
                    [COLOR="Navy"]For[/COLOR] nAc = Dic(CDbl(DateValue(Ray(n, 2)))) To Dic(CDbl(DateValue(Ray(n, 2)))) + Ray(n, 4) - 1
                        nRay(1, UBound(nRay, 2)) = Ray(n, 1)
                        nRay(2, UBound(nRay, 2)) = Ray(1, vAc)
                        nRay(nAc, UBound(nRay, 2)) = Ray(n, vAc) / Ray(n, 4)
                    [COLOR="Navy"]Next[/COLOR] nAc
            [COLOR="Navy"]Next[/COLOR] vAc
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(UBound(nRay, 2), Dts)
    .Value = Application.Transpose(nRay)
    .Borders.Weight = 2
    .Columns.AutoFit
 [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Mick,

Could you help me one more time by commenting the code so I can understand what is happening where? My VBA skills are limited, so there are many parts of it that I don't understand.

Thanks,
Don
 
Upvote 0
Mick,

Thanks for the links. I'm trying to run the code on a list (same columns), but with 102 rows and I'm getting an error, so I'm trying to figure that out. My error is: Run-Time error '9': Subscript out of range.

Thanks,
Don
 
Upvote 0
If you would like me to look at it you will need to send an example of the data its failing on !!!
 
Upvote 0
Thanks Mick! I got it figured out.

The Subscript error was being caused by the dates not being in the correct format (text vs date) or some special character that was at the end of each date. Not really sure which (or both) was causing the issue, but I'm leaning toward the special character because even tho' the dates were left justified (normally meaning text) I could do date type calculations in another column (ie, =DATEDIF(B2, C2, "m")+1) and that worked. Of course I'm making the assumption that wouldn't have worked if the columns were plain text. Anyway, I removed the special character by copying the date columns to notepad, then back to excel. At that point they were right justified and the special character was gone.

Then I had an error 13, so I determined that it didn't like the empty cells where there was no number value, so I just did a find " " and Replace with 0.

Then your code worked perfectly. Thanks for the help!

Don
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
How many rows between each data point in a column
Replies
3
Views
558
Fluff
Fluff
M
  • Solved
Need Help with a Fromula or a Macro to Delete Rows with Time 0:00:00 in Cell, Column G
Replies
9
Views
556
johnnyL
J
G
  • Question Question
How do I calculate and display meeting room utilization
Replies
0
Views
1K
GSTAR
G
L
  • Question Question
VBA Transpose Rows to Columns and Fill in Black Cells for Days Without Data
Replies
0
Views
1K
lstod
L
S
  • Question Question
VBA Solution to do a -4:00 hour GMT time calculate on imported Data in two Colums for all imported rows
2
Replies
10
Views
1K
smd747
S

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top