Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a worksheet with columns A through BR.
Column B contains customers (repeating values)
Column J contains a filing date (repeating values)
Column O contains loan information

I would like to consolidate customers and filing date into 1 row and then have the loan information in subsequent rows appended to the end.

Data looks like:

Number | Customer   | ...| File Date |...| Loan Information|  
1      | Customer 1 | ...| 11/30/2012|...| Loan Info 1  
2      | Customer 1 | ...| 11/30/2012|...| Loan Info 2  
3      | Customer 1 | ...| 05/12/2011|...| Loan Info 1  
4      | Customer 1 | ...| 05/12/2011|...| Loan Info 2  
5      | Customer 2 | ...| 10/31/2012|...| Loan Info 1  
6      | Customer 2 | ...| 10/31/2012|...| Loan Info 2  
7      | Customer 2 | ...| 10/31/2012|...| Loan Info 3  
8      | Customer 3 | ...| 03/16/2010|...| Loan Info 1  
9      | Customer 3 | ...| 03/16/2010|...| Loan Info 2  
10     | Customer 3 | ...| 07/01/2013|...| Loan Info 1  

I would like to get to:

  
Number | Customer   | ...| File Date |...| Loan Information|...|BR|Loan Info 2| Loan Info 3| ect|   
1      | Customer 1 | ...| 11/30/2012|...| Loan Info 1     |...|BR|Loan Info 2  
3      | Customer 1 | ...| 05/12/2011|...| Loan Info 1     |...|BR|Loan Info 2  
5      | Customer 2 | ...| 10/31/2012|...| Loan Info 1     |...|BR|Loan Info 2|Loan Info 3  
8      | Customer 3 | ...| 03/16/2010|...| Loan Info 1     |...|BR|Loan Info 2  
10     | Customer 3 | ...| 07/01/2013|...| Loan Info 1  
share|improve this question
    
Welcome to superuser. What have you tried so far? Show us, where you stuck. Then we can help. –  nixda Jul 1 '13 at 16:03
    
I have tried using an array: =IFERROR(INDEX($O$2:$O$7567,MIN(IF($B$2:$B$7567=B2,IF($J$2:$J$7567=J2,IF($O$2:$O‌​$7567<>"",ROW($B$2:$B$7567),10000),10000),10000))),"") and had trouble when there is a 3rd or 4th row for a given customer date cobination this will also not allow me to delete the rows. I was hoping for a VBA solution but do not have the expertise to do so. Thanks Kevin –  Kevin Jul 1 '13 at 16:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.