Find value in one column and return value from same row but different column in another tab

[Vickie Hartley]

I work for a non-profit community housing agency and created a workbook that has multiple tabs. One of the tabs is a spreadsheet that contains 22 columns (scope assumptions). On another tab I want to pull data from this large spreadsheet. There are usually at least a 100 rows, each room in a house has multiple lines and tasks with sub-totals by room. The spreadsheets primary purpose is to communicate to the construction crew what needs to be done in each room and what the budget is. Here is an example of the primary spreadsheet.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Location[/TD]
[TD]Task[/TD]
[TD]Description[/TD]
[TD]Labor Hours[/TD]
[TD]Labor Costs[/TD]
[TD]Materials[/TD]
[TD]Subs[/TD]
[TD]OH,G&A, Profit[/TD]
[TD]Contract Amount[/TD]
[/TR]
[TR]
[TD]Front Porch[/TD]
[TD]Demolition[/TD]
[TD]Demo garbage, existing windows, steps[/TD]
[TD]3[/TD]
[TD]90[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]60[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Front Porch[/TD]
[TD]Electrical[/TD]
[TD]Properly install wire mold at outlet close to ceiling and exterior light[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]165[/TD]
[TD]71[/TD]
[TD]236[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sub-Total[/TD]
[TD][/TD]
[TD][/TD]
[TD]90[/TD]
[TD]50[/TD]
[TD]165[/TD]
[TD]131[/TD]
[TD]436[/TD]
[/TR]
[TR]
[TD]Living Room[/TD]
[TD]Electrical[/TD]
[TD]Furnish and install new light switch and cover plate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]21[/TD]
[TD]71[/TD]
[/TR]
[TR]
[TD]Living Room[/TD]
[TD]Doors & Windows[/TD]
[TD]Furnish and install new trim and windows (3)[/TD]
[TD]8[/TD]
[TD]300[/TD]
[TD]300[/TD]
[TD][/TD]
[TD]257[/TD]
[TD]857[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sub-Total[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]300[/TD]
[TD]300[/TD]
[TD]50[/TD]
[TD]278[/TD]
[TD]928[/TD]
[/TR]
[TR]
[TD]*******[/TD]
[TD]Electrical[/TD]
[TD]Furnish and install new 240V outlet for range and 120V outlet for fridge[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]500[/TD]
[TD]214[/TD]
[TD]714[/TD]
[/TR]
[TR]
[TD]*******[/TD]
[TD]Plumbing[/TD]
[TD]******* sink - Furnish and install new PVC drain, vent and pex supply lines with shut off valves and all sundries for complete install[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1050[/TD]
[TD]450[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sub-Total[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1550[/TD]
[TD]664[/TD]
[TD]2214[/TD]
[/TR]
</tbody>[/TABLE]

A second, third and fourth spreadsheet is needed to communicate to the sub-contractors what needs to be done. One spreadsheet each for Electrical, Plumbing, and HVAC. Ideally I want to create formulas that will look for every occurrence of a given task (i.e. "Electrical") in column B from the primary spreadsheet and return the corresponding location, description, and cost for each occurrence. For example:

Electrical Scope
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Location[/TD]
[TD]Description[/TD]
[TD]Budget[/TD]
[/TR]
[TR]
[TD]Front Porch[/TD]
[TD]Properly install wire mold at outlet close to ceiling and exterior light[/TD]
[TD]236[/TD]
[/TR]
[TR]
[TD]Living Room[/TD]
[TD]Furnish and install new light switch and cover plate[/TD]
[TD]71
[/TD]
[/TR]
[TR]
[TD]*******[/TD]
[TD]Furnish and install new 240V outlet for range and 120V outlet for fridge[/TD]
[TD]714[/TD]
[/TR]
</tbody>[/TABLE]


Can you please give me some guidance and/or answers?

I often use Mr. Excel to solve my Excel problems by looking for similar problems or using the books I have purchased but this time, I could not find a solution so I posted my first request for help. I have been using this site for about 3 years.

I have tried a multitude of nested formulas (search, if, lookup, index, etc) to get this to work.

 

[tonyyy]

If you're comfortable with a vba solution, you might give this a try...

Sub CreateSubcontractorSheets()

Dim LastCol As Long
Dim LastRow As Long
Dim Subcontractor As String
Dim i As Long
Dim j As Long
Dim ws As Worksheet

Application.DisplayAlerts = False
Application.ScreenUpdating = False

'''''   Deletes existing Electrical, Plumbing and HVAC worksheets
For Each ws In ThisWorkbook.Worksheets
    If ws.Name = "Electrical" Or ws.Name = "Plumbing" Or ws.Name = "HVAC" Then ws.Delete
Next

'''''   Establishes the range extents of the Primary worksheet; turns on AutoFilter
Sheets("Primary").Activate
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
ThisWorkbook.Sheets("Primary").Range(Cells(1, 1), Cells(1, LastCol)).AutoFilter

'''''   Filters the Primary worksheet to each Subcontractor, then copies/pastes to a new worksheet
For i = 1 To 3
    If i = 1 Then Subcontractor = "Electrical"
    If i = 2 Then Subcontractor = "Plumbing"
    If i = 3 Then Subcontractor = "HVAC"
    
    Sheets("Primary").Range(Cells(1, 1), Cells(LastRow, LastCol)).AutoFilter Field:=2, Criteria1:=Subcontractor
    Sheets("Primary").Range(Cells(1, 1), Cells(LastRow, LastCol)).Copy
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Subcontractor
    ActiveSheet.Paste
    '''''   Deletes non-required columns
    For j = LastCol To 1 Step -1
        If Cells(1, j).Value <> "Location" And Cells(1, j).Value <> "Description" And Cells(1, j).Value <> "Contract Amount" And Cells(1, j).Value <> "Budget" Then Cells(1, j).EntireColumn.Delete
        If Cells(1, j).Value = "Contract Amount" Then Cells(1, j).Value = "Budget"
    Next j
    Cells.Columns.AutoFit
    
    Sheets("Primary").Activate
    Application.CutCopyMode = False
Next i

'''''   Turns off AutoFilter
ThisWorkbook.Sheets("Primary").Range(Cells(1, 1), Cells(1, LastCol)).AutoFilter

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Comments are embedded in the code.

Cheers,

tonyyy

 

[Vickie Hartley]

I have never created or used a vba - have only heard about them. Will do some research on how this is done and will post a follow-up. Thanks for the help.

 

[Vickie Hartley]

I have researched a three sites and read multiple tutorials but when I tried to run the code I got an error 'runtime 1004. I'm sure it has to do with range but not familiar with code language to cipher out the problem. I did determine that sheet name correction needed to be done, which I did. Will keep trying but I have already spent 3.5 hours and have to get back to other tasks. If anyone can give me a hint on my error, I would really appreciate it.

 

[tonyyy]

It sounds like you've already changed "Primary" to match your sheet name.

When your get the Runtime 1004 error, which line of code is highlighted?

 

[Vickie Hartley]

That's the trouble - no highlighted or red font color on any words. Sorry I couldn't answer your question.

 

[tonyyy]

If you have a little time, you can try stepping through the code...

Open the Visual Basic Editor and place your cursor anywhere in the module with the code. Pressing the F8 key will highlight a line of code; pressing F8 again will execute that line and highlight the next. You can continue this until the entire program is run, or more likely in this case, an error is generated. And you'll know which line of code is causing the error.

 

[Vickie Hartley]

ThisWorkbook.Sheets("Original scope assumptions").Range(Cells(1, 1), Cells(1, LastCol)).AutoFilter

This line triggered the error 1004

 

[tonyyy]

Is the "Original scope assumptions" worksheet protected?

 

[Vickie Hartley]

No, it is not protected.