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]

Alex,

This forum doesn't support attachments- when necessary people either post to a sharing site like Box.com or exchange email addresses through a Private Message (PM).

I've sent you my email via PM.

 

[AlexaS]

Alex,

This forum doesn't support attachments- when necessary people either post to a sharing site like Box.com or exchange email addresses through a Private Message (PM).

I've sent you my email via PM.

Is there any help that you can give me using the layout I gave you before? My raw data comes in with: location, vendor #, trans type (z50-53), amt and qty.

 

[AlexaS]

Hey, I tried the sumifs off of my raw data and it worked perfectly! I'm going to be able to do this with formulas after all, thanks!

 

[Jerry Sullivan]

Ok- that's good to hear. If you want some help with a VBA approach, the only part that was missing was a screen shot that shows the specific layout of your final report.