Urgent please solve this Excel problem

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 am trying to print name , first date , last date for each group eg

a 20160101

a 20160107

aa 20160108

aa 20160118

ab 20160119

ab 20160128

Answer 1

Assuming your data is in column A and column B, you would want to grab the first row then loop through your data until the values in column A and column B for the current row do not match the values in column A and column B for Row+1. You would then grab the data in the current row. That gives you the first and last for the set. You would write the loop to repeat this process until you hit a blank meaning you are at the end of your dataset. This can be done with While statements along with IF AND statements. If it must be done in Excel let me know. If the JAVA works for you then Patrick has you covered.

Thanks and let me know, I am here if you need me.

Answer 2

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]);

}

}