Excel Comparing and Pulling Data 1

[Rundvelt]

I was wondering if anyone knew the Excel equations that would make this work...

Let's say I have a sheet called Database. In database I have over 2000 lines of information about orders (containing delivery dates, order numbers, quantity, shipping costs, etc.)

Now, in addition to the Database sheet, I also have a sheet called Updates. This sheet is where I put in the corrections (let's say there were 6 crates of oranges delivered instead of 10).

I want excel to search the updates sheet to find matching order numbers (the only consistant piece of information). Then excel would automatically take the information from the update sheet and overwrite the information on the database sheet. It would be handy if I could clear out the updates sheet every so often (so that there is only text or numbers in the database sheet.)

That way I can place the excel corrections immediately into the database, refresh excel and have the database fields corrected.

The update sheet DOES NOT have the same format as the Database sheet. It can't have this because only certain information is updated by HQ. So, you just can't cut and copy the line (which is why I need to automate this)

Any ideas on how this can be done? I looked through the excel sheet formulas page, but that didn't really help.

Thanks a lot!
Robert

 

[xlbo]

have a look at either VLOOKUP (for the formula route) or the FIND method (in VBA)

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[CautionMP]

Robert,
How do you feel about macros? A couple of notes:[ol]
[li]Change Database in the following code to match your actual worksheet name[/li]
[li]Ditto for Updates[/li]
[li]Depending on where your data on the Database sheet begins you can change the '1' in the following line to match your data:
[tt]For lngOutputRow = 1 To 65536[/tt][/li]
[li]The x or y in the following lines will need to be updated to mirror the actual column values for your data [tt]wksDatabase.Cells(lngOutputRow, x) = rngRow.Cells(, x)[/tt][/li]
[/ol]

Option Explicit

Public Sub ProcessUpdates()
On Error GoTo Error_Handler
Dim wksDatabase As Worksheet
Dim rngUpdates As Range
Dim rngRow As Range
Dim lngOutputRow As Long
Dim strErrorText As String

'Get the rows to update from the Update sheet
Set rngUpdates = Application.InputBox("Select Range", "Define Lines", , , , , , 8)

'A range was selected, make sure it is on the Updates worksheet
If rngUpdates.Worksheet.Name <> "Updates" Then
  Err.Raise 9100
End If

'Set a reference to the Database sheet for output
Set wksDatabase = ActiveWorkbook.Worksheets("Database")

'Cycle through the selected range of Updates
For Each rngRow In rngUpdates.Rows
  'Cycle through wksDatabase
  For lngOutputRow = 1 To 65536
    'Find a OrderNumber in Database that matches Current Update row
    If rngRow.Cells(, 1) = wksDatabase.Cells(lngOutputRow, 1) Then
      'Assumes the order number is the first column of each worksheet
      'adjust these to match the real world.

      'If there is a match, overwrite the data.
      
      'Update column x
      'wksDatabase.Cells(lngOutputRow, x) = rngRow.Cells(, x)
      
      'Update column y
      'wksDatabase.Cells(lngOutputRow, y) = rngRow.Cells(, y)
      
      'Update has been performed so stop cysling through wksDatabase
      Exit For
    ElseIf wksDatabase.Cells(lngOutputRow, 1) = "" Then
      'End of data on wksDatabase has been reached so stop looking
      Exit For
    End If
  Next lngOutputRow
Next rngRow

'Prompt to delete the updates just processed
Select Case MsgBox("Do you want to delete the processed updates?", vbYesNo, "Confirm Delete?")
  Case vbYes
    Set rngUpdates = rngUpdates.Rows.Select
    rngUpdates.Clear
    strErrorText = "All updates Processed."
    strErrorText = strErrorText & "Source Data deleted"
  Case vbNo
    'Do nothing
    strErrorText = "All updates Processed."
    strErrorText = strErrorText & "Source Data retained"
End Select

'Clean up
Set rngUpdates = Nothing
Set wksDatabase = Nothing

Error_Handler:
  Select Case Err.Number
    Case 424 'InputBox canceled
      strErrorText = "No update range selected, routine canceled"
    Case 9100 'User defined error
      strErrorText = "The range selected was not on the Update worksheet." & vbCrLf
      strErrorText = strErrorText & "Exiting Update Routine"
    Case Else 'Don't know what error happened, alert user
      strErrorText = "Error Number: " & Err.Number & vbCrLf
      strErrorText = strErrorText & "Description: " & Err.Description & vbCrLf
      strErrorText = strErrorText & "Exiting Update Routine"
  End Select
  If Err.Number <> 0 Then
    MsgBox strErrorText, vbOKOnly, "Houston, we have a problem!"
  Else
    MsgBox strErrorText, vbOKOnly, "Process Complete"
  End If
End Sub

This code is only partially tested so there may be a couple of bugs that still need to be worked out, but I hope this will get you moving in the right direction.

CMP

 

[Rundvelt]

Thanks alot. I appreciate the help. It's a bit over my head, but I'll try and figure it out. Thanks.

 

[CautionMP]

A couple more nudges then.[ul]
[li]On the update sheet press Alt-F11 to open the macro window.[/li]
[li]Copy the code from the posting into the code window.[/li]
[li]Clean up the pasted code (any text that does not follow the 'tab' steps will need to be combined with the previous line).[/li]
[li]Make the changes I outlined in the original posting[/li]
[li]Save the module[/li]
[li]Toggle back to the workbook, turn on the Forms toolbar and draw a button on the worksheet.[/li]
[li]You should then be prompted with the assign macro dialog box, select ProcessUpdates.[/li]
[li]Click anywhere on the worksheet (removing focus from the button you just created)[/li]
[li]Click the button to fire the macro (crossing your fingers will help at this point.)[/li]
[/ul]
If I did my part correctly, the thing should run, if not you will get a dialog box telling you what I did wrong

CMP

P.S. Make the changes to a copy of the real workbook, don't use the actual production workbook as a guinea pig for code development.

 

[Rundvelt]

Thanks alot. I appreciate the help.

 

[SilentAiche]

Rundvelt,

A belated Welcome to Tek-Tips! If you have change for a nickle, I'll offer my two cents worth (which I'm genuinely hesitant to do given CMP's extensive solution!)

Although I suspect you may already be too heavily invested in your Excel efforts to change course, I must say that your situation begs for a database (Access) solution rather than a spreadsheet solution.

A simple scheme involving two access Tables (Customers, Orders) would be appropriate. Data would be entered on a Main Form (customers) and SubForm (orders). The Orders SubForm and underlying Table would maintain a history of each order while allowing you to use/manipulate just the most current and accurate data, perhaps based on a status or date/time field.

Philosophically, I prefer maintaining histories of critical data and using archive functions to regulate file size, etc.

Okay, enough of my rambling about the virtues of Access.

The update sheet DOES NOT have the same format as the Database sheet. It can't have this because only certain information is updated by HQ. So, you just can't cut and copy the line (which is why I need to automate this)

Two questions: 1) If you are overwriting the original order data anyway, why doesn't HQ just overwrite the corrections/updates directly to your Database sheet? 2) Is there a compelling reason for the Updates sheet to have a different format from the Database sheet?

I would recommend providing HQ with an Updates spreadsheet that is identical to your Database spreadsheet - you can hide the columns they don't need. Then the cut and paste option becomes simple.

Finally, I hope you have some safeguards in place that prevents Updates that don't have a corresponding order number on the Database sheet.

Just some thoughts - where's my change?

Good luck!
Tim

[blue]_____________________________________________________
If you need immediate assistance, please raise your hand.
If you are outside of Raleigh, raise your hand and say[/blue] [red]Ooh! Ooh![/red]

 

[CautionMP]

Now that someone else has moved this off topic...

Without knowing your ultimate output I agree, you need an Access solution (that's where my heart is).

I provided an Excel solution because it was an Excel question, if the macro is a little over your head you are going to have to learn new stuff to make this work, I would recommend learning Access. It is a much better tool for manipulating data, and with a little practice you can easily interface with Excel (my customers provide/expect data in Excel, but don't care how I get there).

In response Tim

...(which I'm genuinely hesitant to do given CMP's extensive solution!)

It was a 1[½] cup (as in Coffee) solution, as you can probably guess, I write a lot of macro code.

 

[SilentAiche]

Now I'm hesitant to admit I'd had two cups of coffee (need to switch to your brand, and administer with an IV drip...) BTW, y'all posted three times while I was typing.

Speaking of Off-Topic, and old Peanuts cartoon:

Lucy, to Linus: "Yuck! This hot chocolate is terrible. It tastes like hot water with a brown crayon mixed in."

Linus: "Ug, you're right. I better go add another crayon."

Tim

[blue]_____________________________________________________
If you need immediate assistance, please raise your hand.
If you are outside of Raleigh, raise your hand and say[/blue] [red]Ooh! Ooh![/red]

 

[Rundvelt]

Sorry, bit of clarification. I used an example to represent the situation that is going on (which would have been a much longer and more drawn out explanation.)

I know Access is the way to go. The problem is that the users are secretaries (you ask them to shut down the computer and they turn off the monitor, and use those accursed "Expert Mice") Asking them to learn another program might cause their heads to explode.

So, I really do need an excel solution (which has been supplied, thanks CautionMP!) but I'm more then happy to take experiment with others. The satisfaction? Your code will be used to assist in hospital operations.

I'm the happy medium between secretary and the more advanced users here, so I probably can implement the change, just not engineer it.

Anyways, thanks for the feedback. It is much appreciated.
Robert