Expert: Richard Roberts - 5/5/2008

Question
Hi Richard

Thank you for your support,, I have try the Formula in the attached sheet and its not working correct

Let me Explain this issue in  more Detilas

I Have Customer Trnasactions in Sheet 1 as the following Example

Cust #                    Cust Name                Invoice #         Amount                    Date                Type      
1                    Osama Younis                    11761            10,000.00              01/01/08            Invoice   
2                    Adel Hassan                      11501              1,000.00              01/02/08            Invoice
1                    Osama Younis                    11761           -9,9700.00              01/03/08            Payment
1                    Osama Younis                    11851             1,000.00               01/04/08            Invoice                    
2                    Adel Hassan                       11501             -1,000.00              01/03/08            Payment                    
2                    Adel Hassan                       11502            12,000.00              01/04/08            Invoice
3                    Khaled Aziz                        11503            11,000.00              01/04/08            Invoice
4                    Tawik Faisal                       11504             12,000.00             01/04/08            Invoice    
3                    Khaled Aziz                        11503           -11,000.00              01/04/08            Payment
4                    Tawik Faisal                       11504            -12,000.00             01/04/08            Payment

I want to Extract the Above Transaction using Cust# as Key to build Customer Statement of Account   per Customer Like the Following Format ( in Sheet 2 )

Cust #  :-    1         
Customer Name :  Osama Younis

Date                Invoice #                Invoice                       Payment             Total
                                                   Amount                     Amount
01/01/08         11761                    10,000.00                                             10,000.00
01/03/08         11761                    -9,700.00                     -9,700.00            -9,700.00
   

Cust #  :-    2         
Customer Name :  Adel Hassan
Date                Invoice #                Invoice                       Payment             Total
                                                   Amount                     Amount

01/02/08            11501                    1000.00                                            1,000.00
01/03/08            11501                   -1000.00                      -1000.00          -1000.00                 

As you know using Vlookup will Extract only the 1st Line comtain Customer # 1 and the rest of transactions for this Customer will not Be Extracted

Is their Any Formula can deal with the above sheet as Data Base to generate the Statment of Account.

Please advise

Regards

Osama

----- Original Message ----


Answer
Osama

The following is an article from the microsoft website--I think it will help you.
Richard
======================================================================
How to look up a value in a list and return multiple corresponding values
Applies to: Microsoft Office Excel 2003
Applies to
Microsoft Office Excel 2003



This article was written by Ashish Mathur, a Microsoft MVP (Most Valuable Professional). Visit the Microsoft MVP Web site for more information.



In this article
Look up one value and return one corresponding value

Look up one value and return multiple corresponding values

Identify row numbers

Return corresponding values from column B

Remove possible error values

In this article, I provide a formula for looking up a value and returning multiple occurrences of the same corresponding value in a list; for example, if in a two-column list, there appear multiple values of the name "Ashish" in the first column, the formula returns all of the corresponding values in the same row in the second column.

Look up one value and return one corresponding value
You use the VLOOKUP function to search for a value in the first column of a list and return a value in the same row from another column in the list. Note that the VLOOKUP function is designed to return only the corresponding value of the first instance of a lookup value.

For example, if I type "Ashish" in cell A9, and I type the following VLOOKUP formula in B9:

=VLOOKUP(A9,A1:B7,2,FALSE)



The VLOOKUP function in cell B9 finds the first occurrence of the name "Ashish", and returns the corresponding value in the same row of the second column, which is 234.

Top of Page



Look up one value and return multiple corresponding values
The formula to look up one value and return multiple corresponding values can be stated as this: Identify the row numbers that contain the name "Ashish" in column A, return the corresponding values from column B, and remove possible error values.

Top of Page



Identify row numbers
Enter "Ashish" in cell A10. Then, enter the following array formula (CTRL+SHIFT+ENTER) in cell B10 to determine the row number of a corresponding value:

    

=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))


When you enter or fill this formula in subsequent cells, the formula returns the row numbers for each subsequent corresponding value, in this case, 4 and 7.

Top of Page



Return corresponding values from column B
Now, modify the preceding formula to return the corresponding value (and not the row number) and enter the formula as an array formula (CTRL+SHIFT+ENTER):

    

=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)


The first corresponding value is 234, the value that corresponds to the first occurrence of the name "Ashish".

When you enter or fill this formula in subsequent cells, the formula returns the subsequent corresponding values of 534 and 834.

Top of Page



Remove possible error values
When you enter or fill this formula in subsequent cells, the formula may return an error value because there are only three occurrences of the name "Ashish" in the first column. To hide the error values, use the ISERROR function. Modify the preceding formula and enter it as an array formula (CTRL+SHIFT+ENTER):


=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))




End of article