if statement help -

[gmack1]

So I need to capture the Name and corresponding Total. Notice that the Employee total is varying cells below the name - For Bobby Jones its 3 down and Ricky Bobby its 4.

For example the desired data would read like this:
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bobby Jones[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Ricky Bobby[/TD]
[TD]1,100[/TD]
[/TR]
</tbody>[/TABLE]

The raw data looks like this:

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bobby Jones[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]678876[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]987689[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Employee Total[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Ricky Bobby[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]998765[/TD]
[TD]800[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]87625443[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]9871779[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Employee Total[/TD]
[TD]1,100[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I've tried a pivot table but because of the orientation of the data I can't get it to work.

Any ideas ?

Thanks
G

 

[svendiamond]

Are there always numbers between the employee's name and the "Employee Total" cell? Like in your example?

 

[gmack1]

yes.

I was trying to come up with some if(numeric) statement but can't get it right

 

[Eric W]

Try putting this formula in B2 of your first table. I assumed the other table is on Sheet1:

=VLOOKUP("Employee Total",OFFSET(Sheet1!$B$1:$C$500,MATCH(B2,Sheet1!B:B,0),0,50,2),2,FALSE)

I also assumed there would be a maximum of 50 rows between the name and "Employee Total". Then copy the cell and paste it down.

Let me know if that works for you.

 

[elmer007]

You could also have a column C with the formula in C1 being =A1 and then C2 (and copy down to the bottom) would be: =IF(A1="Employee Total",A2,C1)
This would result in having the employee's name by every row.

Then, you can PivotTable or do SUMIFS.

 

[svendiamond]

If you want a VBA solution, you could use this code. This is assuming your desired table is on Sheet2, and your raw data is on Sheet1.

Sub findTotals()

Dim myLoop As Long, lastEmployee As Long, lastRow As Long
Dim empCell As Range, totCell As Range, employee As String
Dim searchRange As Range, ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

    lastEmployee = ws2.Range("A" & Rows.Count).End(xlUp).Row
    lastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row

For myLoop = 2 To lastEmployee

    employee = ws2.Cells(myLoop, "A").Value
Set empCell = ws1.Range("A:A").Find(employee)
Set searchRange = ws1.Range("A" & empCell.Row & ":A" & lastRow)
Set totCell = searchRange.Find("Employee Total")
    
    ws2.Cells(myLoop, "B").Value = totCell.Offset(0, 1).Value

Next myLoop

End Sub