Automated comparison between two columns

**Laura C** · 06-22-2010, 10:03 AM

Hello all,

I'm very new to using macros in Excel and as wondering if anyone might be able to give some advice.

Basically, it's a matter of security - I have two spreadsheets, each of which contains a list of usernames and a list of accesses to systems associated with that username. I need to compare the accesses for each user and come up with a list of differences between the spreadsheets. For example, the first spreadsheet says Suzy has access to A, B, and C. The second says she has access to B, C, and D. That needs to be picked up and documented.

Part of the issue is also that the spreadsheets are formatted slightly differently. I'm not sure if that matters or not...

Any help would be greatly appreciated.

Thanks!
Laura

**Laura C** · 06-22-2010, 01:38 PM

Ok, so I actually found a snippet of code that does comparisons and points out the differences (here's the link: http://www.exceltip.com/show_tip/Cel...Excel/477.html)

My question now is how can I apply it to my spreadsheet? Any suggestions? I can post an example of the spreadsheet if that would be helpful to look at..

**shg** · 06-22-2010, 01:49 PM

Welcome to the forum, Laura.

People are going to be hard pressed to help wihout seeing the layout. Post a workbook with sanitized data and an example of desired results.

**Laura C** · 06-22-2010, 01:58 PM

Thanks, shg.

Does this help?Example.xls

**shg** · 06-22-2010, 02:24 PM

In the attachment, I inserted the two columns labeled Formula, and added and copied down the formulas shown. Then I selected the two cells labeled Formula and entered "keys" (sans quote) in the Names box, left of the formula bar, then pressed the button to run the macro AlignKeys, which is installed in the workbook. The result is what you see: like items are aligned.

You can paste your data from your other workbooks, copy the formulas in cols A and D down to the end of each dataset, and push the button.