+ Reply to Thread
Results 1 to 6 of 6

Extracting lines after change

  1. #1
    Registered User
    Join Date
    12-24-2017
    Location
    Paramus, United States
    MS-Off Ver
    2010
    Posts
    47

    Extracting lines after change

    Hi,

    I have a set of data. I'm trying to extract only 1 line for each item code and invoice number and eliminate the rest. I have included a small example with my desired results below.

    Thanks
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,730

    Re: Extracting lines after change

    You could do this with a helper column, and use this, copied down...
    =COUNTIFS($B$2:B2,B2,$C$2:C2,C2)

    From there, you could either apply filter, filter on 1 and copy/paste to destination (need to do this manually each time you need to update), or use this ARRAY function (dynamic, will update with any additions, as long as the ranges are correct)...
    =IFERROR(INDEX(A$1:A$22,SMALL(IF($E$2:$E$22=1,ROW($E$2:$E$22)),ROWS($A$1:A1))),"")
    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    629

    Re: Extracting lines after change

    A25=IF(COLUMNS($A$24:A24)<=COLUMNS($A$1:$D$1),IFERROR(INDEX(A$2:A$22,SMALL(IF(FREQUENCY(IF(ISNUMBER(MATCH($C$2:$C$22,$C$2:$C$22,0)),MATCH($C$2:$C$22,$C$2:$C$22,0)),ROW(A$2:A$22)-ROW(A$2)+1),ROW(A$2:A$22)-ROW(A$2)+1),ROWS($A$2:A2))),""),"")


    Control+shift+enter

    copy across


    your custom format column D

  4. #4
    Registered User
    Join Date
    12-24-2017
    Location
    Paramus, United States
    MS-Off Ver
    2010
    Posts
    47

    Re: Extracting lines after change

    Hi Fdibbins,

    Thanks for your solution but there was a problem. I needed to make it start a new count when there was a change in amount instead of a change in item description.
    The problem is that when I have the same amount later in the spreadsheet, it keeps adding to the old count when I want a new count to start. Is there
    any way around this? I added a new file newtest.xlsx so you can see what I mean.

    Thanks
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-24-2017
    Location
    Paramus, United States
    MS-Off Ver
    2010
    Posts
    47

    Re: Extracting lines after change

    I typed in A25

    =IF(COLUMNS($A$24:A24)<=COLUMNS($A$1:$D$1),IFERROR(INDEX(A$2:A$22,SMALL(IF(FREQUENCY(IF(ISNUMBER(MATCH($C$2:$C$22,$C$2:$C$22,0)),MATCH($C$2:$C$22,$C$2:$C$22,0)),ROW(A$2:A$22)-ROW(A$2)+1),ROW(A$2:A$22)-ROW(A$2)+1),ROWS($A$2:A2))),""),"")

    and didn't get any result?

  6. #6
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    629

    Re: Extracting lines after change

    After you type in the formula, don't just press enter.
    Instead, press CTRL + SHIFT + ENTER

    If you've already entered the formula, then highlight the cell with the formula and press F2.


    Then press CTRL + SHIFT + ENTER


    When entered correctly, the formula will be enclosed in {brackets}
    Attached Files Attached Files
    Last edited by CARACALLA; 06-12-2020 at 10:28 AM.

+ 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. Extracting 3 lines per client set based on criteria
    By aimone111 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-05-2018, 06:03 AM
  2. [SOLVED] Extracting a certain number of lines of text
    By L.LEE in forum Excel General
    Replies: 3
    Last Post: 02-06-2016, 09:49 AM
  3. [SOLVED] Extracting Specific Correlating Lines of Code.
    By PhilE in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-08-2015, 11:05 AM
  4. Process for Extracting More than 3 lines of data
    By Suzanne3213 in forum Excel General
    Replies: 2
    Last Post: 04-18-2014, 01:55 AM
  5. Extracting lines with changed data between sheets
    By scaffdog845 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-09-2010, 04:58 AM
  6. Extracting lines from raw data
    By JEllison in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-17-2007, 08:45 AM
  7. Extracting specific lines from datasheet
    By SlipperyPete in forum Excel General
    Replies: 5
    Last Post: 12-21-2006, 10:54 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