Creating summary spreadsheet from two seperate spreadsheets

prschilli

New Member
Joined
Mar 16, 2007
Messages
25
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?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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! :biggrin: )
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.) :oops:

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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top