VBA - search data based on Oldest & Newest Date

I

iris1007

New Member
Joined
Jun 28, 2017
Messages
31
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.
CUSTOMERPARTDATEPre-GROUPThis should be the Result after the Code run
HONEY4073121/10/201400523
HONEYG326623/5/201100523
HONEYG326627/7/201100523HONEY
SYSEMG326616/1/201200523HONEY
SYSEMG32664/7/201300523SYSEM
HONEYG326616/12/201400523SYSEM
WUHANG326922/7/201400523
HONEYG335410/4/200800523
SYSEMG335430/3/201000523HONEY
SYSEMG335521/7/200600523
HONEYK27886/2/201700523
SHANGHAIK27892/12/201600523
HONEY265967/7/2007AA0052
HONEY2659629/3/2010AA0052HONEY
WUHAN2677125/9/2012AA0052
SYSEM2677113/2/2014AA0052WUHAN
SHANGHAI3869923/2/2017AA0052
HONEY90-2512/5/2008AA0052
HONEY90-259/12/2008AA0052HONEY
SYSEM90-2526/2/2013AA0052HONEY
SYSEMG325417/4/2013AA0052
SYSEMG325423/10/2013AA0052SYSEM
SYSEMG325424/6/2014AA0052SYSEM
HONEYG325420/8/2014AA0052SYSEM
HONEYG325420/1/2015AA0052HONEY
HONEYG325623/12/2011AA0052
HONEYG325723/12/2011AA0052
SYSEMG325715/5/2013AA0052HONEY
HONEYG325721/10/2014AA0052SYSEM
HONEYG325719/12/2014AA0052HONEY
WUHANG326118/3/2011AA0052
SYSEMG32616/6/2014AA0052WUHAN
SYSEMG326219/6/2015AA0052
HONEYG326217/3/2016AA0052SYSEM

<tbody>
</tbody><colgroup><col span="2"><col><col><col></colgroup>
 
Sort by date Sort by votes
iris1007 said:
Hi Akuini,
My apology. The max items I have should be 350 instead. Thank you!
Click to expand...


Ok, try this:
Code: 
Sub a1011914a()
Dim i As Long, j As Long, ra As Long
Dim va, vb, vc

ra = Range("B" & Rows.count).End(xlUp).row
va = Range(Cells(2, "B"), Cells(ra, "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 UBound(va, 1))

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), UBound(vc, 2)) = vc

End Sub
 
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Hi Akuini,
Thank you so much for all your helps. It really work perfectly on my file.

One more help :)
Is the code finding case sensitive?
example: the PART in column B is 26A00, but in column D is 26a00

As sometime there may have typo error.

Thanks!

 
Upvote 0
iris1007 said:
Hi Akuini,
Thank you so much for all your helps. It really work perfectly on my file.

One more help :)
Is the code finding case sensitive?
example: the PART in column B is 26A00, but in column D is 26a00

As sometime there may have typo error.

Thanks!
Click to expand...


It's case sensitive, if you want it case insensitive, use this one:
Code: 
Sub a1011914a()
Dim i As Long, j As Long, ra As Long
Dim va, vb, vc

ra = Range("B" & Rows.count).End(xlUp).row
va = Range(Cells(2, "B"), Cells(ra, "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 UBound(va, 1))

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

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

End Sub
 
Upvote 0
Hi Akuini,
Need help from you again.
Following is my sample report:
OPERATORJan-08Feb-08Mar-08Apr-08May-08Jun-08Jul-08Aug-08Sep-08Oct-08Nov-08Dec-08
JAPAN303.13289.10337.18290.15318.40284.58334.58294.18288.12283.98302.33309.93
JAPAN267.85307.88312.80294.47309.87295.38279.77277.17167.4746.27265.33242.38
JAPAN320.85304.37328.97314.33307.27317.38286.85310.03314.27296.70331.98361.90
JAPAN318.57323.72290.60291.73306.05294.13 265.57267.93322.32287.35314.58
JAPAN311.63310.90329.83259.70308.27299.18303.17326.27 318.23352.43
JAPAN262.43320.20326.95307.50 296.28332.87314.65331.10207.705.28279.22
USA274.90176.32331.73303.28 318.12303.30333.17294.98333.53293.15333.90
USA329.05302.17284.77291.92 322.68320.77331.12278.28304.47 334.02
USA331.80307.90352.58269.82324.35305.67331.45298.48218.87311.10312.47345.27
USA301.60316.45316.08304.48311.25305.15294.13243.77321.13318.80318.82336.32
USA331.83316.53341.35314.22312.05267.68351.55262.00277.18326.50310.53358.68
HONG KONG 309.97314.40318.02313.60294.65322.98309.70315.45 285.93297.70
HONG KONG301.88297.65317.13207.45266.95312.82330.45298.05346.65269.58317.13313.68
HONG KONG312.03 337.80322.90298.55320.35284.42302.30302.45323.38322.53329.95
HONG KONG324.67307.60330.67312.85327.15277.22330.33311.83279.12335.33305.27333.97
HONG KONG315.08293.63307.48324.38338.67154.25 259.60111.57245.355.32247.60
HONG KONG317.97303.70315.78313.12319.88321.30319.48271.13290.58279.00296.82302.30
INDIA323.38291.62240.45258.85284.20274.15305.40317.95329.28344.37309.60321.92
INDIA294.77310.02340.15313.97315.72 315.00309.55326.80317.65307.60308.75
INDIA322.45282.17306.07310.47320.37315.50337.98294.53294.27271.88310.65315.17

<tbody>
</tbody><colgroup><col><col span="12"></colgroup>


I would like to rearrange it to...
OPERATORVolumeMONTH
JAPAN303.1333333Jan-04
JAPAN267.85Jan-04
JAPAN320.85Jan-04
JAPAN318.5666667Jan-04
JAPAN311.6333333Jan-04
JAPAN262.4333333Jan-04
AIR ASIA - MAA232.6166667Jan-04
USA274.9Jan-04
USA329.05Jan-04
USA331.8Jan-04
AIR ASIA - MAA303.9333333Jan-04
AIR ASIA - MAA334.5166667Jan-04
AIR ASIA - MAA324.1166667Jan-04
USA301.6Jan-04
USA331.8333333Jan-04
AIR ASIA - MAA324.1166667Jan-04
AIR ASIA - MAA332.3833333Jan-04
HONG KONG301.8833333Jan-04
HONG KONG312.0333333Jan-04
HONG KONG324.6666667Jan-04
HONG KONG315.0833333Jan-04
HONG KONG317.9666667Jan-04
INDIA323.3833333Jan-04
INDIA294.7666667Jan-04
INDIA322.45Jan-04
JAPAN289.1Feb-04
JAPAN307.8833333Feb-04
JAPAN304.3666667Feb-04
JAPAN323.7166667Feb-04
JAPAN310.9Feb-04
JAPAN320.2Feb-04
AIR ASIA - MAA305.9333333Feb-04
USA176.3166667Feb-04
USA302.1666667Feb-04
USA307.9Feb-04
AIR ASIA - MAA275.2833333Feb-04
AIR ASIA - MAA310.0166667Feb-04
AIR ASIA - MAA291.15Feb-04
USA316.45Feb-04
USA316.5333333Feb-04
AIR ASIA - MAA322.5666667Feb-04
AIR ASIA - MAA324.6833333Feb-04
HONG KONG309.9666667Feb-04
HONG KONG297.65Feb-04
HONG KONG307.6Feb-04
HONG KONG293.6333333Feb-04
HONG KONG303.7Feb-04
INDIA291.6166667Feb-04
INDIA310.0166667Feb-04
INDIA282.1666667Feb-04
JAPAN337.1833333Mar-04
JAPAN312.8Mar-04
JAPAN328.9666667Mar-04
JAPAN290.6Mar-04
JAPAN329.8333333Mar-04
JAPAN326.95Mar-04
AIR ASIA - MAA311.1666667Mar-04
USA331.7333333Mar-04
USA284.7666667Mar-04
USA352.5833333Mar-04
AIR ASIA - MAA312.8Mar-04
AIR ASIA - MAA338.3833333Mar-04
AIR ASIA - MAA347.35Mar-04
USA316.0833333Mar-04
USA341.35Mar-04
AIR ASIA - MAA319.6666667Mar-04
AIR ASIA - MAA339.3666667Mar-04
HONG KONG314.4Mar-04
HONG KONG317.1333333Mar-04
HONG KONG337.8Mar-04
HONG KONG330.6666667Mar-04
HONG KONG307.4833333Mar-04
HONG KONG315.7833333Mar-04
INDIA240.45Mar-04
INDIA340.15Mar-04
INDIA306.0666667Mar-04
JAPAN290.15Apr-04
JAPAN294.4666667Apr-04
JAPAN314.3333333Apr-04
JAPAN291.7333333Apr-04
JAPAN259.7Apr-04
JAPAN307.5Apr-04
AIR ASIA - MAA306.8333333Apr-04
USA303.2833333Apr-04
USA291.9166667Apr-04
USA269.8166667Apr-04
AIR ASIA - MAA310.65Apr-04
AIR ASIA - MAA321Apr-04
AIR ASIA - MAA315.4833333Apr-04
USA304.4833333Apr-04
USA314.2166667Apr-04
AIR ASIA - MAA292.3666667Apr-04
AIR ASIA - MAA329.9166667Apr-04
HONG KONG318.0166667Apr-04
HONG KONG207.45Apr-04
HONG KONG322.9Apr-04
HONG KONG312.85Apr-04
HONG KONG324.3833333Apr-04
HONG KONG313.1166667Apr-04
INDIA258.85Apr-04
INDIA313.9666667Apr-04
INDIA310.4666667Apr-04
JAPAN318.4May-04
JAPAN309.8666667May-04
JAPAN307.2666667May-04
JAPAN306.05May-04
JAPAN308.2666667May-04
USA324.35May-04
AIR ASIA - MAA270.3666667May-04
AIR ASIA - MAA296.9666667May-04
AIR ASIA - MAA317.9166667May-04
USA311.25May-04
USA312.05May-04
AIR ASIA - MAA346.3166667May-04
AIR ASIA - MAA325.2333333May-04
HONG KONG313.6May-04
HONG KONG266.95May-04
HONG KONG298.55May-04
HONG KONG327.15May-04
HONG KONG338.6666667May-04
HONG KONG319.8833333May-04
INDIA284.2May-04
INDIA315.7166667May-04
INDIA320.3666667May-04
JAPAN284.5833333Jun-04
JAPAN295.3833333Jun-04
JAPAN317.3833333Jun-04
JAPAN294.1333333Jun-04
JAPAN299.1833333Jun-04
JAPAN296.2833333Jun-04
AIR ASIA - MAA283.6166667Jun-04
USA318.1166667Jun-04
USA322.6833333Jun-04
USA305.6666667Jun-04
AIR ASIA - MAA303.5666667Jun-04
AIR ASIA - MAA279.5666667Jun-04
AIR ASIA - MAA242.9Jun-04
USA305.15Jun-04
USA267.6833333Jun-04
AIR ASIA - MAA308.7666667Jun-04
AIR ASIA - MAA308.85Jun-04
HONG KONG294.65Jun-04
HONG KONG312.8166667Jun-04
HONG KONG320.35Jun-04
HONG KONG277.2166667Jun-04
HONG KONG154.25Jun-04
HONG KONG321.3Jun-04
INDIA274.15Jun-04
INDIA315.5Jun-04
JAPAN334.5833333Jul-04
JAPAN279.7666667Jul-04
JAPAN286.85Jul-04
JAPAN303.1666667Jul-04
JAPAN332.8666667Jul-04
AIR ASIA - MAA338.25Jul-04
USA303.3Jul-04
USA320.7666667Jul-04
USA331.45Jul-04
AIR ASIA - MAA277.3833333Jul-04
AIR ASIA - MAA314.4Jul-04
AIR ASIA - MAA329.9166667Jul-04
USA294.1333333Jul-04
USA351.55Jul-04
AIR ASIA - MAA339.85Jul-04
AIR ASIA - MAA212.4833333Jul-04
HONG KONG322.9833333Jul-04
HONG KONG330.45Jul-04
HONG KONG284.4166667Jul-04
HONG KONG330.3333333Jul-04
HONG KONG319.4833333Jul-04
INDIA305.4Jul-04
INDIA315Jul-04
INDIA337.9833333Jul-04
JAPAN294.1833333Aug-04
JAPAN277.1666667Aug-04
JAPAN310.0333333Aug-04
JAPAN265.5666667Aug-04
JAPAN326.2666667Aug-04
JAPAN314.65Aug-04
AIR ASIA - MAA305.1166667Aug-04
USA333.1666667Aug-04
USA331.1166667Aug-04
USA298.4833333Aug-04
AIR ASIA - MAA279.1833333Aug-04
AIR ASIA - MAA292.2166667Aug-04
AIR ASIA - MAA321.05Aug-04
USA243.7666667Aug-04
USA262Aug-04
AIR ASIA - MAA239.2166667Aug-04
AIR ASIA - MAA338.4833333Aug-04
HONG KONG309.7Aug-04
HONG KONG298.05Aug-04
HONG KONG302.3Aug-04
HONG KONG311.8333333Aug-04
HONG KONG259.6Aug-04
HONG KONG271.1333333Aug-04
INDIA317.95Aug-04
INDIA309.55Aug-04
INDIA294.5333333Aug-04
JAPAN288.1166667Sep-04
JAPAN167.4666667Sep-04
JAPAN314.2666667Sep-04
JAPAN267.9333333Sep-04
JAPAN331.1Sep-04
AIR ASIA - MAA338.95Sep-04
USA294.9833333Sep-04
USA278.2833333Sep-04
USA218.8666667Sep-04
AIR ASIA - MAA304.0333333Sep-04
AIR ASIA - MAA303.4833333Sep-04
AIR ASIA - MAA285.1666667Sep-04
USA321.1333333Sep-04
USA277.1833333Sep-04
AIR ASIA - MAA286.1Sep-04
AIR ASIA - MAA258.7166667Sep-04
HONG KONG315.45Sep-04
HONG KONG346.65Sep-04
HONG KONG302.45Sep-04
HONG KONG279.1166667Sep-04
HONG KONG111.5666667Sep-04
HONG KONG290.5833333Sep-04
INDIA329.2833333Sep-04
INDIA326.8Sep-04
INDIA294.2666667Sep-04
JAPAN283.9833333Oct-04
JAPAN46.26666667Oct-04
JAPAN296.7Oct-04
JAPAN322.3166667Oct-04
JAPAN207.7Oct-04
AIR ASIA - MAA319.75Oct-04
USA333.5333333Oct-04
USA304.4666667Oct-04
USA311.1Oct-04
AIR ASIA - MAA314.1Oct-04
AIR ASIA - MAA321.6833333Oct-04
AIR ASIA - MAA278Oct-04
USA318.8Oct-04
USA326.5Oct-04
AIR ASIA - MAA350.2666667Oct-04
AIR ASIA - MAA297.0333333Oct-04
HONG KONG269.5833333Oct-04
HONG KONG323.3833333Oct-04
HONG KONG335.3333333Oct-04
HONG KONG245.35Oct-04
HONG KONG279Oct-04
INDIA344.3666667Oct-04
INDIA317.65Oct-04
INDIA271.8833333Oct-04
JAPAN302.3333333Nov-04
JAPAN265.3333333Nov-04
JAPAN331.9833333Nov-04
JAPAN287.35Nov-04
JAPAN318.2333333Nov-04
JAPAN5.283333333Nov-04
AIR ASIA - MAA327.7Nov-04
USA293.15Nov-04
USA312.4666667Nov-04
AIR ASIA - MAA313.2666667Nov-04
AIR ASIA - MAA286.1166667Nov-04
AIR ASIA - MAA326.2166667Nov-04
USA318.8166667Nov-04
USA310.5333333Nov-04
AIR ASIA - MAA304.8666667Nov-04
AIR ASIA - MAA268.9Nov-04
HONG KONG285.9333333Nov-04
HONG KONG317.1333333Nov-04
HONG KONG322.5333333Nov-04
HONG KONG305.2666667Nov-04
HONG KONG5.316666667Nov-04
HONG KONG296.8166667Nov-04
INDIA309.6Nov-04
INDIA307.6Nov-04
INDIA310.65Nov-04
JAPAN309.9333333Dec-04
JAPAN242.3833333Dec-04
JAPAN361.9Dec-04
JAPAN314.5833333Dec-04
JAPAN352.4333333Dec-04
JAPAN279.2166667Dec-04
AIR ASIA - MAA345.5Dec-04
USA333.9Dec-04
USA334.0166667Dec-04
USA345.2666667Dec-04
AIR ASIA - MAA313.7833333Dec-04
AIR ASIA - MAA324.8166667Dec-04
AIR ASIA - MAA349.95Dec-04
USA336.3166667Dec-04
USA358.6833333Dec-04
AIR ASIA - MAA362.2Dec-04
AIR ASIA - MAA336.9Dec-04
HONG KONG297.7Dec-04
HONG KONG313.6833333Dec-04
HONG KONG329.95Dec-04
HONG KONG333.9666667Dec-04
HONG KONG247.6Dec-04
HONG KONG302.3Dec-04
INDIA321.9166667Dec-04
INDIA308.75Dec-04
INDIA315.1666667Dec-04

<tbody>
</tbody><colgroup><col span="3"></colgroup>

Hope to hear from you soon.
Thanks in advance for the helps.
 
Upvote 0
Since this is a new topic, I suggest you start a new thread, so more people are willing to help you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Locked
  • Question
VBA - Sort data on TextBox1 from oldest to newest.
Replies
4
Views
345
Fluff
Fluff
A
  • Question
Displays data from oldest to newest VBA
Replies
1
Views
343
Monty Norman
M
A
  • Question
Lookup+sum+max
Replies
2
Views
406
aarky
A
K
  • Solved
Populate Data Based on Start and End Date
Replies
5
Views
1K
Fluff
Fluff
X
  • Question
Need help in copy and pasting multiple lines based on a criteria
Replies
0
Views
510
xlbaby
X

Forum statistics

Threads
1,215,641
Messages
6,125,982
Members
449,276
Latest member
surendra75

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