Comparing Data in different workbooks/sheets and then acting

rnhutsko

Board Regular
Joined
Apr 22, 2005
Messages
87
This is a repost of my earlier question. Any help is greatly appreciated.

I keep a spreadsheet (mainsheet) that lists all the pertinent data for my inventory. It is sorted by make, Model, and year of the car. Each car has a stock number that is unique.

Every day the accounting department updates a proprietary accounting system and therefore my costs change daily. The accounting system has a function that I can manually export to an excel spreadsheet some very basic data. What I want to do is to write a macro in my spreadsheet that compares the stock# data in the "mainsheet" to the stock# data in the manual sheet and, if it is a match cut and paste the cost data to the mainsheet.
_________________

I have written a little bit, but I keep getting hung up on what I thinkl is a boolean deal. What i mean is that Excel has a built-in function exact(text1,text2) that returns true or false. I was trying to utlize this meathodolgy but don't know how. here is my concept:

Sub concept
Do until selction.value= ""
get first cell data in manual sheet
select mainsheet first cell
pastetest = false
Do until selection.value =""
If exact(manual sheet stock#, mainsheet stock#) = true then
copypaste data
pastetest = true
end If
If pastetest = false then
paste manualsheet data on last row
end if
Loop
move to next cell in manualsheet
Loop
end sub
_________________
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Need some more detail on the structure of both sheets.

What is the name of the manual sheet.
Where is the data on both the mainsheet, and the manual sheet.
What data from the manual sheet is being brought into the mainsheet, and where is it going.


Tony
 

rnhutsko

Board Regular
Joined
Apr 22, 2005
Messages
87
What is the name of the manual sheet.
Where is the data on both the mainsheet, and the manual sheet.
What data from the manual sheet is being brought into the mainsheet, and where is it going.


The manual sheet name is called "TempRPX"

The Mainsheet is called "Pre-Owned Inventory"

Transfering Cost, Days in Inventory to the data the macro finds a match for the stock number. If no match is found then it should transfer stock#, make, model, year, days in inventory, color, and miles. Assume that the columns are in indentical order as listed for both sheets (ie stocj# is column A, Make is B, etc)

Thanks in advance for the help.
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Try the following
Code:
Sub aaa()
 
 For Each ce In Sheets("TempRPX").Range("A2:A" & GetLastRow("TempRPX", "A"))
  placer = 0
  On Error Resume Next
  placer = WorksheetFunction.Match(ce, Sheets("Pre-Owned Inventory").Range("a:a"), 0)
  On Error GoTo 0
  If placer > 0 Then
   Range(ce.Offset(0, 1), ce.Offset(0, 6)).Copy Destination:=Sheets("pre-owned inventory").Range("b1").Offset(placer - 1, 0)
  Else
   Range(ce, ce.Offset(0, 6)).Copy Destination:=Sheets("pre-owned inventory").Range("A" & GetLastRow("pre-owned inventory", "A") + 1)
  End If
 Next ce
End Sub

Function GetLastRow(sh As String, col As String)
 GetLastRow = Sheets(sh).Cells(Rows.Count, col).End(xlUp).Row
End Function

It will cycle through the entries in TempRPX and transfer any matching data to Pre-Owned Inventory, and add any new entries to the bottom.


Tony
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,157
Members
417,011
Latest member
Amaden95

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
Top