# Extracting lines after change

1. ## 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

2. ## 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).

3. ## 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

4. ## 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

5. ## 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. ## 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}

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

#### 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