How to highlight first and last row of each group in excel?

Question

Below is an extract of my Excel data

A 20160101

A 20160104

A 20160105

A 20160106

A 20160107

AA 20160108

AA 20160111

AA 20160112

AA 20160113

AA 20160114

AA 20160115

AA 20160118

AB 20160119

AB 20160120

AB 20160121

AB 20160122

AB 20160125

AB 20160126

AB 20160127

AB 20160128

like I HAVE over 10,000+ rows

I want to highlight these rows

a 20160101

a 20160107

aa 20160108

aa 20160118

ab 20160119

ab 20160128

Please give me step by step solution

Answer 1

Hello, Chintu,

What type of highlighting would you like? Here are a couple of approaches:

1. Place your mouse cursor over the leftmost element of the first row, then left click and hold as you drag the cursor to the value column to select the entire row you want to highlight. Once selected, you can do one of the following:
2. Change the font color
3. Change the fill color, or
4. convert to BOLD type

Alternatively, you can make the same selection and then choose "Cell Styles" under the Conditional Formating Tab on the ribbon. You can chose from several colors and patterns.

A more advanced techique would be to use Conditional Formatting. Thias would require some work to set up a relationship that can identify the first and last rows, perhaps by setting a value in a new column that contains "1" for the first entry and "2" for the final entry in a set. The conditional formatting could be used to color the first row blue and the final row red, as an example.

I believe the first option in which you manually change the highligh color for a range of cells is the easiest.

Bob

Answer 2

Dr. Bob has given an answer that mentions the conditional formatting part of this question, but how do you know which rows to mark as 'first' or 'last'? Nobody has the patience to comb through 10,000+ rows to do that...

I'll call your A/AB values Key and assume they're in column A. Create a new column C called First Or Last, and in cell C2, enter the formula =IF(A2<>A1,1,IF(A2<>A3,2,0)) . This formula first checks if the key in row 2 is different from the one before it, then checks if it's different from the one after it. The first time a key appears, First Or Last will have the value 1, the last time a key appears, it will have the value 2, otherwise it will show 0. It's important to know that this will only work as expected if the table is sorted on your key values!

From here you have some options:

1. Copy the First Or Last column and paste as values to remove the formula, then sort the table by this column and highlight your rows manually now that they're in big blocks. When you're done highlighting you can sort the table however you'd like
2. Do conditional formatting, the details of which can depend on which version of excel you're using, so you'll want to look up a basic conditional formatting guide for your version of excel.

Answer 3

The first and the last.... the Alpha and the Omega!

Am honestly having a major roadblock staying within the bounds of MS Excel...

So instead I wrote you some code !!! It is written in Java. Probably not the Best solution

but it does work.

This little java app that will produce the report you want.

I can help you set it up and/or we can share the code and class file via google drive or the cloud...

For email, click on my picture image in my profile and you will see a link to email.

(1) First, copy and paste the source code into a plain text document (like from NOTEPAD) and

save it as AlphaAndOmega.java on the desktop.

(2) Open up MS-DOS prompt and change directory to the desktop....

If you want to compile the program, issue the command :> java AlphaAndOmega.java

The file AlphaAndOmega.class shall be created and appear in the desktop

(3) Save your spreadsheet as *.CSV file on the desktop

(4) Go back to the desktop and issue the command :> java AlphaAndOmega filename.csv

The report you want shall be written to file AlphaAndOmega.csv

you can move and rename it as you like.

========================================================================

import java.io.*;

class AlphaAndOmega

{

void Go(String filename)

{

System.out.println(" Examining file " + filename);

try

{

FileReader fileReader = new FileReader(filename);

BufferedReader bufferedReader = new BufferedReader(fileReader);

FileWriter fileWriter = new FileWriter("AlphaAndOmega.dat");

BufferedWriter bufferedWriter = new BufferedWriter(fileWriter);

String prevDate,prevStrName,inbuff;

String curDate,curName;

inbuff = bufferedReader.readLine();

String[] tokens = inbuff.split(",");

prevStrName = tokens[0];

prevDate = tokens[1];

bufferedWriter.write(inbuff);

bufferedWriter.newLine();

do

{

inbuff = bufferedReader.readLine();

if (inbuff!=null)

{

tokens = inbuff.split(",");

curName = tokens[0];

curDate = tokens[1];

if (curName.compareTo(prevStrName)!=0)

{

String outbuff = prevStrName + "," + prevDate;

bufferedWriter.write(outbuff);

bufferedWriter.newLine();

outbuff = curName + "," + curDate ;

bufferedWriter.write( outbuff);

bufferedWriter.newLine();

}

prevStrName = curName;

prevDate = curDate;

}

else

{

String outbuff = prevStrName + "," + prevDate;

bufferedWriter.write(outbuff);

bufferedWriter.newLine();

}

} while (inbuff != null);

bufferedReader.close();

bufferedWriter.close();

}

catch (IOException ex)

{

System.out.println(" error processing data file");

}

}

public static void main(String args[])

{

(new AlphaAndOmega()).Go(args[0]);

}

}