+ Reply to Thread
Results 1 to 6 of 6

Copy First and Last rows of Particular Value in Column A

  1. #1
    Registered User
    Join Date
    12-06-2016
    Location
    Utah, United States
    MS-Off Ver
    2010
    Posts
    2

    Copy First and Last rows of Particular Value in Column A

    I'm looking for a way to copy the rows of the first and last values for a particular model number in order to find out how long it takes to make each partifular model. Every number under the "model" colum will always change. i.e AAA.... BBB... are unique values and can have varying amount of products being made.

    Any help would be appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Copy First and Last rows of Particular Value in Column A

    Welcome to the forum.

    I can think of a few ways to do this with helper columns but I cant think of an elegant or simple way yet...

    EDIT: See the attachment as it works with some array formulas for the end point and a vlookup for the start point.
    Attached Files Attached Files
    Last edited by mikeTRON; 12-06-2016 at 02:58 PM.
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Copy First and Last rows of Particular Value in Column A

    I did this two ways.

    On the input sheet, I used formulas. The firs occurrence of a value can be found using MATCH. =MATCH(A2,$A$1:$A$15,0). The last occurrence is found using an array formula: =MAX((Range=Value) * ROW(Range)), or in this case: =MAX(($A$1:$A$15=A2)*ROW($A$1:$A$15)). From there you can pick off the dates and time using INDEX. You can, of course roll the formulas in the helper cells into one, big, ugly formula.

    http://www.utteraccess.com/wiki/inde..._Nth_Occurance

    For the second method, I used a helper column in column D to combine the date and time and then a pivot table to get the min and max of these values by Model.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Copy First and Last rows of Particular Value in Column A

    I did this two ways.

    On the input sheet, I used formulas. The first occurrence of a value can be found using MATCH. =MATCH(A2,$A$1:$A$15,0). The last occurrence is found using an array formula: =MAX((Range=Value) * ROW(Range)), or in this case: =MAX(($A$1:$A$15=A2)*ROW($A$1:$A$15)). From there you can pick off the dates and time using INDEX. You can, of course roll the formulas in the helper cells into one, big, ugly formula.

    http://www.utteraccess.com/wiki/inde..._Nth_Occurance

    For the second method, I used a helper column in column D to combine the date and time and then a pivot table to get the min and max of these values by Model.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Copy First and Last rows of Particular Value in Column A

    The Forum has been acting up tonight, so I wasn't able to post this earlier (I actually got a message "The server is busy - try again later" !!). Anyway, I've taken a different approach from the other submissions, using 3 helper columns in the main sheet (coloured blue in the attached file).

    This formula in D2, when copied down, will allocate a unique sequential number on the first row that a Model is encountered:

    =IF(COUNTIF(A$2:A2,A2)=1,MAX(D$1:D1)+1,"-")

    otherwise it returns a hyphen to show those rows where the formula is active. I then generate the minimum (earliest) date/time and the maximum (latest) date/time for each Model in columns E and F using these array* formulae:

    E2: =IF(ISNUMBER(D2),MIN(IF($A$2:$A$15=$A2,$B$2:$B$15+$C$2:$C$15)),"")

    F2: =IF(ISNUMBER(E2),MAX(IF($A$2:$A$15=$A2,$B$2:$B$15+$C$2:$C$15)),"")

    *Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual < Enter >.

    In the Output sheet I have moved your example data across a few columns, so that you can compare the results, and used this formula in A2:

    =IF(INT((ROWS($1:1)-1)/2)+1>MAX(Input!D:D),"",INDEX(Input!A:A,MATCH(INT((ROWS($1:1)-1)/2)+1,Input!$D:$D,0)))

    This brings the Model from the first sheet where column D is 1, and when copied down will repeat that value before getting the Model for the number 2 (twice), and so on for the other values in column D of the first sheet.

    This formula in D2:

    =IF($A2="","",INT(INDEX(Input!$E:$F,MATCH(INT((ROWS($1:1)-1)/2)+1,Input!$D:$D,0),MOD(ROWS($1:1)-1,2)+1)))

    will split the date out of the date/time in column E of the first sheet, and on the next row it will get the date from column F, and alternate this down the column. A similar formula in E2:

    =IF($A2="","",MOD(INDEX(Input!$E:$F,MATCH(INT((ROWS($1:1)-1)/2)+1,Input!$D:$D,0),MOD(ROWS($1:1)-1,2)+1),1))

    will split out the time portion alternately from column E and then column F, and so on.

    If you have more data in your real file you should adjust the ranges in the two array formulae in E2 and F2 of the first sheet (and use CSE again to commit them), and you will need to ensure that all formulae are copied down far enough to accommodate the amount of data that you have.

    Hope this helps.

    Pete
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-06-2016
    Location
    Utah, United States
    MS-Off Ver
    2010
    Posts
    2

    Re: Copy First and Last rows of Particular Value in Column A

    Pete,

    Thank you so much! That worked great!!

    EDIT: Miketron, your solution worked great too! Just saw your edit post!

    Thanks again for your guys' help!
    Last edited by JesusGar; 12-07-2016 at 03:55 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy rows and maintain Column width for each column
    By Rick_Stanich in forum Excel General
    Replies: 2
    Last Post: 06-24-2014, 08:06 AM
  2. Replies: 8
    Last Post: 04-11-2014, 10:18 AM
  3. Replies: 3
    Last Post: 03-04-2014, 09:08 PM
  4. [SOLVED] Macro to copy rows based on a critera and copy the name of the column
    By dreddster in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-10-2013, 11:34 AM
  5. Replies: 3
    Last Post: 01-30-2013, 04:32 PM
  6. Replies: 5
    Last Post: 02-07-2012, 04:55 PM
  7. Copy Column A rows based on Column B value
    By bauthement in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-04-2010, 11:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1