Horizontal to Vertical with repeating key fields

[SWinfield]

Hi, I hope someone can point me in the right direction as I've hit a brick wall.

I have a file that is laid out horizontally as below;

Unit / Employee Number / OT Rate 1 / OT Rate 1 value / OT Rate 2 / OT Rate 2 Value / and repeat 13 times!

But to be able to load it into our system it needs to be laid out like this;

Unit / Employee Number / OT Rate 1 / OT Rate 1 Value
Unit / Employee Number / OT Rate 2 / OT Rate 2 Value

So i need to change the OT Rate and associated value into vertically held data but duplicate the employee number next to it..

I hope that makes sense?

Any suggestions will be greatfully received!

Thanks

Shaun

 

[GTO]

Hi there,

1. Is it OT Rate 1 thru OT Rate 13?
2. Do we need to worry about blanks?
   1. If yes, do we need to check the Rate or Value to see if empty?
3. VBA okay?
4. What column and row starts the data?

 

[SWinfield]

Hi GTO,

1. Yes there are 13 different rates to be loaded, so it's OT 1 -> OT13, so what I need to make happen is take the one horizontal line, repeat the unit & employee number 13 times vertically with each individual rate returned against it.

2. There won't be any blank cells to worry about, if no data exists against a rate it will return a 0.

3. VBA is fine by me, I can handle cutting and pasting what I need into VBA - but writing actual code is something I can only do for really basic stuff at the moment!

4. Unit = A1, Employee No = B1, Rate 1 = C1, Value 1 = D1 (continues across 13 rates) So for example the 10th entry down would start from A10. There are no headings, this is an extract from an input sheet.

If it would be easier I'll see if I can get the HTML Excel exporter to work so you can see the actual sheet.

Thanks for having a look!

 

[SWinfield]

Ok,

This is the original sheet the data is keyed in on

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1						Overtime
2			Working	Hourly	Annual	1	1	1.25	1.33	1.5	2
3	Group	Emp No	Days	Rate	Salary	831	832	834	836	838	83C
4	085	7003066	5	21.527	41418	1	1	1	1	1	1
5	085	7001086	5	11.2645	21673	2	1	1	1	1	1
6	085	7001770	5	30.9183	60974	1	1	1	1	1	1
7	090	7002986	5	11.476	22080	1	2	1	1	1	1
8	085	7001089	5	19.7079	37918	1	1	1	1	1	1
9	085	7001089	5	19.7079	37918	1	1	1	1	1	1
Input Sheet

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	085	7003066	831	100	832	100	834	100	836	100	838	100
Excel 2003 And this is how it looks in the sheet I need to convert Output Test 1

Excel 2003

 

[GTO]

Thank you. I think I am 'getting' part of it, as I see where the 831, 832,...,838 is coming from. The vals (100 is the one row shown) in D/F/H..., are getting converted to 1's and in a couple of cases 2's?

Sorry if I am being thick-headed, but that part is throwing me a bit. Could you show a few rows of raw data? I am guessing:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	85	7003066	831	100	832	100	834	100	836	100	838	100
2	85	7001086	831	100	832	100	834	100	836	100	838	100
3	85	7001770	831	100	832	100	834	100	836	100	838	100
4	90	7002986	831	100	832	100	834	100	836	100	838	100
5	85	7001089	831	100	832	100	834	100	836	100	838	100
6	85	7001089	831	100	832	100	834	100	836	100	838	100
Sheet1

Excel 2003

 

[GTO]

Hi GTO,

1. Yes there are 13 different rates to be loaded, so it's OT 1 -> OT13, so what I need to make happen is take the one horizontal line, repeat the unit & employee number 13 times vertically with each individual rate returned against it.

I think I am misunderstanding post 4, as I thought the top example was output. Leastwise if ea emp ID# is to be run down 13 times as stated above.

 

[SWinfield]

Hi GTO,

You don't really need to worry about the first Excel sheet, that's where the data is keyed in by numerous people. So it's formated to be easier to read for them.

What I currently have is this;

Excel Workbook
	A	B	C	D	E	F	G	H
1	085	700501	831	1	832	2	833	3
2	085	700502	831	3	832	2	833	1
3	085	700503	831	4	832	5	833	6
4	085	700504	831	1	832	8	833	2
Original

Excel Workbook
	A	B	C	D
1	085	700501	831	1
2	085	700501	832	2
3	085	700501	833	3
4	085	700502	831	3
5	085	700502	832	2
6	085	700502	833	1
7	085	700503	831	4
8	085	700503	832	5
9	085	700503	833	6
10	085	700504	831	1
11	085	700504	832	8
12	085	700504	833	2
Excel 2003 But I need to automate the conversion of it into this Required Format

Excel 2003



Hope that clarifies!

 

[GTO]

Okay, in a junk copy of your wb, with the raw data in Sheet1, see if this is in the right direction.

Option Explicit
    
Sub exa()
'(If Group Number and/or Emp ID can be counted on as numbers, change to As Long         //
Dim _
aryInput        As Variant, _
aryOutput       As Variant, _
GroupNum        As Variant, _
EmpID           As Variant, _
lLRow           As Long, _
i               As Long, _
ii              As Long, _
n               As Long
    
    '// Using Codenames of the sheets.  'Sheet1' (or that is, the Codename of the sheet //
    '// the raw data) to be transferred to Sheet2 (codename of a blank sheet) to see    //
    '// if I am in the direction you want to be going...                                //
    With Sheet1
        '// Find last EmpID                                                             //
        lLRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        
        '// Size output array                                                           //
        ReDim aryOutput(1 To lLRow * 13, 1 To 4)
        '// Grab raw vals                                                               //
        aryInput = Range(.Range("A1"), .Cells(lLRow, 28)).Value
        
        '// For ea row...                                                               //
        For i = 1 To lLRow
            '// ...save the group num and ID to keep plunking into Output array...      //
            GroupNum = aryInput(i, 1)
            EmpID = aryInput(i, 2)
            '// Loop thru the columns in the row.                                       //
            For ii = 3 To 27 Step 2
                n = n + 1
                aryOutput(n, 1) = GroupNum
                aryOutput(n, 2) = EmpID
                aryOutput(n, 3) = aryInput(i, ii)
                aryOutput(n, 4) = aryInput(i, ii + 1)
            Next
        Next
    End With
    '// Plunk outout wherever.                                                          //
    Sheet2.Range("A1").Resize(UBound(aryOutput, 1), 4).Value = aryOutput
End Sub

Hope that helps,

Mark

 

[SWinfield]

Mark,

That is spot on! You've just saved me so much messing around. Thank you very much.

Superstar!

 

[GTO]

Glad to help