creating a macro that filters differences in columns

[chadder44]

Hi everyone. I'm very new to excel, but I had some help on this forum on how to make some filters for a speadsheet that I have. I attached the file that was created for me with the set rules, but I was wondering if there is a way that I can have these rules automatically applied to any spreadsheet I have formatted in this way. so I don't have to copy paste everytime) P.S. The columns may have up to 10,000 rows.....(Thanks.

[JBeaucaire]

Hi Chadder, I'll take a look at your sample file. Meanwhile, read through the Forum Rules so you can use and follow them effectively.

You'll need to edit that post above and GO ADVANCED to correct the title, it should specifically state the topic.

Perhaps: "Applying Formatting to multiple sheets"

[chadder44]

ok thanks, and sorry, i thought I was being specific.

[JBeaucaire]

On Sheet1, I can't imagine indexing a column with SUMPRODUCT() in that manner. By the time you get down to the 10,000th row, you're adding tens of thousands of calcs PER cell. Holy cow.

The sheet2 formulas are great.

Can you just describe again what you want happening between sheet1 and sheet2? Don't refer to the existing formulas, just the process you need.

[NBVC]

Sheet1, I can't imagine indexing a column with SUMPRODUCT() in that manner. By the time you get down to the 10,000th row, you're adding tens of thousands of calcs PER cell. Holy cow.

That is why I did suggest an alternative in your other post using the TRUE/FALSE and Countif combination of helper columns..

[JBeaucaire]

Perhaps this in D2, then copied down, accomplishes the same index values without all the overhead (as long as D1 is empty):

=IF(EXACT(A2,B2),D1,D1+1)

[NBVC]

yes, perhaps.

[chadder44]

I just want the rows that are not EXACTLY the same between column A and B, to be copied over to sheet 2 (with the ID#'s copied along as well).

[JBeaucaire]

The replacement formula I've suggested and your existing sheet2 seem to do a good job.

Adding a formula to a cell then double-clicking the lower-right corner to copy it all the way down a dataset is simple and quick. What issue are you trying to solve?

[chadder44]

i was wondering if there was a way that i could load a spread sheet that only has sheet 1 with column A, B, and C, and loading a macro that creates sheet 2 with the filtered rows. This is something that I am trying to make more user friendly because I am not the only one who is doing this. We are contantly creating these spreadsheets with the 3 columns, and we need an easy way to creat sheet 2 that filters the rows we need to look at. If the only way is to copy all the formulas each and every time, then that will have to do. Thanks for all your help.

[JBeaucaire]

I would think the simplest process is to have a two-sheet workbook already setup with column D filled out (and down a few 100 rows), and sheet2 filled out with the formulas already in a table going down a few 100 rows.

Then, just paste the new data each time from your source book into columns A.B.C and voila!