Matching two spreadsheets

[method373]

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.

[flummi]

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

[Niek Otten]

Hi Hans,

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

Several options down the page

--
Kind regards,

Niek Otten

"method373" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>