[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 660
  • Last Modified:

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:

 ExampleImage
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
Asked:
activematx
  • 2
1 Solution
 
NBVCCommented:
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
 
activematxAuthor 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
 
NBVCCommented:
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now