Matching two spreadsheets
 

Hey guys, I have another question, nerve-wracking for me, probably cake
for you.

I have two similiar spreadsheets with about 6,000 rows of data and a
bunch of columns. They're close, but not exact. Is there a way to match
the two up? Spreadsheet 1 and 2 look like this:


100 100
200 200
300 400
400 500
500 600
600 700
700
800


The common denominator would be the first row, let's say A1-A6000, with
a unique product code.

Basically, we have one master spreadsheet that was originally uploaded
to our SQL server, and then items were deleted manually on our web
interface because they were out of stock. I want to delete the items
that are in the master spreadsheet that don't exist in spreadsheet #2 -
so basically if an item isn't on spreadsheet 2, it deletes the entire
row in spreadsheet 1 so that at the end, there's an even number of rows
in each spreadsheet and they line up. Also, the columns don't match up
exactly in both spreadsheets - only the product codes are the same. Not
sure if that matters, really, but I figured I'd let you know.

Make any sense?

Just wanted to say thank you, you guys have been such a big help. Wish
I could give something back.


--
method373
------------------------------------------------------------------------
method373's Profile: http://www.excelforum.com/member.php...o&userid=29888
View this thread: http://www.excelforum.com/showthread...hreadid=513934

Matching two spreadsheets
 

Try this in a command button:

Set rng1 = Range("range1")
Set rng2 = Range("range2")
rcnt1 = rng1.Rows.Count
rcnt2 = rng2.Rows.Count
For rc1 = 1 To rcnt1
foundit = False
For rc2 = 1 To rcnt2
If rng1.Cells(rc1, 1).Value = rng2.Cells(rc2, 1).Value
Then
foundit = True
GoTo nextrng1
End If
Next rc2
If Not foundit Then
' rng1.Cells(rc1, 2).Value = "delete"
rng1.Cells(rc1, 1).Delete shift:=xlShiftUp
rcnt1 = rcnt1 - 1
End If
nextrng1:
Next rc1
MsgBox ("done.")


But test it first!

Hans

Matching two spreadsheets
 

Hi Hans,

http://www.cpearson.com/excel/duplic...tingDuplicates

Several options down the page

--
Kind regards,

Niek Otten

"method373" wrote
in message ...

Hey guys, I have another question, nerve-wracking for me, probably cake
for you.

I have two similiar spreadsheets with about 6,000 rows of data and a
bunch of columns. They're close, but not exact. Is there a way to match
the two up? Spreadsheet 1 and 2 look like this:


100 100
200 200
300 400
400 500
500 600
600 700
700
800


The common denominator would be the first row, let's say A1-A6000, with
a unique product code.

Basically, we have one master spreadsheet that was originally uploaded
to our SQL server, and then items were deleted manually on our web
interface because they were out of stock. I want to delete the items
that are in the master spreadsheet that don't exist in spreadsheet #2 -
so basically if an item isn't on spreadsheet 2, it deletes the entire
row in spreadsheet 1 so that at the end, there's an even number of rows
in each spreadsheet and they line up. Also, the columns don't match up
exactly in both spreadsheets - only the product codes are the same. Not
sure if that matters, really, but I figured I'd let you know.

Make any sense?

Just wanted to say thank you, you guys have been such a big help. Wish
I could give something back.


--
method373
------------------------------------------------------------------------
method373's Profile:
http://www.excelforum.com/member.php...o&userid=29888
View this thread: http://www.excelforum.com/showthread...hreadid=513934