Excel Formula to check three variables and return value

[AlexaS]

Hello,

I'm to the point of pulling my hair out. I've tried this in formulas (if, if/and, if/vlookup, if/vlookup/and) and I've been trying to write a macro for this (which I'm thinking needs to loop).

Problem: I have a report that is broken into three categories.

Column A includes locations, Column B includes vendors. Across the top, the monthly report is broken into four classifications (trans types), which are then further subdivided by quantity and cost. (Rough estimation is provided below)

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Locations
[/TD]
[TD]Vendor
[/TD]
[TD]Z53
[/TD]
[TD][/TD]
[TD]Z50
[/TD]
[TD][/TD]
[TD]Z51
[/TD]
[TD][/TD]
[TD]Z52
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Qty
[/TD]
[TD]$
[/TD]
[TD]Qty
[/TD]
[TD]$
[/TD]
[TD]Qty
[/TD]
[TD]$
[/TD]
[TD]Qty
[/TD]
[TD]$
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]C
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]D
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]D
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


My data comes in sorted by location, and subtotaled by vendor and trans type. So I'll have a row of data that includes:
Location 1, Vendor A, Z51, $200, 2
Location 1, Vendor A, Z53, $150, 2
Location 1, Vendor B, Z53, $150, 1
...

What I need to do is pull the data for Z53 based on Location & Vendor. So from the data is would read Location 1 & Vendor A and then find them on the report and paste into the appropriate row (in my report Z53 qty is col E and $ is col F).

I was trying to do a macro that within a determined range (finding the first and last instance of Z53) would match location and vendor to the monthly report and paste the values of Qty and $ into the appropriate fields. Below is what I managed to mangle:

Dim Holder1 As Range
Dim Holder2 As Range
Dim Holder3 As Range
Dim Holder4 As Range
Dim Found As Range
Dim y As Range
Dim x As Range
Dim Z0 As Range
Dim Z1 As Range
Dim Z2 As Range
Dim Z3 As Range

'this section finds the first and last instance of Z52
Set y = Cells.Find("Z52", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Select
Set x = Selection.Find(What:="Z52", After:=ActiveCell, LookIn:= _
            xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
            xlNext, MatchCase:=False, SearchFormat:=False)
            
Set Z2 = Range(Cells(y, "A"), Cells(x, "E"))
Range(Z2).Select
Set Holder1 = Cells(y, "A")
Set Holder2 = Cells(y, "B")
Set Holder3 = Cells(y, "E")
Set Holder4 = Cells(y, "D")
Sheets("Sheet3").Select
Range("B:B").Select
Set Found = Selection.Find(What:=Holder1, After:=ActiveCell, LookIn:= _
            xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
            xlNext, MatchCase:=False, SearchFormat:=False)
If Not Found Is Nothing Then
    Found.Activate
    If ActiveCell.Offset(0, 1) = Holder2 Then
        Range("E4").Value = Holder3
        Range("F4").Value = Holder4
    ElseIf Found.Offset(1, 0) Then
        If ActiveCell.Offset(0, 1) Then
            Range("E4").Value = Holder3
            Range("F4").Value = Holder4
'And this is where I started realizing that this could feasibly go out to fifty 
'or 100 "elseif"s and is insanely long
            
    
End Sub

I was originally trying to match from the report to the data and then copy back to the report, but I had the idea of instead taking the location and vendor from the data and finding the match on the report and pasting. That way I wouldn't have to worry about errors (unless a new location was added that was not included on the report - which I think is an easier fix than trying to go the other way, but maybe not).

I'm out of ideas, any help would be appreciated. Thanks.

 

[Jerry Sullivan]

Hi Alexa, What you describe is what PivotTables were born to do!

Using a PivotTable, the raw data you have can be summarized in the report you want in about a minute (no VBA, no formulas required).

If you haven't tried using PivotTables, you might find this tutorial helpful.
PivotTable I: Get started with PivotTable reports in Excel 2007 - Excel - Office.com

 

[AlexaS]

This did create a report. The problem is, I have a report that I need to place the data into. So even with the pivot table, I still need to get the data from there to my report.

I can get a formula to work, if I know where in the data my information is. The problem with that is my locations aren't always going to be set. Any ideas?

 

[AlexaS]

Ok, I've managed to create a potential macro solution from my raw data. The problem is that I will have to do this for each location, inside each location for each vendor and then I will have to do it four times for each trans type. The code that follows is one location, vendor and trans type.

Sub Test()
Dim CellLocation As String
Dim Holder1 As Range
Dim Holder2 As Range
    
    Columns("A:A").Select
    Set Found = Selection.Find(What:="10061701WI", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    If Not Found Is Nothing Then
    Found.Activate
    CellLocation = ActiveCell.Address
        If Found.Offset(0, 1).Value = "105039" Then
            If Found.Offset(0, 2).Value = "Z53" Then
                Set Holder1 = Found.Offset(0, 4)
                Set Holder2 = Found.Offset(0, 3)
            Else
                Set Found = Selection.FindNext(After:=ActiveCell)
                If Not Found.Address = CellLocation Then
                Found.Activate
                    If Found.Offset(0, 1).Value = "105039" Then
                        If Found.Offset(0, 2).Value = "Z53" Then
                            Set Holder1 = Found.Offset(0, 4)
                            Set Holder2 = Found.Offset(0, 3)
                        Else
                            Set Found = Selection.FindNext(After:=ActiveCell)
                            If Not Found.Address = CellLocation Then
                            Found.Activate
                                If Found.Offset(0, 1).Value = "105039" Then
                                    If Found.Offset(0, 2).Value = "Z53" Then
                                        Set Holder1 = Found.Offset(0, 4)
                                        Set Holder2 = Found.Offset(0, 3)
                                    Else
                                        Set Found = Selection.FindNext(After:=ActiveCell)
                                        If Not Found.Address = CellLocation Then
                                        Found.Activate
                                            If Found.Offset(0, 1).Value = "105039" Then
                                                If Found.Offset(0, 2).Value = "Z53" Then
                                                    Set Holder1 = Found.Offset(0, 4)
                                                    Set Holder2 = Found.Offset(0, 3)
                                                End If
                                            End If
                                        Else
                                        End If
                                    End If
                                End If
                            Else
                            End If
                        End If
                    End If
                Else
                End If
            End If
        Else
            
        End If
        Range("H1").Value = Holder1
        Range("I1").Value = Holder2
    End If
 
End Sub

And once I go to actually put the information into the report (the holder values), instead of just the test that I was doing putting the values into a spot on the same sheet, it's going to add maybe another twenty lines (5 lines of code to go to the other page and put the value in the correct spot, times 4 searches).

Could I maybe consolidate a bit and place the "Range.Value = Holder" portions before the last end if? (I just tested this and yes I can - the above code now reflects this change)

Any suggestions to cut this down would be most appreciated (especially since at this point I haven't confirmed with the user just how many locations we are ACTUALLY talking about).

THANKS!

 

[Jerry Sullivan]

The data could be transferred using formulas. If your each item is unique (meaning that there is only one listing in your data for each combination of the 3 criteria), then an Index-Match formula will probably work. Otherwise a Sumifs could be used. I'd be glad to help with those formulas.

I'm not understanding your set up though. You have the raw data, and the report you found that you can make with a PivotTable. Is there a 3rd set of data that represents the "final" report? If so, how is it different from the report you made with a PivotTable?

EDIT: I hadn't seen the reply with the code you just posted when I wrote this.
I'll have a look a little later this morning.

 

[AlexaS]

The data could be transferred using formulas. If your each item is unique (meaning that there is only one listing in your data for each combination of the 3 criteria), then an Index-Match formula will probably work. Otherwise a Sumifs could be used. I'd be glad to help with those formulas.

That would be great!

I'm not understanding your set up though. You have the raw data, and the report you found that you can make with a PivotTable. Is there a 3rd set of data that represents the "final" report? If so, how is it different from the report you made with a PivotTable?

The approximation that I created in my first post is the actual set-up for the users report. I am updating a report that is already in use. Plus, the pivot table has two problems: 1) the location from the raw data is just a number and not the named location that they use, 2) for trans type Z50 I need the QTY followed by the $. The pivot table is set up to display the QTY for the four trans types and then the $ for the four trans types.

The raw data is the only data, it's just that the set up of the pivot table is not how the end user needs it. What I am doing is doing a "monthly report" simply to get it into the proper locations, at which point I will have a macro that will copy and paste the values to the "final" report. The "final" report is set up with Row one indicating either "Prior Year" (for the rolling 12mtd functionality it needs) or the month of the current year. Row two indicates for prior year which quarter it summarizes (since the report is run quarterly). Row three indicates the breakdown by trans type, row four has column headings in A & B for the named location and corresponding vendor, and then under the trans type has the distinction for quantity and cost. Then starting in cell C5 the actual data starts. It's very big but it's how they need to see it.

EDIT: I hadn't seen the reply with the code you just posted when I wrote this.
I'll have a look a little later this morning.

That would be a great help, considering that I have: 27 named locations (which might have multiple corresponding numbered locations in the system), 31 vendors (which multiple locations utilize) and I will have to do each set four times for the different trans types. ANYTHING that helps cut down the code would be appreciated.

Thanks!

 

[Jerry Sullivan]

That helps a little, but I'm still unclear.

Firstly- if you do decide to go the VBA route, there are much better approaches than a series of If...Else statements for each Location - so I'd suggest you forgo that attempt.

It sounds like you are trying to use one macro to create an intermediate report, then a second macro to transfer information from the intermediate report to the final report.
If you could have one macro that put the raw data into the final report, would that be better?

Regarding the PivotTable, by putting the Trans Types field in the Column Labels area of the Pivot Report, it will produce the layout you want for the Qty/Cost.
That point is moot though if you don't really need the intermediate report.

 

[AlexaS]

That helps a little, but I'm still unclear.

Firstly- if you do decide to go the VBA route, there are much better approaches than a series of If...Else statements for each Location - so I'd suggest you forgo that attempt.

I know there are better options. I'm still just so new at this that I don't know what those better options are. I'm thinking maybe a loop of some nature to run through the raw data and deposit it to the report? But none of the loops I tried to create worked.

It sounds like you are trying to use one macro to create an intermediate report, then a second macro to transfer information from the intermediate report to the final report.
If you could have one macro that put the raw data into the final report, would that be better?

Yes. However, the issue I'm having is that for one named location (Appleton) and vendor there are potentially multiple numbered locations that are included in that amount. For example: 10061700W and 10061701W are both Appleton, and I need the qty and cost information for vendor 1 to be subtotaled.

Regarding the PivotTable, by putting the Trans Types field in the Column Labels area of the Pivot Report, it will produce the layout you want for the Qty/Cost.

You're right, I moved the trans type above the values header. However I still need the named locations and vendors that correspond to the numbers from the system.

I'm open to suggestions on a better way to do the VBA, I'm open to suggestions on formulas (I'd almost prefer formulas because then it's copy and paste if/when new locations/vendors are created).

Let me know what is still unclear and I'll try to explain better.

Thanks!
-Alex

 

[Jerry Sullivan]

The "final" report is set up with Row one indicating either "Prior Year" (for the rolling 12mtd functionality it needs) or the month of the current year. Row two indicates for prior year which quarter it summarizes (since the report is run quarterly). Row three indicates the breakdown by trans type, row four has column headings in A & B for the named location and corresponding vendor, and then under the trans type has the distinction for quantity and cost. Then starting in cell C5 the actual data starts. It's very big but it's how they need to see it.

I'd be happy to help you with some VBA code that updates the "final report" directly from the raw data with no intermediate report.

Could you post a mockup that shows the specific details of the final report that are only summarized in the above quote?

The issue of converting Location numbers to Location names could be handled through a lookup table - do you have such a table in your workbook?

 

[AlexaS]

Is there a way to attach an excel document? Or is there another way to get you a sample without downloading anything? I'm on a work computer and can't download things.