VBA - search data based on Oldest & Newest Date

iris1007 · Jun 28, 2017

Hi,
Below is the data I have.
I need to find the Customer name base on same Pre-Group & same Part, copy and paste in column E "Result". With following criteria:

1. For those with same Pre-Group & same Part:
- the Customer with oldest DATE, Column E = blank
- the Customer with 2nd oldest DATE, Column E = Customer with oldest DATE
- the Customer with 3rd oldest DATE, Column E = Customer with 2nd oldest DATE

so on and so forth....

Thanks in advance for the helps and hope to hear the good news soon.

CUSTOMER	PART	DATE	Pre-GROUP	This should be the Result after the Code run
HONEY	40731	21/10/2014	00523
HONEY	G3266	23/5/2011	00523
HONEY	G3266	27/7/2011	00523	HONEY
SYSEM	G3266	16/1/2012	00523	HONEY
SYSEM	G3266	4/7/2013	00523	SYSEM
HONEY	G3266	16/12/2014	00523	SYSEM
WUHAN	G3269	22/7/2014	00523
HONEY	G3354	10/4/2008	00523
SYSEM	G3354	30/3/2010	00523	HONEY
SYSEM	G3355	21/7/2006	00523
HONEY	K2788	6/2/2017	00523
SHANGHAI	K2789	2/12/2016	00523
HONEY	26596	7/7/2007	AA0052
HONEY	26596	29/3/2010	AA0052	HONEY
WUHAN	26771	25/9/2012	AA0052
SYSEM	26771	13/2/2014	AA0052	WUHAN
SHANGHAI	38699	23/2/2017	AA0052
HONEY	90-25	12/5/2008	AA0052
HONEY	90-25	9/12/2008	AA0052	HONEY
SYSEM	90-25	26/2/2013	AA0052	HONEY
SYSEM	G3254	17/4/2013	AA0052
SYSEM	G3254	23/10/2013	AA0052	SYSEM
SYSEM	G3254	24/6/2014	AA0052	SYSEM
HONEY	G3254	20/8/2014	AA0052	SYSEM
HONEY	G3254	20/1/2015	AA0052	HONEY
HONEY	G3256	23/12/2011	AA0052
HONEY	G3257	23/12/2011	AA0052
SYSEM	G3257	15/5/2013	AA0052	HONEY
HONEY	G3257	21/10/2014	AA0052	SYSEM
HONEY	G3257	19/12/2014	AA0052	HONEY
WUHAN	G3261	18/3/2011	AA0052
SYSEM	G3261	6/6/2014	AA0052	WUHAN
SYSEM	G3262	19/6/2015	AA0052
HONEY	G3262	17/3/2016	AA0052	SYSEM

Akuini · Jun 29, 2017

iris1007 said:
Hi,
Below is the data I have.
I need to find the Customer name base on same Pre-Group & same Part, copy and paste in column E "Result". With following criteria:

Try this:

Code:

Sub a1011914a()

Dim va, vb
Dim rr As Long
rr = ActiveSheet.Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
va = Range(Cells(2, "A"), Cells(rr, "D"))
ReDim vb(1 To rr - 1, 1 To 1)
For j = 2 To UBound(va, 1)
        If va(j, 2) = va(j - 1, 2) And va(j, 4) = va(j - 1, 4) Then
            vb(j, 1) = va(j - 1, 1)
        End If
Next
Range("E2").Resize(UBound(vb, 1), 1) = vb

End Sub

iris1007 · Jun 29, 2017

Thank you so much Akuini. You're a legend.

Akuini · Jun 30, 2017

iris1007 said:
Thank you so much Akuini. You're a legend.

You're welcome & thanks for replying

iris1007 · Jul 2, 2017

Hi Akuini,
I have another huge file which have many Items in a same column (column B) and I would like to search at PART (column D) and assign "1" if Yes else "0"..according to Item No.
Can you help?
Thanks in advance for the helps and hope to hear from you soon.

Item No	Details		PART	Description	A	B	C	D	E	F	G	H	I
A	2041-04 MISSING 2065-01 CRACK 73-01 Configuration 84-01 Configuration 83-03 Modification 311947A00 Modification	2041-04	MISSING	1	0	0	0	0	0	0	1	0
B	18-01 MISSING 312169A00 Configuration 312130A00 Configuration 312155A00 CRACK 312161A00 Configuration 73-01 Configuration 74-01 Modification 18-01 MISSING 12A00 Configuration 26A00 MISSING	2044-01	CHANGE	0	0	0	0	0	0	0	0	0
C	2065-01 CRACK 73-01 Configuration 78-01 Configuration 83-03 Modification 51-02 CHANGE 311364A00 Modification 311777A00 CRACK 312090A00 Modification 311599A00 Configuration 311241A00 CHANGE 2055-01 Configuration 18-01 MISSING	2054-01	CRACK	0	0	0	0	1	1	0	0	0
D	312127A00 CRACK 312105B00 CRACK 312161A00 Configuration 311595A00 Configuration 311667A00 Modification 311471A00 MISSING 311881A00 Modification 12A00 Configuration 51-02 CHANGE 311244A00 Modification 311262A00 Modification 311312A00 Modification 2065-01 CRACK 73-01 Configuration 78-01 Configuration	2055-01	Configuration	0	0	1	0	0	0	0	0	0
E	82-02 MISSING 83-03 Modification 77-01 CRACK 70-03 Configuration 2063-16 Modification 2054-01 CRACK 26A00 MISSING 14A00 CHANGE 311471A00 MISSING 311314A00 MISSING 311262A00 Modification 311244A00 Modification 311241A00 CHANGE 74-01 Modification 15A00 Modification	2063-16	Modification	0	0	0	0	1	0	1	0	0
F	312090A00 Modification 311947A00 Modification 311667A00 Modification 312105B00 CRACK 312169A00 Configuration 2054-01 CRACK 77-01 CRACK	2064-01	Configuration	0	0	0	0	0	0	0	0	0
G	83-03 Modification 82-02 MISSING 77-01 CRACK 2063-16 Modification 26A00 MISSING 311491A00 Configuration	2065-01	CRACK	1	0	1	1	0	0	0	0	0
H	12A00 Configuration 76-01 Configuration 311241A00 CHANGE 311262A00 Modification 311313A00 Modification 311312A00 Modification 2041-04 MISSING	70-03	Configuration	0	0	0	0	1	0	0	0	0
I	73-01 Configuration 312091A00 Modification	73-01	Configuration	1	1	1	1	0	0	0	0	1
			74-01	Modification	0	1	0	0	1	0	0	0	0
			75-01	MISSING	0	0	0	0	0	0	0	0	0
			76-01	Configuration	0	0	0	0	0	0	0	1	0
			77-01	CRACK	0	0	0	0	1	1	1	0	0
			78-01	Configuration	0	0	1	1	0	0	0	0	0
			79-01	Modification	0	0	0	0	0	0	0	0	0
			81-01	Configuration	0	0	0	0	0	0	0	0	0
			82-02	MISSING	0	0	0	0	1	0	1	0	0
			83-03	Modification	1	0	1	0	1	0	1	0	0
			84-01	Configuration	1	0	0	0	0	0	0	0	0
			18-01	MISSING	0	1	1	0	0	0	0	0	0
			19-01	CHANGE	0	0	0	0	0	0	0	0	0
			51-02	CHANGE	0	0	1	1	0	0	0	0	0
			26A00	MISSING	0	1	0	0	1	0	1	0	0
			12A00	Configuration	0	1	0	1	0	0	0	1	0
			14A00	CHANGE	0	0	0	0	1	0	0	0	0
			15A00	Modification	0	0	0	0	1	0	0	0	0
			311142A00	Modification	0	0	0	0	0	0	0	0	0
			311241A00	CHANGE	0	0	1	0	1	0	0	1	0
			311244A00	Modification	0	0	0	1	1	0	0	0	0
			311262A00	Modification	0	0	0	1	1	0	0	1	0
			311312A00	Modification	0	0	0	1	0	0	0	1	0
			311313A00	Modification	0	0	0	0	0	0	0	1	0
			311314A00	MISSING	0	0	0	0	1	0	0	0	0
			311359A00	Modification	0	0	0	0	0	0	0	0	0
			311364A00	Modification	0	0	1	0	0	0	0	0	0
			311460A00	CHANGE	0	0	0	0	0	0	0	0	0
			311471A00	MISSING	0	0	0	1	1	0	0	0	0
			311491A00	Configuration	0	0	0	0	0	0	1	0	0
			311595A00	Configuration	0	0	0	1	0	0	0	0	0
			311599A00	Configuration	0	0	1	0	0	0	0	0	0
			311667A00	Modification	0	0	0	1	0	1	0	0	0
			311676A00	Modification	0	0	0	0	0	0	0	0	0
			311721A00	Modification	0	0	0	0	0	0	0	0	0
			311777A00	CRACK	0	0	1	0	0	0	0	0	0
			311881A00	Modification	0	0	0	1	0	0	0	0	0
			311889A00	Modification	0	0	0	0	0	0	0	0	0
			311947A00	Modification	1	0	0	0	0	1	0	0	0
			312002A00	Modification	0	0	0	0	0	0	0	0	0
			312086A00	CRACK	0	0	0	0	0	0	0	0	0
			312090A00	Modification	0	0	1	0	0	1	0	0	0
			312091A00	Modification	0	0	0	0	0	0	0	0	1
			312105B00	CRACK	0	0	0	1	0	1	0	0	0
			312106B00	Modification	0	0	0	0	0	0	0	0	0
			312121A00	Modification	0	0	0	0	0	0	0	0	0
			312127A00	CRACK	0	0	0	1	0	0	0	0	0
			312130A00	Configuration	0	1	0	0	0	0	0	0	0
			312155A00	CRACK	0	1	0	0	0	0	0	0	0
			312161A00	Configuration	0	1	0	1	0	0	0	0	0
			312169A00	Configuration	0	1	0	0	0	1	0	0	0

Akuini · Jul 3, 2017

iris1007 said:
Hi Akuini,
I have another huge file which have many Items in a same column (column B) and I would like to search at PART (column D) and assign "1" if Yes else "0"..according to Item No.
Can you help?
Thanks in advance for the helps and hope to hear from you soon.

Ok, try this:

Code:

Sub a1011914a()
Dim i As Long, j As Long
Dim va, vb, vc

va = Range(Cells(2, "B"), Cells(10, "B")).Value
ra = Range("D:D").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
vb = Range(Cells(2, "D"), Cells(ra, "D")).Value
ReDim vc(1 To UBound(vb, 1), 1 To 9)

For i = 1 To UBound(vb, 1)
    For j = 1 To UBound(va, 1)
        If InStr(1, va(j, 1), vb(i, 1), 0) Then
            vc(i, j) = 1
                Else
            vc(i, j) = 0
        End If
    Next
Next

Range("F2").Resize(UBound(vc, 1), 9) = vc

End Sub

iris1007 · Jul 3, 2017

Hi Akuini,
Million Thanks for your fast respond.
As this items I have in my report can be up till 10k (column A & B), was that meant I need to change the "UBound(<acronym title="vBulletin">vb</acronym>, 1), 1 To 9)"? Can the Code stop if column A is Blank?

Once again Thanks!

Akuini · Jul 3, 2017

iris1007 said:
Hi Akuini,
Million Thanks for your fast respond.
As this items I have in my report can be up till 10k (column A & B), was that meant I need to change the "UBound(<acronym title="vBulletin">vb</acronym>, 1), 1 To 9)"? Can the Code stop if column A is Blank?

Once again Thanks!

I don't understand. If col A & B are 10k rows, do you mean Item No will go on I,J,K .... To 10K rows? Or after Item No I it will return back to A?

iris1007 · Jul 3, 2017

Hi Akuini,
My apology. The max items I have should be 350 instead. Thank you!

iris1007 · Jul 3, 2017

Hi Akuini,
Yes, you are right. Item No will go on I,J,K,L ......
Thanks in advance for ALL the helps.

VBA - search data based on Oldest & Newest Date

iris1007

New Member

Excel Facts

Akuini

Well-known Member

iris1007

New Member

Akuini

Well-known Member

iris1007

New Member

Akuini

Well-known Member

iris1007

New Member

Akuini

Well-known Member

iris1007

New Member

iris1007

New Member

Similar threads

Forum statistics

Share this page

VBA - search data based on Oldest & Newest Date

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock