automatically bold a whole row of data

[profitdr123]

I have a very large spreadsheet with over 435,000 rows of data. How do I program excel 2007 to automatically bold a row if two data fields match?

[FDibbins]

Hi and welcome to the forum

You could probably do this with Conditional Formatting, but with such a large amount of data, that may slow your file down

2 other options would be

use a formula in a blank column to do that text for you, and then use filters to show the duplicates
(this might be a better option, rather than trying to scan through 400 000+ rows of data to find the bolded rows)

Use VBA to do the bolding for you. This should not slow things down (after the 1st run i think), but it may still be a mission to find what you need with that many rows

[profitdr123]

I have no clue what u just said. Please explain the necessary steps.

[alansidman]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[FDibbins]

Conditional Formatting is a feature in excel that lets you change cell formatting depending on specified criteria. You find it on the Home tab, under Styles. If you have not used it before, create a new dummy workbook and play around with it for a while, it can be very useful

the down-side of CF is that it can become very resource-intensive with large volumes of data, which can slow things down.

My other suiggestion was to use a helper column and create a foemula in there that will test for teh criteria you want (duplicates?) and then show a code. You would then apply filters - again in the Home tab under Editing. This will provide you with a derop-down from which you can select certain data to be shown/hidden. Again, test/play with it on a dummy workbook to see what it does

[profitdr123]

Here is the file. If the data in In column F is the same as in Column L then I want the whole row to be bolded. The first tab is the raw data and the second tab is what I want to program to do.

[FDibbins]

Thanks for the file, it makes things much easier.

1st, even though some of those adresses look the same, they are not. The data in L seems to have over 100 trailing spaces. So, for instance...
"1651 SAGE CREEK CT" is not the same as "1651 SAGE CREEK "

Having said that....
Option 1, using Contitional Formatting
Try this and see how much it slows your file down. Save your file as a copy and then test onthat, so you dont mess up your original file.

1. highlight the range you want to apply the conditional formatting to A2:AG435 000
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =TRIM($F2)=TRIM($L2) Format Font BOLD

Option 2 using filters (Do this 1 instead of Option 1 if things slow down too much for you)
With the amount of data you say you have, even if you bold (or color-code) rows of data, you will still be left with an impossible amount of data to manually scan through. This is the option I would use, if this was something I had to do.
1. in a helper column (I used AH - you can use a different column if you want) Enyter this into AH2 and copy down...
=TRIM($F2)=TRIM($L2) (we could do something fancy, but no need, I dont think)
2. On the Home tab, under Editing, select Sort and Filter/Filter
You will see that each heading now has a small triangle in a box in it. If you click on any of them (in any of the columns), you will have a window with all unique entires inthat column, with a check mark next to them. You can UNcheck "select all" a,d then check individual items for a list of just those items.
So, click the triangle/pull-down in AH, UNselect FALSE, Click OK and you will be left with a list of rows where F=L. You can now do with them what you will. If you highlight and copy them, then paste them to a new sheet, all you will get is the matching rows

[profitdr123]

Thanks for the info. I will try it out. Yes this is 1 of many steps. I got a programmer to do a more complicated process: finding 5 or more properties owned by same owners address, delete all others and put 2 spaces between each group. He gave me a macro but I have no clue how to use it and the instructions he gave me were not usable. Would you be able to help?

[FDibbins]

Macros are not my strong side , but if is is already written, Im sure we can help you to get it implimented.

Try my suggestions above (focus on the 2nd option 1st), and let me know how you make out. I could probably adapt that formula to find multiple properties/1 owner.

Based on the sample provided, and assuming you would base this on "Owner name/s", there were no duplicate names in that list. I used this...
=COUNTIF($J$2:$J$34,J2)>1
in A1

[profitdr123]

I cleaned up the extra spaces on the two columns by finding 2 spaces and replacing them with none. but I have some data files that now have 1 space after the last part of the cell. How do I get rid of that extra space at the end? I am still working on your advice. But this one issue is stopping me.

[FDibbins]

To remove the extra spaces, try this...
=TRIM(L2)

[profitdr123]

Thanks all this worked. I got rid of the extra spaces and bolded the rows with F & L matching. Now I have the task of eliminating any properties where the owners address is less than 5. Here is the instructions the programmer gave me.
EXCEL PROGRAMMING CODE FOR ORANGE COUNTY 5 OR MORE PROPERTIES OWNED BY 1 PERSON/COMPANY.

- Open your document and Click on “DEVELOPER” tab above in Excel (if this tab is not showing, see link below to enable it).
- Then on the right, click on “Visual Basic”
- When in “Visual Basic”, on the right side, click on “VBAProjects” and right click and select "Import File" and select this file: http://wikisend.com/download/545488/UserForm1.frm and this file: http://wikisend.com/download/552982/Module1.bas
- Then click on the green play button in the tool bar to run the project.
- This will run the code of your macro to get the results.

http://msdn.microsoft.com/en-us/libr.../bb608625.aspx

Your code is ready, follow the same instructions above. It's moving slowly through the whole 431489 records, because it's checking the whole 431489 rows for each address, meaning 431489x431489.

As for other CD's, what you would need to change is the following in the code:
- J2:J431489 to the new address column
- J3:J431489 to the new address column
- 431489 to the new number of rows
- the name of the sheet if different.

I got to the part where I imported the two codes into excel but that is where the instructions cant be found. I have excel 2007 and I told him that. Now what?

[FDibbins]

Now I have the task of eliminating any properties where the owners address is less than 5

Did you try my suggestion from post # 9?

Based on the sample provided, and assuming you would base this on "Owner name/s", there were no duplicate names in that list. I used this...
=COUNTIF($J$2:$J$34,J2)>1
in AI

All you need to do is modify the formula slightly...
=COUNTIF($J$2:$J$34,J2)<5
Copy that down in AI, remove the filter (the same way you applied it), then reapply - it will now include the new helper. Filter on TRUE and you will have those with less then 5 properties. You can now either just delete them, or move them to another sheet. If you move them (might be better to copy/paste) delete the blank rows, remove the filter and you should be left with what you want

[profitdr123]

I am using owners address not owners name. Many times the name may change but the address remains the same. Use column L. Look at new sample attached.

[profitdr123]

I tried your last suggestion and that works wonderfully without using a complicated macro . Once I entered in the formula I then sorted the data by the TRUE OR FALSE answer and then deleted the rows with TRUE. Then I resorted the data by owners address and then inserted two blank rows. Is there a formula to insert the two blank rows after the common addresses?

[FDibbins]

Sorry, not that I know of. It could be done with VBA, but then you are heading back down that path again.

As I explained above, you dont need to sort/delete/sort (you can if you want lol), just turn filters off, turn them back on and the new column will be included, you can then just filter on TRUE and then delete those rows

[profitdr123]

I think I did it right. I clicked on excellent rating and marked it as solved.

[FDibbins]

Im really happy we came up with something you can use It would be great if you could click on the small * at the bottom left of my post to add to my reputation as well