Solved

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

Posted on 2013-06-06
3
637 Views
Last Modified: 2013-06-06
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
Comment
Question by:activematx
  • 2
3 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39226975
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
 
LVL 9

Author Comment

by:activematx
ID: 39226995
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
 
LVL 23

Expert Comment

by:NBVC
ID: 39227078
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question