Repeat lines get highlighted - how?

[stawned]

Hi

Here is my situation:
I use Excel 2010. I have a spreadsheet which has about 5 columns - Name, Street Address, City, State, Zip, etc ...
The spreadsheet contains about 2500 rows of information. I have sorted the spreadsheet so that it sorts by Street Address.

Now what i would like to do is to get excel to HIGHLIGHT the rows with the same street address, so that its easier on my eyes, for editing certain information about families. For eg. first 5 rows belong to a family, and hence the street adress is the same, and hence i would like it highlighted ... then the next few entries are individual people, with different addresses; Then row 13 to 16 are a family and so they all have the same address - again would like this highlighted. (colour doesnt matter)... and so on, for the rest of the 2500 rows.

How can this be done? I am a newbie at excel, and found you guys through google. I would appreciate any help - thanks!

[TomOfCT]

Hi. This gets a bit complex, but is possible.

The function you want to use is called Conditional Formatting. Hitting Alt-O then D will pull up the menu. You want to create a New Rule, then "Use a Formula to Determine Which Cells to Format."

You could make the rule simply highlight the row if the address equals the row before it or after it. However, you could have two blocks of same addresses sequentially (2 people on 1 main street, then 2 other people on 10 main street), so you might want each of those in separate colors.

I made a simple spreadsheet that does what I think you want. I will try to upload it here in a minute.

[TomOfCT]

AddrHighlights.xlsx

This should do what you want. It is fairly complex to explain fully. Pls ask any questions back if/when you get stumped! Cheers, Tom

[stawned]

Hi TomOfCT,

Thanks for your reply. What you have done looks great ... i just dont know how i am supposed to implement it on to my excel sheet. The reason i am getting the same addresses highlighted is beacuse i want to send a letter to everyone (mail merge), but to families at the same address i'd rather send just 1 letter eg. "Dear John Smith and family".

So the highlighted sections will make it easier for me to go through the entire excel sheet, and delete dupilcate address entries (i dont want it auto delete formula, becuase there are some i dont want to delete, and there are exceptions). But atleast i dont have to strain my eyes over 2000 lines. Also, having different colours is very good, but not much of a priority - coz i am going to delete anyway!

[TomOfCT]

Hi TomOfCT,

Thanks for your reply. What you have done looks great ... i just dont know how i am supposed to implement it on to my excel sheet. The reason i am getting the same addresses highlighted is beacuse i want to send a letter to everyone (mail merge), but to families at the same address i'd rather send just 1 letter eg. "Dear John Smith and family".

So the highlighted sections will make it easier for me to go through the entire excel sheet, and delete dupilcate address entries (i dont want it auto delete formula, becuase there are some i dont want to delete, and there are exceptions). But atleast i dont have to strain my eyes over 2000 lines. Also, having different colours is very good, but not much of a priority - coz i am going to delete anyway!

To implement this, add two columns in the right on your spreadsheet...add the concatenation column, and the number column. Follow the formulas that I set up.

Then, add Conditional formatting. Follow the directions in my post above... "The function you want to use is called Conditional Formatting. Hitting Alt-O then D will pull up the menu. You want to create a New Rule, then "Use a Formula to Determine Which Cells to Format."

You can hit Alt+O D on my spreadsheet and then you will see the rules I created. You would need to create these rules in your spreadsheet.

Or, alternatively, you could simply paste your data in my spreadsheet...insert extra columns as needed...

[stawned]

Thanks for your reply Tom, i am going to try this out and reply how it went ....
One more dumb question ... why doesnt excel allow 2 spreadsheet files to be viewed side by side? I it just me? I've got a 3 monitor setup, and its not letting me do this .... i havent noticed this, but now that i am starting to use excel a bit, this is a very annoying feature. I tried the "View" "Window" "View Side by Side" option ... but that just opens the sheet on the same screen split into half ... not what i want.

I know this is off topic, but would you know what to do?

Cheers

[TomOfCT]

One more dumb question ... why doesnt excel allow 2 spreadsheet files to be viewed side by side? I it just me? I've got a 3 monitor setup, and its not letting me do this .... i havent noticed this, but now that i am starting to use excel a bit, this is a very annoying feature. I tried the "View" "Window" "View Side by Side" option ... but that just opens the sheet on the same screen split into half ... not what i want.

I know this is off topic, but would you know what to do?

Cheers

Excel is a MDI, or multiple-document interface. To see multiple spreadsheet files within the big excel window, you need to click the button next to the X in the top right corner of the spreadsheet. Not the top right of the full excel file, but just below that, there will be a button...see the image attached below. The help tip will be "Restore" window. That will let you see multiple excel files at once.


ExcelCorner.JPG

[TomOfCT]

Heading to bed now. I'll look tomorrow if there are more questions. Cheers.

[stawned]

Excel is a MDI, or multiple-document interface. To see multiple spreadsheet files within the big excel window, you need to click the button next to the X in the top right corner of the spreadsheet. Not the top right of the full excel file, but just below that, there will be a button...see the image attached below. The help tip will be "Restore" window. That will let you see multiple excel files at once.


Attachment 166783

Thanks for your replies TomOfCT - however, thats not what i meant by multiple windows. That opens excel sheet in ONE excell platform. i meant to say multiple excel platforms - like opening multiple Internet Explorers, for different websites, side by side, instead of opening TABS in one Internet Explorer ... each on differnt monitors (i have 3 monitors) ... u get what i am trying to explain? lol ..

As for your formulas - everything is working out well so far, im in the middle of editing the whole document.

Thanks a lot for the formulas .. u made everything so easier.

Cheers!

[martindwilson]

open excel again it creates 2 seperate instances of excel,repeat for a third, but you will not have all the copy paste functionality you have within excel

[TomOfCT]

Thanks for your replies TomOfCT - however, thats not what i meant by multiple windows. That opens excel sheet in ONE excell platform. i meant to say multiple excel platforms - like opening multiple Internet Explorers, for different websites, side by side, instead of opening TABS in one Internet Explorer ... each on differnt monitors (i have 3 monitors) ... u get what i am trying to explain? lol ..

As for your formulas - everything is working out well so far, im in the middle of editing the whole document.

Thanks a lot for the formulas .. u made everything so easier.

Cheers!

Glad to help.

You'll get slightly more functionality having all of the workbooks open within one instance of Excel. I'd maximize one instance of Excel so it covers all three of your screens, then open each workbook just to fit on one screen.

[martindwilson]

that can create problems ,sometimes if you say save it in the extreme right hand screen for example someone with only one monitor may not see it when they try and open it in excel

[TomOfCT]

that can create problems ,sometimes if you say save it in the extreme right hand screen for example someone with only one monitor may not see it when they try and open it in excel

Fair point. Those "off the screen" workbooks are frustrating for the uninitiated.

The "View | Arrange All" function solves this problem for me. I have multiple monitors at work...but some colleagues have even more monitors than me (for reasons I don't fully understand)...and I have to go hunting for the workbooks that set sent to me!

[stawned]

Thanks for all your help guys. Tom, the formulas were a success - it helped me filter out the repeat address, and i also re-applied the formula to help me filter out the repeat last names, just in case there were spelling errors in the addresses ... lol

As for multiple excel sheets, i figured out a way how to with the help of some suggestions on google. Here's how its done - Open up the first workbook as usual. Then go to Start (Windows 7), type EXCEL in the search field and open up a new blank work sheet. This blank work book can now be moved using your mouse to a new screen - and then you can drag/drop the 2nd saved workbook in it and there you go. Simple, but still had to figure it out. lol

Cheers fellas.

[stawned]

open excel again it creates 2 seperate instances of excel,repeat for a third, but you will not have all the copy paste functionality you have within excel

lol, just figured out that what i said was exactly what u meant - mind my newbie-ness. lol

simple copy/paste works for me ... all i need. Thanks