Excel 2010 Macro Needed for Search, Compare & Change

[reposting with clearer title]

Is there a way to create a macro that will do the following (I'm stating it as a person would do it):

- look down column A and find any two rows that match in column A only (if other columns differ across the rows that's okay)
Say it finds that row 10 and row 11 in column A match, then
- look across these two matching rows to column J and determine if row 10/column J differs from row 11/column J
- and if yes, they do differ, change row 10/column J to the contents of row 11/column J
- lastly, after making that change, continue on down column A to find additional changes needed until the whole spreadsheet is checked

If this is possible, please advise and maybe even provide the instructions or formula to use?

Thanks,
Cindi

If sort is important, use a blank column and range fill with numbers. Then upon completion, you've got the info you need to restore the sort.

As I understand what you're asking, I've done that type of thing before. I added a column to the sheet that I could empty and then use as a marker that I had already processed the row - marked with an "ok" if it is either the first or subsequent "copies" of the record. The cells all start out blank and are marked as evaluated. Then I do an activeCell scan (with isblank(activeCell.offset(0,10) (for instance - whatever your "ok" column is) type of comparisons to ensure that the record has not already been processed. Start the scan at range("a2").select (for example) and after each comparison, use the "activeCell.offset(1,0).select" to go to the next row. Continue the scan until you get to "if isblank(activeCell)" then quit the processing.

For each record that you process, use a .find routine to determine if there are any more rows with that data. If there is, you can adjust cell data in either record as desired by using either activeCell.offset(0,x) and r.offset(0,x) (where R is the range variable used by the .find routine). It sounds complicated, I know, but just step through the code until you've gone through a few comparisons and adjustments and then let it go.

Hope this helps.

Actually, my column A is numbers so sorting is fine (as long as all the other row information stays with it). That is what I do now.

If it helps, let me explain my use/reason: I own a small used books store. I export my inventory from my main database into Excel in order to upload it onto my website. Column A is the book's unique Item #; column J is the book's quantity that I have in stock. Periodically, as books are sold in my store and/or new copies come into the store, I need to update the master accordingly--only the quantity (column J mentioned above); the rest of the information across the row remains the same. So here's what I currently do: I maintain one master Excel sheet. I export from the database into Excel and highlight the whole list. Then I copy-n-paste it at the bottom of the master list, and then sort by column A. Thus I end up with one big sheet, every other line is a duplicate of the one above with one white and the duplicate a color. I need to change only the white line in column J. [As I add inventory obviously there will be rows at the bottom which are new and thus don't have a duplicate/don't need changing.] I manually do the above steps to make the changes. When I'm done with all the changes and my original master rows are now all updated, I then re-sort by color and delete the duplicate colored ones. Quite a convoluted process to do manually, and as my inventory grows it's getting phenomenally time consuming as well.

I hope that makes sense, and thanks for helping me.


(PS: In a perfect world I would have an inventory database that would automatically export only the listings which have changed, up or down, so I would know immediately which needed changing at all. But, frustratingly, I do not have such a thing. I aspire to that when I grow up and the budget allows.)

Not working, I get a "Run-Time error '1001'... The sort reference is not valid"

Does it matter that I named the macro UpdateQuantity? I changed it in your formula.

I would attach a sample for you to play with but I can't figure out how to do that. Do you want me to email it to you?

Not working, I get a "Run-Time error '1001'... The sort reference is not valid"

Does it matter that I named the macro UpdateQuantity? I changed it in your formula.

It doesn't matter about the color, does it? I have two colors right now, purple and blue (means different things to me, unrelated to this) -- but of course I could make them all one color for running the macro.

I would attach a sample for you to play with but I can't figure out how to do that. Do you want me to email it to you?

No, now I get a run-time error 5 "invalid procedure call or argument"

I think it's now attached.

Nope. Maybe I am doing something wrong on my end but I don't know what. Here are the steps I took:

I copied your code from here, and went back to my spreadsheet
Clicked on Macros
Type the macro name CompareAndChange
Click Create
in the next window it shows
Sub CompareAndChange ()

End Sub
with the cursor between the two lines
I pasted your code where the cursor is, and Save (macro enabled workbook)
Now I'm back in my spreadsheet
I click Macros -- I see two entries (one selected) that both say macrotest.xlsm!Module1.CompareAndChange
I click Run
Get the error message "Compile error Ambiguous name detected: CompareAndChange"
and in the box behind the error is the code
first line says Sub CompareAndChange ()
next Option Explicit
space
next Sub CompareAndChange () <---and this line is highlighted

No, still not working. Sigh. After all this time I guess I could've done it manually, LOL.
I give up, I don't know what else to try.

Thank you, nonetheless. I appreciate your trying to help me with this.

Oh, there's an idea. I'll try that. I probably won't get to work on this anymore until Monday. I'll get back to you then.

Have a good weekend.

Hi,

there is error coming out in this below mail

Run time errror '5' :
Invalid Procedure call or argument

.Header = xlNo

Okay, I'm finally back to this. No matter what advice from all of you I take, the macro will not work. It won't even work on Pichingualas' own spreadsheet. Is it maybe my version of Excel (I'm using 2010)? is there perhaps some setting within Excel that is not set properly? I do have macros enabled. Is it Windows Vista? I'm grabbing at straws here. It is very strange that the macro works for you but will not for me.