Match data on one sheet with data in multiple columns on next sheet

[Jeremy King]

This is my first post. I didn't find another post that looked like mine so I apologize if this is a duplicate post.

I am creating a spreadsheet that shows 4 years worth of pavement maintenance activities. This spreadsheet is tied to another sheet with all of the budget info associated with these maintenance activities.

Our roads are associated with control-section numbers and one roadway may have more than one control-section number depending on the length, etc.

On the planning sheet, I would like to have a way to look at the roadway (column 1, sheet 1) and the control section (column 2, sheet 1) and match this case with the info on the budget sheet, roadway (column 1, sheet 2) and control section (column 2, sheet 2) and if there is a match, it will display the info in the "description of work" column 3. I need it keep looking for the match until there is no more matches...e.g. If there are two instances of work on the same roadway/control section, it would put "description of work1" " + " "description of work2" in the cell and then stop looking when there are no more matches.

This budget info needs to be filterable/table format so whoever is working with the info can sort it to fit their needs. I don't know how to paste a screenshot so below is my best reproduction of the two sheets...



Sheet 1 - Pavement Plan

Roadway | Control Section|Work Description FY 18 |Work Description FY 19 | Work Description FY 20|
------------------------------------------------------------
FM-0324 | 0176-08| needed formula | needed formula | needed formula
----------------------------------------------------------------



Sheet 2 - Budget Plan (FY 18)

Roadway | Control Section| Other budget data columns | Description of Work
------------------------------------------------------------------------------------------
FM-0324|0176-08| unrelated info | Base repair
---------------------------------------------------------------------------------------------
FM-1969|2256-26| unrelated info | Hotmix Surface
---------------------------------------------------------------------------------------------
FM-0324 | 0176-08| unrelated info | Mill & Inlay
-----------------------------------------------------------------------------------------------
FM 1725| 1254-01 | unrelated info | Base Repair




Expected result on Sheet 1 in "Work Description FY 18" column: Base Repair + Mill & Inlay

I'm sorry this is so long, but I didn't know how to explain it. My IT section told me to google Mr. Excel...so here I am.

Thanks in advance to any help!!!

 

[RobertSF]

Welcome to Mr. Excel and a great big boo to your IT section for blowing you off. I'm in IT myself, and I would never tell a user to googe a technical issue.

Anyway, I'm pretty good with Excel, and I know of no way to make a formula do what you describe (i.e. keep looking for matches). For this, I think you're going to need VBA code.

While you can't upload pictures, you can post links to picture, and you can look for and find a picture host that doesn't require that you register an account. Here's one such place: https://imgbb.com/

You can also click the "Go Advanced" button when you're posting a message here, and the advanced toolbar has a way to create tables that let you describe a spreadsheet.

If you can, post a couple of pictures.

 

[Jeremy King]

Thanks for the help with the photo links. Below are two links. I have a photo of the Pavement Plan spreadsheet where the formula needs to be placed (Link 1) and a photo of the Budget Plan where the info is drawn from (link 2).

Link 1: https://ibb.co/dq8Tnx
Link 2: https://ibb.co/dq8Tnx

Is there any way to do "nested Ifs"?

If VBA is the only way, does any one have any suggestions? I am not a coder at all. The closest thing to VBA that I've done is recording a macro! lol

Any help is greatly appreciated. This spreadsheet is very helpful to our Maintenance Supervisors, but as it stands, you have to do dual entry. I'm trying to make this more user friendly and to eliminate double work. These guys don't have time to sit behind a computer and double work. They have to be out on the roads making things happen.

Thanks in advance!

 

[Jeremy King]

I pasted the same link twice.

Here is the Pavement Plan spreadsheet photo.

https://ibb.co/gJ6nfH

 

[RobertSF]

Let me take a look at the images you posted. I see the second one as well. Meanwhile, I can answer your question on nested IFs.

Yes, you can nest them. An IF function has three arguments: a) the logical test, b) what to do if the logical test is true, c) what to do if the logical test is false. To nest an IF, put it in place of b) or c). For example, suppose there's a value in A1. If the value is less than 1, it's an error. If the value is more than 3, it's not 3. And if the value is 1, 2, or 3, the result will be 11, 22, or 33.

Here's the formula: =IF(A1<1,"err",IF(A1<4,IF(A1=3,"33",IF(A1=2,"22","11")),"not 3"))

They're horrible to look at, aren't they? I'd take the formula and break it apart to understand it.

Here's the first pass.
=IF(A1<1,"err",IF(A1<4,IF(A1=3,"33",IF(A1=2,"22","11")),"not 3"))

Now we take the blue part, which executes if B1 is not less than 1.
IF(A1<4,IF(A1=3,"33",IF(A1=2,"22","11")),"not 3")

Then you would take the green part, break it down, and so on and so forth.

VBA has a lot more elbow room. The code would be:

Option Explicit


Public Function NestedIf(A1 As Integer) As Variant
    
    '=IF(A1<1,"err",IF(A1<4,IF(A1=3,"33",IF(A1=2,"22","11")),"not 3"))

    Dim result As Variant

    If A1 < 1 Then
        result = "err"
    Else
        If A1 < 4 Then
            If A1 = 3 Then
                result = "33"
            Else
                If A1 = 2 Then
                    result = "22"
                Else
                    result = "11"
                End If
            End If
        Else
            result = "not 3"
        End If
    End If
    
    NestedIf = result

End Function

Do you know how to get into the VBA editor by pressing F11? If so, you could get into VBA, Insert a Module, which will give you a blank page where you can past in the code. Then, on a sheet, if you enter the formula =NestedIf(3), you'll get 33.

I'll take a look at the pictures and see if I can think of something.

 

[RobertSF]

I may have been wrong about this not being possible without VBA. Have you heard of array formulas? I was googling and found this. It might be possible to adapt it to your case.
https://exceloffthegrid.com/vlookup-list-all-the-matching-items/

 

[RobertSF]

Here's one solution. It's a function called "summary," so in a cell you type =summary(ColA, ColB), where ColA and ColB are the two values you want to find in the budget sheet.

You can download an example spreadsheet here: http://clicketyhome.com/jeremyking.xlsm

I know you said you didn't code, but here's the code in case someone wants to make it more efficient.

Option Explicit

Public Function Summary(ColA As String, ColB As String)
   
    Dim source As Variant
    Dim ndx As Long
    Dim result As String
    
    source = ThisWorkbook.Sheets("Sheet1").ListObjects(1).Range
    
    result = ""
    
    For ndx = 2 To UBound(source)
        If source(ndx, 1) = ColA Then
            If source(ndx, 2) = ColB Then
                result = result & source(ndx, 3) & Chr(10)
            End If
        End If
        If source(ndx, 1) = ColA And source(ndx, 2) > ColB Or source(ndx, 1) > ColA Then Exit For
    Next ndx
    
    If result > "" Then result = Left(result, Len(result) - 1)
    
    Summary = result
    
End Function