Vlookup or HLookup or Sumproduct Help
 

Good Day I have the following information and and am not sure wg=hich is the
best way to handle it.

Sheet1
Row ColumnA ColumnB ColumnC ColumnD
1 Date Audit Auditor Length
2 1/1/07 Butter Frank 1.5
3 1/1/07 Cheese Mary 2.8
4 1/1/07 Meat Perry 3.5
5 1/2/07 Butter Mary 1.5
3 1/2/07 Cheese Frank 2.8
4 1/2/07 Meat Perry 3.5
so on and so forth for the week.

I have on Sheet2 the schedule layed out like the following,

Row ColumnA ColumnB ColumnC ColumnD ColumnE
1 Frank Mary Perry TotalHours

Vlookup or HLookup or Sumproduct Help
 

And what are you expecting the rest of sheet two to look like?

LOU wrote:
Good Day I have the following information and and am not sure wg=hich is the
best way to handle it.

Sheet1
Row ColumnA ColumnB ColumnC ColumnD
1 Date Audit Auditor Length
2 1/1/07 Butter Frank 1.5
3 1/1/07 Cheese Mary 2.8
4 1/1/07 Meat Perry 3.5
5 1/2/07 Butter Mary 1.5
3 1/2/07 Cheese Frank 2.8
4 1/2/07 Meat Perry 3.5
so on and so forth for the week.

I have on Sheet2 the schedule layed out like the following,

Row ColumnA ColumnB ColumnC ColumnD ColumnE
1 Frank Mary Perry TotalHours

Vlookup or HLookup or Sumproduct Help
 

I sen by accident, heh..
finished below.

Sheet1
Row ColumnA ColumnB ColumnC ColumnD
1 Date Audit Auditor Length
2 1/1/07 Butter Frank 1.5
3 1/1/07 Cheese Mary 2.8
4 1/1/07 Meat Perry 3.5
5 1/2/07 Butter Mary 1.5
6 1/2/07 Cheese Frank 2.8
7 1/2/07 Meat Perry 3.5
so on and so forth for the week.

I have on Sheet2 the schedule layed out like the following,

Row ColumnA ColumnB ColumnC --------- week finish with totals
1 Audit 1/1/2007 1/2/2007 TotalHours
2 Butter
3 Meat
4 Cheese

I need a lookup formula that I can place in cell B2 and the rest of the week
which will look at SHEET1 and determine who is doing the audit on the date
and bring the info bakc to cellB2. The answer would be "Frank" on 1/1/07 and
1/2/07 it would be "Mary".

Any help would be great!

Thank you.
Lou

Vlookup or HLookup or Sumproduct Help
 

In sheet2,

For Frank, A2 =sumif(sheet1!C:C,sheet2!A1,sheet1!D:D)

For Mary, B2 =sumif(sheet1!C:C,sheet2!B1,sheet1!D:D)

etc

Total hours is either =sum(sheet1!D:D)

OR sheet2 A2 + B2 + C2

Vlookup or HLookup or Sumproduct Help
 

Is this what you were looking for?

Sum of Length Auditor
Audit Frank Mary Perry Grand Total
Butter 1.5 1.5 3
Cheese 2.8 2.8 5.6
Meat 7 7
Grand Total 4.3 4.3 7 15.6

It's called a PivotTable. It can be found under the "Data" Menu in
Excel.

Cheers,
Jason Lepack

LOU wrote:
Good Day I have the following information and and am not sure wg=hich is the
best way to handle it.

Sheet1
Row ColumnA ColumnB ColumnC ColumnD
1 Date Audit Auditor Length
2 1/1/07 Butter Frank 1.5
3 1/1/07 Cheese Mary 2.8
4 1/1/07 Meat Perry 3.5
5 1/2/07 Butter Mary 1.5
3 1/2/07 Cheese Frank 2.8
4 1/2/07 Meat Perry 3.5
so on and so forth for the week.

I have on Sheet2 the schedule layed out like the following,

Row ColumnA ColumnB ColumnC ColumnD ColumnE
1 Frank Mary Perry TotalHours

Vlookup or HLookup or Sumproduct Help
 

=INDEX(Sheet1!$C$2:$C$100,MATCH(1,(Sheet1!$A2:$A10 0=Sheet2!$A1)*(Sheet1!$B$2:$B$100=Sheet2!B$2),0))
ctrl+shift+enter, not just enter

"LOU" wrote:

Good Day I have the following information and and am not sure wg=hich is the
best way to handle it.

Sheet1
Row ColumnA ColumnB ColumnC ColumnD
1 Date Audit Auditor Length
2 1/1/07 Butter Frank 1.5
3 1/1/07 Cheese Mary 2.8
4 1/1/07 Meat Perry 3.5
5 1/2/07 Butter Mary 1.5
3 1/2/07 Cheese Frank 2.8
4 1/2/07 Meat Perry 3.5
so on and so forth for the week.

I have on Sheet2 the schedule layed out like the following,

Row ColumnA ColumnB ColumnC ColumnD ColumnE
1 Frank Mary Perry TotalHours

Vlookup or HLookup or Sumproduct Help
 

Everyone thank you,
Jason,
Pivot table would be nice but I need the Auditors name to show on the day of
the week as listed below.

Sum of Length Auditor
Audit 1/1/07 1/2/07 1/3/07
Butter Mary Frank
Cheese Bob Mary
Meat Frank Bob

No total columns, when I do this it does not let me use the name corectly,
it want to sum and change it to zeros or A numeric value even if you have it
set to text.

Thank you.
Lou

"Jason Lepack" wrote:

Is this what you were looking for?

Sum of Length Auditor
Audit Frank Mary Perry Grand Total
Butter 1.5 1.5 3
Cheese 2.8 2.8 5.6
Meat 7 7
Grand Total 4.3 4.3 7 15.6

It's called a PivotTable. It can be found under the "Data" Menu in
Excel.

Cheers,
Jason Lepack

LOU wrote:
Good Day I have the following information and and am not sure wg=hich is the
best way to handle it.

Sheet1
Row ColumnA ColumnB ColumnC ColumnD
1 Date Audit Auditor Length
2 1/1/07 Butter Frank 1.5
3 1/1/07 Cheese Mary 2.8
4 1/1/07 Meat Perry 3.5
5 1/2/07 Butter Mary 1.5
3 1/2/07 Cheese Frank 2.8
4 1/2/07 Meat Perry 3.5
so on and so forth for the week.

I have on Sheet2 the schedule layed out like the following,

Row ColumnA ColumnB ColumnC ColumnD ColumnE
1 Frank Mary Perry TotalHours

Vlookup or HLookup or Sumproduct Help
 

Backup your spreadsheet, place a copy of this in your VB Editor and
test it out.

Cheers,
Jason Lepack

Sub macro1()
On Error GoTo macro1Err
Dim wb As Workbook
Dim wsF As Worksheet, wsT As Worksheet
Dim rF As Range, rT As Range, rTemp As Range, rTemp2 As Range

Set wb = ActiveWorkbook
Set wsF = wb.ActiveSheet
Set wsT = wb.Sheets.Add

' sort the data for the column headers
wsF.Columns("A:D").Sort Key1:=wsF.Range("A2"), Order1:=xlAscending,
_
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal

' create the column headers
Set rF = wsF.Range("A2")
Set rT = wsT.Range("A1")
Do While Not rF.Value = ""
If Not rF.Value = rT.Value Then
Set rT = rT.Offset(0, 1)
rT.Value = rF.Value
End If
' create a temporary column for searching later
rF.Offset(0, 4).Value = rF.Value & "|" & rF.Offset(0, 1).Value
Set rF = rF.Offset(1, 0)
Loop

' sort the data for the row headers
wsF.Columns("A:E").Sort Key1:=wsF.Range("B2"), Order1:=xlAscending,
_
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal

' create row headers
Set rF = wsF.Range("B2")
Set rT = wsT.Range("A1")
Do While Not rF.Value = ""
If Not rF.Value = rT.Value Then
Set rT = rT.Offset(1, 0)
rT.Value = rF.Value
End If
Set rF = rF.Offset(1, 0)
Loop

' sort the data for the table
wsF.Columns("A:E").Sort Key1:=wsF.Range("A2"), Order1:=xlAscending,
_
Key2:=wsF.Range("B2"), Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal

' fill in the table
Set rF = wsT.Range("A2")
Do While Not rF.Value = ""
Set rT = wsT.Range("B1")
Do While Not rT.Value = ""
Set rTemp = Intersect(rF.EntireRow, rT.EntireColumn)
Set rTemp2 = wsF.Range("E:E").Find(rT.Value & "|" &
rF.Value)
If Not rTemp2 Is Nothing Then rTemp.Value =
rTemp2.Offset(0, -2).Value
Set rT = rT.Offset(0, 1)
Loop
Set rF = rF.Offset(1, 0)
Loop

' remove the temporary column that was created
wsF.Range("E:E").Value = ""

MsgBox "New Inspection sheet placed in " & wsT.Name

macro1Goodbye:
Set rTemp2 = Nothing
Set rTemp = Nothing
Set rF = Nothing
Set rT = Nothing
Set wsF = Nothing
Set wsT = Nothing
Set wb = Nothing
Exit Sub
macro1Err:
MsgBox "NUMBER:" & Err.Number & vbCrLf & vbCrLf & "DESCRIPTION:" _
& vbCrLf & Err.Description & vbCrLf & vbCrLf & _
"Supply Jason Lepack with this information as well as " & _
"the data that you were using.", vbCritical, "TRAPPED ERROR"
Resume macro1Goodbye
Resume Next
End Sub



LOU wrote:
Everyone thank you,
Jason,
Pivot table would be nice but I need the Auditors name to show on the day of
the week as listed below.

Sum of Length Auditor
Audit 1/1/07 1/2/07 1/3/07
Butter Mary Frank
Cheese Bob Mary
Meat Frank Bob

No total columns, when I do this it does not let me use the name corectly,
it want to sum and change it to zeros or A numeric value even if you have it
set to text.

Thank you.
Lou

"Jason Lepack" wrote:

Is this what you were looking for?

Sum of Length Auditor
Audit Frank Mary Perry Grand Total
Butter 1.5 1.5 3
Cheese 2.8 2.8 5.6
Meat 7 7
Grand Total 4.3 4.3 7 15.6

It's called a PivotTable. It can be found under the "Data" Menu in
Excel.

Cheers,
Jason Lepack

LOU wrote:
Good Day I have the following information and and am not sure wg=hich is the
best way to handle it.

Sheet1
Row ColumnA ColumnB ColumnC ColumnD
1 Date Audit Auditor Length
2 1/1/07 Butter Frank 1.5
3 1/1/07 Cheese Mary 2.8
4 1/1/07 Meat Perry 3.5
5 1/2/07 Butter Mary 1.5
3 1/2/07 Cheese Frank 2.8
4 1/2/07 Meat Perry 3.5
so on and so forth for the week.

I have on Sheet2 the schedule layed out like the following,

Row ColumnA ColumnB ColumnC ColumnD ColumnE
1 Frank Mary Perry TotalHours