• Status: Solved
• Priority: Medium
• Security: Public
• Views: 663

# Simple Excel Macro - Grab last row of a number and copy to new sheet

Hi Excel Macro Experts,

I need a macro or formula that will grab the last number from a column that is the same and export it to a new spreadsheet/workbook.

Here is what the workbook looks like inside:

I have highlighted the rows that need to be copied to a new sheet.  Notice how Column F has numbers in it which are the same.  What I need the macro/formula to do is export the last set from the series and copy it to a new sheet.

I have attached the workbook to this question.  Thank you so much.
Example.xlsx
0
activematx
• 2
1 Solution

Commented:
Here's a formula solution...

In first sheet add a helper column in column T.

In T2:

=IF(F3<>F2,COUNT(T\$1:T1)+1,"")

copied down

then in Sheet1, in A2:

=IFERROR(INDEX('FY2012 INVOICES'!A:A,MATCH(ROWS(\$A\$2:\$A2),'FY2012 INVOICES'!\$T:\$T,0)),"")

copied across to column S and down until you see blank rows... this gets you the info required.
0

Author Commented:
Hi NB_VC

This is great.  It appears to work.  Would you mind briefly explaining to me what each formula does, so I can have a basic understanding of what was done.  Thanks so much!
0

Commented:
The first formula just identifies and cumulatively counts the last occurance of each code in column F.

The Index formula, indexes the column to extract from, and matches a consecutively increasing number starting at 1 with the use of ROWS(\$A\$2:\$A2)... as you drag down this changes to ROWS(\$A\$2:\$A\$3), and so on.  That number is matched to column T on other sheet... and since the formula there was consecutively numbering the matches, then the numbers are matched in sequence and the indexed column item extracted.

The IFERROR() returns a blank when you have copied down more rows than there are matches in other sheet.

As you copy across the Indexed column A:A changes to B:B, etc to get the adjacent column info.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.