Creating summary spreadsheet from two seperate spreadsheets

[prschilli]

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?

 

[HalfAce]

How about if we change all "Preferred ID" numbers to a 13 digit (text) string with leading zeros before comparing with the "Purchase ID" numbers.

Sub ListPreferredPurchases()
Dim LstRw1 As Long, LstRw2 As Long, NxtRw3 As Long
Dim Rng1 As Range, Rng2 As Range, CustId As Range, FndID As Range, c As Range
Dim FrstAddrss As String, OrigVal As String, ResltVal 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
Rng2.NumberFormat = "@"
For Each c In Rng2
  OrigVal = c.Value
  ResltVal = String(13 - Len(OrigVal), "0") & OrigVal
  c.Value = ResltVal
Next c
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

(That sound like it'll work?)

 

[prschilli]

I think this will work. For some reason I still have a invalid procedure call.

When you execute the command
Set FndID = Rng2.Find(CustId, LookAt:=xlWhole)
What does it assume? Is Custid the column in my preferred sheet? Or do I have to do something special in like format the Column to be General?

 

[HalfAce]

CustId is each customer ID in the Purchases sheet.
It's being told to loop down through each customer ID in Purchases, (one at a time) and
find that value in Rng2 (the customer IDs in the Preferred sheet.)

These ID are static values, and not the return of formulas - (yes?)
I don't think formatting is going to be an issue.

I don't see anything off hand that would generate the invalid call procedure error.

 

[prschilli]

Does that column have have a value of "CustID" as the title? Or maybe I have my tab spelled incorrect? As you know a invalid procedure call is so vague I have to do some more playing.

 

[HalfAce]

No, CustID is simply a variable we're using to refer to each cell as we loop through
column A of the Purchases sheet. (We could use any string we wanted there, 'x' if you
like, we just needed a way to refer to it somehow).

When you get the error, use Debug and hover your mouse over the variables & such.
You should get a little (yellow) tool tip that will tell you what excel views the value of each
variable at the time so you can compare that with what you believe it should be.

 

[prschilli]

HalfAce

Thank for the code and the great hints! Finally got it to work.

I greatly appreciate it!!

 

[HalfAce]

You're most welcome. Glad it helped.
Just out of curiosity, what did the problem turn out to be?

 

[prschilli]

After I put the VB in debug mode I noticed I was not getting any data in my range2.find test. But this was do the For Each loop I was excuting.

I can't recall how I had it coded, but I was not looping through the values in Rng1. If I rember correctly it was something really silly like I did not have the spreadsheet name spelled correctly in the VB code.

Kind of funny how we can create our own bugs!

 

[HalfAce]

Kind of funny how we can create our own bugs!

Those are the hardest ones to find.