Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Creating summary spreadsheet from two seperate spreadsheets

This is a discussion on Creating summary spreadsheet from two seperate spreadsheets within the Excel Questions forums, part of the Question Forums category; Here is my problem. I have two spreadsheets. I need to be able to match information from detailed spreadsheet for ...

  1. #1
    New Member
    Join Date
    Mar 2007
    Posts
    25

    Default Creating summary spreadsheet from two seperate spreadsheets

    Here is my problem.

    I have two spreadsheets. I need to be able to match information from detailed spreadsheet for specific information from a lookup spreadsheet.

    The detailed sheet (call it Purchases) has information about what was purchased during a month.
    Columns:
    A (CustomerID) B (How much purchased) C (What was purchased) D ( Quantity purchased)
    ex)
    A B C D
    1234 $12.00 Hex Nuts 12
    1111 $1.00 Cancy bar 1
    1011 $100.00 Lawn Chair 1

    The specific information sheet (call it preferred) contains a list of what customers are "Preffered" so only a few customers will show up.
    Columns:
    A (Customer ID) B (Name) C (Address) D (City) E (State) F (Zip)
    ex)
    A B C D E F
    1111 Joe Blow 123 Main St New York NY 10000

    What I want is a way to create another sheet that will list the information from the purchase spreadsheet for the customers from the preferred spreadsheet.

    I can envision that this will be done with a Macro using VBA. I will have to admit I am very novice. I may understand the concept, but I do not know how to code to VBA part.

    Ultimately I think my idea would be to have my third sheet (preferred purchases) to run the macro. It would have to resad the detailed sheet and be able to read the summary sheet and produce output for those customers who actually matched. This preferred purchase spreasheet would have the same format as the purchases spreadsheet but it would not have all the data in it from the purchases just for that purchase where the custid from purchased equals the custid from preferred.


    Any ideas?

  2. #2
    MrExcel MVP HalfAce's Avatar
    Join Date
    Apr 2003
    Location
    Alaska
    Posts
    9,230

    Default

    Hello prschilli, welcome to the board.
    So. . . is the only indication of whether or not a customer is "preferred" going to be if their customer id(?) - name(?) is found in column A or B of your sheet "preferred"?

    Am I correct in thinking you only want the preferred customers from the "Purchases" sheet to show on the "preferred purchases" sheet if there are/were purchases made?

  3. #3
    New Member
    Join Date
    Mar 2007
    Posts
    25

    Default

    I think the answer to your question is yes.

    Yes the only indication to if the customer is "preferred" is that they will have and entry in the preferred sheet. The customer id (column a) on the preferred sheet is what I am using as my key.

    Column A (cust id) in the "preferred" sheet will need to match Column A (CustID) in the "purchase" sheet.

    In the preferred sheet I want to definately only want to show the preferred customers who made purchases. This does assume that the preferred customer does not always make a purchase for the month.

  4. #4
    MrExcel MVP HalfAce's Avatar
    Join Date
    Apr 2003
    Location
    Alaska
    Posts
    9,230

    Default

    OK, here's what I'm thinking. Code that will:
    Loop through the IDs in column A of the sheet named 'Purchases'
    If that ID is found in column A of the sheet named 'Preferred' then check to see if there's an 'amount purchased' value in column B for that ID in 'Purchases' sheet.
    If there is then copy that entire row from the 'Purchases'(?) sheet to the 'preferred purchases' sheet and then move on down to the next ID in 'Purchases' sheet, etc.

    That sound like what you're looking to do?

  5. #5
    New Member
    Join Date
    Mar 2007
    Posts
    25

    Default

    Sound perfect. I looked at my data closer.

    I do want to add you could have a negative amount for the amount purchased (because returns). And for some reason I do see a 0.00 as an amount purchased. My guess is that one of the sales people made a keying mistake.

  6. #6
    MrExcel MVP HalfAce's Avatar
    Join Date
    Apr 2003
    Location
    Alaska
    Posts
    9,230

    Default

    OK, let's start with this.
    It assumes:
    1) Your sheets are named "Purchases", "Preferred" and "Preferred Purchases".
    2) There may be multiple purchases for any customer on "Purchases" sheet.
    3) You only want to copy the rows where the value in column B of "Purchases" sheet
    is greater than zero.
    It can be run from any sheet you like. (or even one you don't like! )
    Code:
    Sub ListPreferredPurchases()
    Dim LstRw1 As Long, LstRw2 As Long, NxtRw3 As Long
    Dim Rng1 As Range, Rng2 As Range, CustId As Range, FndID As Object
    Dim FrstAddrss As String
    With Sheets("Purchases")
      LstRw1 = .Cells(Rows.Count, "A").End(xlUp).Row
      Set Rng1 = .Range("A2:A" & LstRw1)
    End With
    With Sheets("Preferred")
      LstRw2 = .Cells(Rows.Count, "A").End(xlUp).Row
      Set Rng2 = .Range("A2:A" & LstRw2)
    End With
    Application.ScreenUpdating = False
    For Each CustId In Rng1
      With Sheets("Preferred")
        Set FndID = Rng2.Find(CustId, LookAt:=xlWhole)
        If Not FndID Is Nothing Then
          FrstAddrss = FndID.Address
          Do
            If CustId.Offset(, 1) > 0 Then
              NxtRw3 = Sheets("Preferred Purchases").Cells(Rows.Count, "A").End(xlUp).Row + 1
              CustId.EntireRow.Copy Sheets("Preferred Purchases").Cells(NxtRw3, "A")
            End If
            On Error Resume Next
            Set FndID = .FindNext(.FndID)
            On Error GoTo 0
          Loop While FndID.Address <> FrstAddrss
        End If
      End With
    Next
    Application.ScreenUpdating = True
    End Sub
    Does this get you close?

  7. #7
    New Member
    Join Date
    Mar 2007
    Posts
    25

    Default

    HalfAce.

    I think this might work, but my data has thrown me for a interesting loop.

    1) On the preffered sheet for some reason the same customerid can be on the sheet more than once. Another problem is that on this sheet the CustID is a number (which you probably would expect) BUT.

    2) CUSTID on the Purchases sheet is a 13 digit string with a 0 filled format.

    Do I need to remove dups and convert my custid on the preffered customer to match the custid on the purchase sheet?

    Right now I am getting a run time error that I about to research. Run-Time error '5' Invald procedure call or argument. Funny how undescriptive VB error can be.

    If you have any ideas on how I need to handle the duplicate data and bad type casting let me know.

  8. #8
    New Member
    Join Date
    Mar 2007
    Posts
    25

    Default

    This might be a silly question but what is the range of values being expected by the FndID as Object?

    For some reason this where the code dies.

    If you want better sample of the data and code let me know.

  9. #9
    MrExcel MVP HalfAce's Avatar
    Join Date
    Apr 2003
    Location
    Alaska
    Posts
    9,230

    Default

    Not a silly question at all.
    FndID should be dimmed as 'Range', not 'Object'.
    It will work either way but the data type is in fact a range (which is an object) but it should be
    specified as to which type of object.
    (Don't know why I used Object in the beginning. That's just not a good programming habit.)

    I don't think the customer ID being found on the 'Preferred' sheet multiple times would be a problem.
    It's simply searching each 'Purchases' sheet customer ID to see if it's found in 'Preferred' sheet and (if so) copying from the 'Purchases' sheet, so it shouldn't matter how many times it may appear in the 'Preferred' sheet. It's just seeing if it's there or not.

    As for the CustomerID being different between the two sheets, yeah, that will be a problem.
    That can be handled a number of ways.
    For example we could loop through the second sheet and remove all leading zeros in the ID (but then what if you have any that are supposed to begin with zero?)
    Or we could go through the IDs on the first sheet and make them all 13 digits by adding leading zeros.

    Plenty of other options too but it all kinda depends on how you want to handle it.

  10. #10
    New Member
    Join Date
    Mar 2007
    Posts
    25

    Default

    Halface. Thanks for the quick response.

    I have not had a chance to clarify with my users yet, but I am going with the assuption that these sheets are the best output I will get.

    I was able to get a solution in Access but I did add the leading zeros to the preferred customers ID. I also had to remove the duplicate customers from that sheet so I did not have duplicate results from my Access query (used an advanced filter in Excel for this). To format my customer ID into a 13 digit string with leading zeros I did a simple format(cstr(custid)"0000000000000") and made another table to do my Access query. Does the VBA in excel work the same way?

    I really would like to implement a Macro solution so that my end users did not have to have me get involved with Access, but for now Access was my quick and dirty solution.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com