Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Combine information about products from 2 spreadsheets | Excel Worksheet Functions | |||
Spreadsheets are cut off when you paste and link? | Excel Worksheet Functions | |||
Excel Charts Linked to Spreadsheets | Charts and Charting in Excel | |||
Matching data in two spreadsheets | Excel Discussion (Misc queries) |