Solved

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

Posted on 2013-06-06
3
614 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now