How to Write an If Statement or Vlookup Statement

[wec43wec]

I am trying to write a condition in excel that will select one row of data that contains at least 8 columns based on the "oldest date" for each account number within a list of different account numbers that could be repeated in the listing by up to 5 rows of the same account number but different dates.

This list contains over 50,000 rows but are separated or different by the account # and the date of activity or transaction.

I am trying to select the row of data for each account number that has the "oldest date" but I am not sure how to write this code/condition in excel (not VBA) using an if statement or a vlookup statement.

My data is as follows:


Title row (A1 through J1)

A1 B1 C1 D1 E_1
Area_ID District_ID District_Name Account_# "Date"

South District_S Name_A 111111 Mar 2
South District_S Name_A 111111 Apr 9
South District_S Name_A 111111 May 5

North District_N Name_N 222222 Jan 6
North District_N Name_N 222222 Feb 3

West District_W Name_W 555555 Feb 8
West District_W Name_W 999999 Apr 6
West District_W Name_W 111111 May 1

East District_E Name_E 888888 Nov 9

etc.

The desired results of the above data would be as follows

South District_S Name_S 111111 May 5
North District_N Name_N 222222 Feb 3
West District_W Name_W 111111 May 1
East District_E Name_E 888888 Nov 8

Note that only the row that contains the oldest date is selected for "each account number".

Note: I only listed the five major column headings. There are a few other headings that are not that important.

Thanks`for any help.





example





that is indicated in example column "x"

 

[SkipVought]

Hi,

Example???

Also is the DATE column TEXT or DATE? To check, select a cell and Format/Cells - Number Tab - GENERAL, and post back with the result.

Skip,
[sub]
[red][/red]
[/sub]

 

[GlennUK]

What do you mean by "oldest date"? In your examples it looks like you are wanting results for newest date, not oldest date?!?!?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[wec43wec]

GlennUK - you are correct. The dates should have been stated as the most "recent" date.

Skip - the date can be text or date because I am pulling this data from QMF / SQL database. I have the option to tag the data column as date or text before moving this database to excel.

Skip - you mentioned "example" I gave an example in my posting of the "raw" data and the desired results. Did you need additional information?

 

[MJPPaba]

Approach from a slightly different angle...

Sort the data in this order...

Date Sort Descending
Account Sort Ascending
District Name Sort Ascending
District ID Sort Ascending
Area ID Sort Ascending

then in cell F2 enter the following formula...
=IF(A2&B2&C2&D2<>A1&B1&C1&D1,E2,"")

copy this formula down to the bottom of the data in column F.

Go to [DATA]..[FILTER]..[AUTO FILTER]

The using the filter that appears in the header drop down the list in column F and select [NON-BLANKS]

the list that is displayed are the unique entries where the maximum date value is given.

Cheers

Matt

 

[PeterMoran]

Hi,

One Possible way is to create an extra column on the right as a sequence column. It has a formula in it which will be coded to show a 1 against the rows you want to list (with the most recent date). I have assumed that the blank rows are not present but just for illustration - an extra compication if they are present, just a longer formula.

Once you do this you can do a filter on that column to select all the 1's and you have what you want.

From your data I have the first data row from B4 to F4 and G4 as the Seq column with the following formula:

=IF(AND(B4=B5,E4=E5),G5+1,1)

This will put a 1 against those items you want to list and continue to do this thru the data. I did notice that WEST does not seem to be consistent with your details as each row has a different Account No.

On my spreadsheet I show a 1 against rows 3, 5, 6, 7, 8, 9.

Lets know if you need further assistance.

Good Luck!

Peter Moran

 

[SkipVought]

May 5 is not a date, unless it is a FORMATTED NUMBER.

"...the date can be text or date because I am pulling this data from QMF ..." A DATE cannot be TEXT. The TEXT that you posted is virtually useless in terms of a DATE for the purposes stated.

Yes, you posted an example, followed by several statements followed by some BLANK SPACE and the word...

example

followed by more BLANK SPACE, followed by

that is indicated in example column "x"






So, where is [red]...example column "x"[/red]



Skip,
[sub]
[red][/red]
[/sub]

 

[yogia]

Hi wec43wec:

Let us say your data looks like as in the

    A          B           C             D            E  
-----------------------------------------------------------
1 | Area_ID	District_ID District_Name Account_#	Date
2 |	          
3 | South	  District_S  Name_A	    111111	   2-Mar
4 | South	  District_S  Name_A	    111111	   9-Apr
5 | South	  District_S  Name_A	    111111	   5-May
6 |				
7 | North	  District_N  Name_N	    222222	   6-Jan
8 | North	  District_N  Name_N	    222222	   3-Feb
9 |				
10| West	   District_W  Name_W	    555555	   8-Feb
11| West	   District_W  Name_W	    999999	   6-Apr
12| West	   District_W  Name_W	    111111	   1-May
13|				
14| East	   District_E  Name_E	    888888	   9-Nov
15|

Then I used AdvancedFilter to to extract the records as you want them. The computed criterion formula in cell G1 is ...

=AND($A2<>"",COUNTIF($A2:$A$2,$A2)=COUNTIF($A$2:$A$14,$A2))

So with my List_range as $A$1:$E$14, my criterion range as $G$1:$G$2, and the Copy_to_range As $G$5:$K$14, the results are as illustrated.

    G          H              I                J            K
----------------------------------------------------------------------
1 |                            				
2 | TRUE				
3 | 
4 |	          				
5 | Area_ID	District_ID	District_Name	Account_#	Date
6 | South	  District_S	 Name_A	       111111	   5-May
7 | North	  District_N	 Name_N	       222222	   3-Feb
8 | West	   District_W	 Name_W	       111111	   1-May
9 | East	   District_E	 Name_E	       888888	   9-Nov

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com