Large spreadsheet with conditional formatting

W

Worthe

New Member
Joined
Sep 12, 2008
Messages
7
windows xp
excel 2002 service pack 2
Problem:
I have multiple large spreadsheets of questionnaire data. Responses are as %'s
What I would like to do is use conditional formatting to pick out the top 3 of each set of questions per column not including 0% (if it falls in the top 3). Using different shades of green in the cell background and a white font.
so.....
The spreadsheets are up to 3000 rows long and up to column BB wide. The sets of questions are of random length spaced by an empty row. So question 1 may have 11 parts to it, each taking up a row so 12 rows in total. Question 2 may have 5 parts taking up row each so 6 rows in total...............Question 80 may have 7 parts so 8 rows in total and on and on.
What I have been doing is working down the first column of responses and manually adding the conditional formatting. Then using the format painter to copy across the relevant rows and columns. For example if Question one was from c5:bb26 I would format c$5:c$26 with the top three then use format painter to drag across to bb5:bb26.
Conditional Formatting formula used: =D7=LARGE(D$4:D$15,1)
=D7=LARGE(D$4:D$15,2)
=D7=LARGE(D$4:D$15,3)
Conditional Formatting on multiple large spreadsheets of data

The problem is I have to do this for every set of questions and the questions per worksheet are different lengths. So once I've completed the first I can't just copy across the formats to the other worksheets. This is very time consuming and a little soul destroying!
I do not know if I am asking for a lot of development or if I am in fact doing it the quickest way (I hope not!). But can anyone help speed up this task by using either VBA or a macro. I have limited experience of both and have so far only managed to create a macro where you need to change all the cell references each time. Which turns out to be not much quicker than the above method! My new! VBA skills are based around cannibalising existing code and macros so no where near good enough yet.
Thank you in advance for your advice and help.
Regards
Worthe
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Which column contains the Question number and is it repeated in each row? Assuming it's repeated in column C, you can use an array formula like:

=D4=LARGE(IF($C$4:$C$18=C4,$D$4:$D$18),1)

Change the range references to suit.
 
Upvote 0
Hi Andrew.

Thank you for your prompt reply but I was unable to figure out how to use the array and or make it work. Apologies if I seem a bit slow.

I tried to use the Html maker tool but didn't have much success so this is some html of the page layout. If you copy and paste into a text editor (notepad for windows) and save to your desktop with .html as the file format (Table.html for example) Then double click on the browser icon it should be displayed in your browser. (Sorry if this is all stuff you probably know but wanted to be sure)

Although it doesn't as far as I know open in excel should give you an idea of what the spreadsheet looks like.

Each column is grouped by question (called Set in the example) I would like to add formatting to show top 3 as long as it does not equal 0% for each set of questions. Each column is a unique month so the top 3 is by column and it cut off is the next question (set)
HTML: 
<!----------copy and paste tags below--------------->
</html>
<head>
</head>
<body>
<table border="1">
<tr>
  <td>&nbsp</td>
  <td>&nbsp</td>
  <td>results a</td>
  <td>results b</td>
  <td>results c</td>
  <td>results d</td>
</tr>
<tr>
  <td>Set 1</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
</tr>
<tr>
  <td>&nbsp</td>
  <td>How many cats expressed to pref</td>
  <td>40%</td>
  <td>24%</td>
  <td>99%</td>
  <td>0%</td>
</tr>
<tr>
  <td>&nbsp</td>
  <td>How many dogs</td>
  <td>43%</td>
  <td>52%</td>
  <td>58%</td>
  <td>45%</td>
</tr>
<tr>
  <td>Set 2</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
</tr>
<tr>
  <td>&nbsp</td>
  <td>How many cats don't like swimming</td>
  <td>0%</td>
  <td>21%</td>
  <td>3%</td>
  <td>0%</td>
</tr>
<tr>
  <td>&nbsp</td>
  <td>How many dogs don't like swimming</td>
  <td>1%</td>
  <td>3%</td>
  <td>5%</td>
  <td>0%</td>
</tr>
<tr>
  <td>&nbsp</td>
  <td>How many fish don't like swimming</td>
  <td>0%</td>
  <td>0%</td>
  <td>1%</td>
  <td>0%</td>
</tr>
<tr>
  <td>Set 35</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
</tr>
<tr>
  <td>&nbsp</td>
  <td>How many xxxxx xx xx</td>
  <td>15%</td>
  <td>48%</td>
  <td>13%</td>
  <td>0%</td>
</tr>
<tr>
  <td>&nbsp</td>
  <td>does this always xxxxx xx xx</td>
  <td>1%</td>
  <td>3%</td>
  <td>5%</td>
  <td>0%</td>
</tr>
<tr>
  <td>&nbsp</td>
  <td>what do you see as the best xxxxx xx xx</td>
  <td>33%</td>
  <td>3%</td>
  <td>87%</td>
  <td>6%</td>
</tr>
<tr>
  <td>&nbsp</td>
  <td>can this be done xxxxx xx xx</td>
  <td>24%</td>
  <td>3%</td>
  <td>13%</td>
  <td>12%</td>
</tr>
<tr>
  <td>&nbsp</td>
  <td>what value does this xxxxx xx xx</td>
  <td>8%</td>
  <td>0%</td>
  <td>0%</td>
  <td>19%</td>
</tr>
</table>

</body>
</html>
 
Last edited:
Upvote 0
Hi Andrew.

Thank you for your prompt reply but I was unable to figure out how to use the array and or make it work. Apologies if I seem a bit slow.

I tried to use the Html maker tool but didn't have much success so this is some html of the page layout. If you copy and paste into a text editor (notepad for windows) and save to your desktop with .html as the file format (Table.html for example) Then double click on the browser icon it should be displayed in your browser. (Sorry if this is all stuff you probably know but wanted to be sure)

Although it doesn't as far as I know open in excel should give you an idea of what the spreadsheet looks like.

Each column is grouped by question (called Set in the example) I would like to add formatting to show top 3 as long as it does not equal 0% for each set of questions. Each column is a unique month so the top 3 is by column and it cut off is the next question (set)
HTML: 
<!----------copy and paste tags below--------------->
</html>
<head>
</head>
<body>
<table border="1">
<tr>
  <td>&nbsp</td>
  <td>&nbsp</td>
  <td>results a</td>
  <td>results b</td>
  <td>results c</td>
  <td>results d</td>
</tr>
<tr>
  <td>Set 1</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
</tr>
<tr>
  <td>&nbsp</td>
  <td>How many cats expressed to pref</td>
  <td>40%</td>
  <td>24%</td>
  <td>99%</td>
  <td>0%</td>
</tr>
<tr>
  <td>&nbsp</td>
  <td>How many dogs</td>
  <td>43%</td>
  <td>52%</td>
  <td>58%</td>
  <td>45%</td>
</tr>
<tr>
  <td>Set 2</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
</tr>
<tr>
  <td>&nbsp</td>
  <td>How many cats don't like swimming</td>
  <td>0%</td>
  <td>21%</td>
  <td>3%</td>
  <td>0%</td>
</tr>
<tr>
  <td>&nbsp</td>
  <td>How many dogs don't like swimming</td>
  <td>1%</td>
  <td>3%</td>
  <td>5%</td>
  <td>0%</td>
</tr>
<tr>
  <td>&nbsp</td>
  <td>How many fish don't like swimming</td>
  <td>0%</td>
  <td>0%</td>
  <td>1%</td>
  <td>0%</td>
</tr>
<tr>
  <td>Set 35</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
  <td>&nbsp</td>
</tr>
<tr>
  <td>&nbsp</td>
  <td>How many xxxxx xx xx</td>
  <td>15%</td>
  <td>48%</td>
  <td>13%</td>
  <td>0%</td>
</tr>
<tr>
  <td>&nbsp</td>
  <td>does this always xxxxx xx xx</td>
  <td>1%</td>
  <td>3%</td>
  <td>5%</td>
  <td>0%</td>
</tr>
<tr>
  <td>&nbsp</td>
  <td>what do you see as the best xxxxx xx xx</td>
  <td>33%</td>
  <td>3%</td>
  <td>87%</td>
  <td>6%</td>
</tr>
<tr>
  <td>&nbsp</td>
  <td>can this be done xxxxx xx xx</td>
  <td>24%</td>
  <td>3%</td>
  <td>13%</td>
  <td>12%</td>
</tr>
<tr>
  <td>&nbsp</td>
  <td>what value does this xxxxx xx xx</td>
  <td>8%</td>
  <td>0%</td>
  <td>0%</td>
  <td>19%</td>
</tr>
</table>

</body>
</html>
 
Last edited:
Upvote 0
If the data you posted is in the range A1:F14, which cells would you expect to be highlighted for each Set?
 
Upvote 0
I would like the top 3's for these from the example:-

c3:c4 (top 3) d3:d4 (top 3) e3:e4 (top 3) f3:f4 (top 3)

c6:c8(top 3), d6:d8(top 3), e6:e8(top 3), f6:f8(top 3)

c10:c14(top 3), d10:d14(top 3), e10:e14(top 3), f10:f14(top 3)

If possible
 
Upvote 0
Yes my mistake, this was just to give a feel for the format and I was rushing a bit. All sets of questions will have at least 3 rows in the real worksheets.
For example one of the real worksheets is from a1:aq725 consisiting of 88 seperate sets of questions ranging from 4 rows to 16 rows a piece.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Conditional Formatting - How to Properly Copy + Paste without Duplicating Rules
Replies
1
Views
1K
6StringJazzer
6StringJazzer
V
  • Solved
Copy Conditional Formattting (Greater than)
Replies
5
Views
496
Joe4
Joe4
R
  • Question
Excel spreadsheet- Date issue
Replies
1
Views
209
Kerryx
K
T
  • Question
Dragging Down Conditional Formatting
Replies
1
Views
707
kevin9999
K
G
  • Solved
Skins Game Conditional Formatting
2
Replies
16
Views
940
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,938
Messages
6,122,346
Members
449,080
Latest member
Armadillos

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top