Macro to extract required data on the basis of an account number.

[Himanshu]

Hi Everyone,


I have a "sheet 1" containing data in more than 10000 thousands rows and 300+ different accounts.

Columns -Account, Vendor Name, Doc Number, Doc Date, Due Date, Net Date, Amount

Rows -1660001, TJ Max , 123456 , 07/08/2011, 14/07/2011, 14/07/2011, $1000

There are account numbers which are repetitive and have their respective details as per the column headings. For Instance account no. 1660001 can be there 50 times along with the corresponding data.(Line items)


What i am looking for is on "sheet 2" If i enter a particular account number in A1 i should get all the data pertaining to that account number only, whether it contains 5 line items(rows) or 50. Below is the required output


"Sheet 2" Cell - A1

1660001


Account, Vendor Name, Doc Number, Doc Date, Due Date, Net Date, Amount

1660001, TJ Max , 123456 , 07/08/2011, 14/07/2011, 14/07/2011, $1000


Is there any Macro that can make the output possible.


Many thanks in advance.

 

[Hui]

If you want to extract all the records matching your criteria, Have a look at using Advanced Filter.


To just acummulate the totals where it matches your criteria, look at Sumproduct or Sumifs

so on sheet2

G1: =Sumproduct((Sheet1!$A:$A=A1)*(Sheet1!$G:$G))

or

G1: =Sumifs(Sheet1!$G:$G,Sheet1!$A:$A,A1)