Advanced Help Needed. Dynamic Index? Match? or Vlookup with Multiple Criteria

[laplacacm]

I am having difficulty figuring out how to go about filling in my excel sheet because I have a lot of criteria to meet.

My goal is to do identify my customer in Worksheet 1: 2011 SAP Data's Column F (in this case, F2, Controller Service & Sales) and then be able to copy the value in J2 to Worksheet 2: IND Sales.

For Example, if I find F2 (Controller Service & Sales) I am trying to automate a way to get Worksheet 1, "J2" to end up in Worksheet 2, "M3" because the value correlates with "DAC" and "July".

Another Example, I would like to get Worksheet 1, "J4" to end up in Worksheet 2, "I4" because the value correlates with "IEI" and "March".

An additional problem that I have been facing is that every customer doesn't have the same values in column H. If you look at the customer if "F18", their first product is "IW". This adds another level of criteria.


-- removed inline image ---

Worksheet 2: IND Sales
http://dl.dropbox.com/u/5170983/Worksheet 2 IND Sales.bmp

For me, this is quite difficult, so if anyone out there is able to help, I would be so grateful!

If you think you are able to help and have some questions or need some clarification, please let me know!

Thank you in advance!

 

[Leith Ross]

Hello laplacacm,

I can help you do this but I need to know a few more details.

• Are there any reasons VBA macros could not be used?
• Can you post the workbooks for review?
• What version or versions of Excel are you using?

Sincerely,
Leith Ross

 

[laplacacm]

Hello Leith Ross,

I really appreciate you taking your time to look at this!

1. No they can definitely be used, it is just that I am not very proficient in them. That is why I haven't went that route. If can be done that way, awesome!
2. Here is a dropbox link to the file.
http://dl.dropbox.com/u/5170983/POS 2007 to 2011-04.xlsx
3. I am using Excel 2010. I am able to use any version though!

Thank you! I'm going to send you a gift card or something!

 

[Leith Ross]

Hello laplacacm,

Thanks for post the workbook. It answers quite a few questions I had about the data layout. I have one more question.

Let's use customer US00017 as an example. When IEI is found in column "H", I take it all the month amounts from March through June (column I) need to be copied to "IND Sales" I4:J4, correct?

Sincerely,
Leith Ross

 

[laplacacm]

Leith Ross,

That is correct. For customer US00017, if IEI is in Column H, then March (2011/03), May (2011/05), June (2011/06) and July (2011/07) will be populated in IND Sales.

IND Sales would have the following:

I4 = 64.00
K4 = 152.00
L4 = 5508.00
M4 = 192.00

The same would go for when DC is found in Column H of Worksheet 2011 SAP Data and so on and so forth for all of the different customers. Worksheet IND Sales/G8:M8 would then be populated with the corresponding dollar values for January through July.

Thank you!

 

[Leith Ross]

Hello laplacacm,

This took much longer than I originally thought due to the peculiarities of the layout. Here is the macro that will copy the sales data from the "2011 SAP Data" to the "IND Sales" worksheet. The file has uploaded to http://www.mediafire.com/?dan97sv7d7wd4jv

Sub CopySalesData()

  Dim Cell As Range
  Dim CustRng As Range
  Dim DateRng As Range
  Dim DstRng As Range
  Dim DstWks As Worksheet
  Dim NextCust As Range
  Dim RngEnd As Range
  Dim SalesByMonth() As Variant
  Dim SBU As String
  Dim SBUcnt As Long
  Dim SBUs As Collection
  Dim SrcRng As Range
  Dim SrcWks As Worksheet
  
  
  ' Define the Source and Destination worksheets and starting cells
    Set SrcWks = Worksheets("2011 SAP Data")
    Set DstWks = Worksheets("IND Sales")
    
    Set SrcRng = SrcWks.Range("E2")         'Customer column on Source worksheet
    Set DstRng = DstWks.Range("D2")         'Customer column on Destination worksheet
      
      
     ' Dynamically size the source range
       Set RngEnd = SrcWks.Range("I:I").Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False)
       Set SrcRng = SrcWks.Range(SrcRng, RngEnd)
       
     ' Mark the end of the destination range
       Set RngEnd = DstWks.Cells(Rows.Count, "F").End(xlUp)
              
       ' SBUs return the row offset from the Customer cell for a given SBU
         Set SBUs = New Collection
      
         For Each Cell In DstRng.MergeArea
           SBUs.Add SBUcnt, Cell.Offset(0, 2).Value
           SBUcnt = SBUcnt + 1
         Next Cell
      
      
       ' Copy sales data to the "IND Sales" worksheet
       
         Set NextCust = SrcRng.Cells(SrcRng.Rows.Count, 1)
         
         Do
           Set CustRng = SrcRng.Columns(1).Cells.Find("*", NextCust, xlValues, xlWhole, xlByRows, xlNext, False)
           
           If CustRng = "Result" Then Set CustRng = CustRng.Offset(1, 0)
           
         ' Date Range ends with an empty cell
           Set DateRng = SrcWks.Range(CustRng.Offset(0, 4), SrcRng.Columns(5).Cells.Find("", CustRng.Offset(0, 4)))
                      
           ReDim SalesByMonth(1 To 12)
           
           SBU = DateRng.Cells(1, 1).Offset(0, -1)
           
             For Each Cell In DateRng
               If Cell = "" Then Exit For
               
               If Cell = "Result" Then
                  DstWks.Cells(DstRng.Row + SBUs(SBU), DstRng.Column + 3).Resize(1, 12).Value = SalesByMonth
                  ReDim SalesByMonth(1 To 12)
                  SBU = Cell.Offset(1, -1)
               Else
                  SalesByMonth(Month(Cell)) = Cell.Offset(0, 1)
               End If
             Next Cell
             
           ' DstRng is a merged range. Offseting by row goes to the next merged range.
             Set DstRng = DstRng.Offset(1, 0)
             
             If DstRng.Row > RngEnd.Row Then Exit Sub
             
           Set NextCust = CustRng.Offset(1, 0)
         Loop
      
End Sub

Sincerely,
Leith Ross