Need to find value at intersection of row and column

[9tanstaafl9]

Sorry if this is obvious, but I have no idea how to even look it up. I've never done anything similar.

I have timecard values on one sheet that look something like this:

employee # 1 1 2 2
type ST OT ST OT

costcode hours
100 0 0 6 2
200 0 0 0 0
300 5 0 0 1


On another sheet, I need to have a result that looks something like this:

employee# type costcode hours
1 ST 100 0
1 DT 100 0
1 ST 200 0
1 DT 200 0
1 ST 300 5
1 DT 300 0
2 ST 100 6
2 DT 100 2
2 ST 200 0
2 DT 200 0
2 ST 300 0
2 DT 300 1

Is there any way with either formulas or VBA to get this information?

I was thinking of maybe just counting the number of employees, multiplying that by three and then by the number of cost codes. I could then automatically fill in the first three columns with no trouble. But the HOURS column I'd need some way to find the intersection of the cost code and the employee number. Then I could use an IF to figure out the different time types.

I can create named ranges if needed. I will not know the employee numbers or the cost codes ahead of time. Each employee has the same two time types, ST and DT. Some employees will have no hours and some cost codes will have no hours for any employee.

Any ideas would be appreciated, I have no ideas here.

 

[MickG]

Based on your Data as below:-

[COLOR="RoyalBlue"][B]Row No [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(A)   [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(B) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(C) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(D) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(E) [/B][/COLOR]
1.      employee  1       1       2       2      
2.      type      ST      DT      ST      DT     
3.                                               
4.      costcode  hours                          
5.      100       0       0       6       2      
6.      200       0       0       0       0      
7.      300       5       0       0       1

Try this for results starting Sheet2 "A1".

[COLOR="Navy"]Sub[/COLOR] MG15Jul57
Private [COLOR="Navy"]Sub[/COLOR] CommandButton1_Click()
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range


Lst = Cells("1", Columns.Count).End(xlToLeft).Column
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A5"), Range("A" & Rows.Count).End(xlUp))
ReDim Ray(1 To Rng.Count * Lst, 1 To 4)
c = 1
Ray(1, 1) = "Emp#": Ray(1, 2) = "Type": Ray(1, 3) = "Cost Code": Ray(1, 4) = "Hours"


[COLOR="Navy"]For[/COLOR] Ac = 1 To Lst - 1 [COLOR="Navy"]Step[/COLOR] 2
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        c = c + 1
        Ray(c, 1) = Range("A1").Offset(, Ac)
        Ray(c, 2) = Range("A2").Offset(, Ac)
        Ray(c, 3) = Dn.Value
        Ray(c, 4) = Dn.Offset(, Ac)
        c = c + 1
        Ray(c, 1) = Range("A1").Offset(, Ac + 1)
        Ray(c, 2) = Range("A2").Offset(, Ac + 1)
        Ray(c, 3) = Dn.Value
        Ray(c, 4) = Dn.Offset(, Ac + 1)
   [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] Ac
Sheets("Sheet2").Range("A1").Resize(c, 4) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[9tanstaafl9]

Thank you! I'm going to go try this right now. Sorry for the delay, I had chores/kid stuff I had to do. I'll let you know how it works ASAP. (May take me a bit, my example was simplified, but I can figure it out from what you gave me.)

Based on your Data as below:-

[COLOR=RoyalBlue][B]Row No [/B][/COLOR] [COLOR=RoyalBlue][B]Col(A)   [/B][/COLOR] [COLOR=RoyalBlue][B]Col(B) [/B][/COLOR] [COLOR=RoyalBlue][B]Col(C) [/B][/COLOR] [COLOR=RoyalBlue][B]Col(D) [/B][/COLOR] [COLOR=RoyalBlue][B]Col(E) [/B][/COLOR]
1.      employee  1       1       2       2      
2.      type      ST      DT      ST      DT     
3.                                               
4.      costcode  hours                          
5.      100       0       0       6       2      
6.      200       0       0       0       0      
7.      300       5       0       0       1

Try this for results starting Sheet2 "A1".

[COLOR=Navy]Sub[/COLOR] MG15Jul57
Private [COLOR=Navy]Sub[/COLOR] CommandButton1_Click()
[COLOR=Navy]Dim[/COLOR] Lst [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range


Lst = Cells("1", Columns.Count).End(xlToLeft).Column
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A5"), Range("A" & Rows.Count).End(xlUp))
ReDim Ray(1 To Rng.Count * Lst, 1 To 4)
c = 1
Ray(1, 1) = "Emp#": Ray(1, 2) = "Type": Ray(1, 3) = "Cost Code": Ray(1, 4) = "Hours"


[COLOR=Navy]For[/COLOR] Ac = 1 To Lst - 1 [COLOR=Navy]Step[/COLOR] 2
    [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
        c = c + 1
        Ray(c, 1) = Range("A1").Offset(, Ac)
        Ray(c, 2) = Range("A2").Offset(, Ac)
        Ray(c, 3) = Dn.Value
        Ray(c, 4) = Dn.Offset(, Ac)
        c = c + 1
        Ray(c, 1) = Range("A1").Offset(, Ac + 1)
        Ray(c, 2) = Range("A2").Offset(, Ac + 1)
        Ray(c, 3) = Dn.Value
        Ray(c, 4) = Dn.Offset(, Ac + 1)
   [COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]Next[/COLOR] Ac
Sheets("Sheet2").Range("A1").Resize(c, 4) = Ray
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[9tanstaafl9]

It worked!!!!! Thank you so much. I am was literally jumping up and down a few seconds ago. The way I was going to try to do this probably wouldn't have worked and even if it did it would have taken forever to run.

One quick question. Since I don't completely understand what is going on in your code. My HOURS are actually supposed to end up in column L and my cost codes actually start in row 11. Which part of the code would I change for that? (I tested this by changing my spreadsheet to match my example I gave you, but that won't work in real life.)

Thank you again!

Based on your Data as below:-

[COLOR="RoyalBlue"][B]Row No [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(A)   [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(B) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(C) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(D) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(E) [/B][/COLOR]
1.      employee  1       1       2       2      
2.      type      ST      DT      ST      DT     
3.                                               
4.      costcode  hours                          
5.      100       0       0       6       2      
6.      200       0       0       0       0      
7.      300       5       0       0       1

Try this for results starting Sheet2 "A1".

[COLOR="Navy"]Sub[/COLOR] MG15Jul57
Private [COLOR="Navy"]Sub[/COLOR] CommandButton1_Click()
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range


Lst = Cells("1", Columns.Count).End(xlToLeft).Column
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A5"), Range("A" & Rows.Count).End(xlUp))
ReDim Ray(1 To Rng.Count * Lst, 1 To 4)
c = 1
Ray(1, 1) = "Emp#": Ray(1, 2) = "Type": Ray(1, 3) = "Cost Code": Ray(1, 4) = "Hours"


[COLOR="Navy"]For[/COLOR] Ac = 1 To Lst - 1 [COLOR="Navy"]Step[/COLOR] 2
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        c = c + 1
        Ray(c, 1) = Range("A1").Offset(, Ac)
        Ray(c, 2) = Range("A2").Offset(, Ac)
        Ray(c, 3) = Dn.Value
        Ray(c, 4) = Dn.Offset(, Ac)
        c = c + 1
        Ray(c, 1) = Range("A1").Offset(, Ac + 1)
        Ray(c, 2) = Range("A2").Offset(, Ac + 1)
        Ray(c, 3) = Dn.Value
        Ray(c, 4) = Dn.Offset(, Ac + 1)
   [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] Ac
Sheets("Sheet2").Range("A1").Resize(c, 4) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MickG]

Ref :- Cost Code Line.
If the actual word "Cost code" is in "A11" then change the line as below to start in "A12"

Set Rng = Range(Range([B][COLOR=#ff0000]"A12")[/COLOR][/B], Range("A" & Rows.Count).End(xlUp))

Ref "Hours"
If you require the results to be laid out as per the code results but ending in Column "L" then change the line below to start in column "I"

Sheets("Sheet2").Range([B][COLOR=#ff0000]"I1"[/COLOR][/B]).Resize(c, 4) = Ray