Drawing the latest information from a range of numbers

[davemek21]

Hi Folks,

Long time lurker but today I was finally asked to do something I couldn't self teach myself so I reach out to you, the experts that dazzle me daily.

I have a list generated by our back end system. I've been tasked to pull out every line that is an older entry with just the latest entry remaining.

2016-09-22 12_33_49-Microsoft Excel - Cylinder location list 22.09.16.jpg

In the attached picture COLUMN A is what I am tracking and I only want to leave the row that is the last entry for it. COLUMN C seems like the best bet to work on as that is a location code and the highest number is always the latest entry.

The entire list is long with many entries.

[Pete_UK]

I can't read that, and I certainly do not want to recreate your data manually before I can try a few things out. It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

[davemek21]

Hi Pete, thanks for the reply. Hopefully this works. A small sample from the sheet.

[Pete_UK]

You can see in the attached file that I have used column I of Sheet1 as a helper column with this formula in I2:

=IF(A2<>A3,MAX(I$1:I1)+1,"-")

This can be copied down to beyond the bottom of your data (the hyphens will show you where the formula is active), and this sets up a unique sequential number for the final row of a particular block of data related to one cylinder, assuming the data is sorted by Cylinder #.

As you have a lot of data, I've extracted the required data to Sheet2, where I have copied your headings into B1:I1. I've used column A for another helper column, with this formula in A2:

=IFERROR(MATCH(ROWS($1:1),Sheet1!I:I,0),"")

This will return the row number in Sheet1 where the first record that we want can be found, and when it is copied down it will return the row number for the 2nd record, then the 3rd, and so on. I used this formula in B2:

=IF($A2="","",INDEX(Sheet1!A:A,$A2))

which will return the appropriate data from column A of Sheet1 for the record whose row is given in A2, and this can be copied across into C2:I2 to get the data from the other columns. The date columns need to be formatted appropriately, and then the row of formulae can be copied down as far as you need them - I've copied to row 15 in the example file.

Hope this helps.

Pete

[davemek21]

Absolutely fantastic Pete. This one really had me stuck but you've absolutely nailed it. I'll be trying to reverse engineer your formulas all day!

[Pete_UK]

Glad to help. Thanks for feeding back.

Pete