Loop through Range

[johnbird1988]

Hello

I am after some best approach advice. I have a list of transaction data in Sheet1 and a list of customers in Sheet2. I would like to return in 4 individual columns in Sheet2 next the the customer ID the four latest transactions.

Which is the best approach than doing two loops. Is there a way I can achieve this using one loop through the data in Sheet1. I was possibly think about adding the Customer IDs to a collection but them I was not sure about how to write the collection back into Sheet2.

Open to options and Ideas to do this in the most efficient way.

Thank you

John

 

[Dim Me as xlNoob]

Is VBA required?

I made this formula with:

Sheet 1
Customer IDs in column A, transaction numbers in column B

Sheet 2
Customer IDs in column A, most recent transactions in columns B to E (most recent to 4th most respectively).

Sheet 2 cell B2 =INDEX(Sheet1!$B$2:$B$28,LARGE(IF(Sheet1!$A$2:$A$28=$A2,ROW(Sheet1!$A$2:$A$28)-MIN(ROW(Sheet1!$A$2:$A$28))+1),COLUMNS($B2:B2)))

Confirmed with CTL+SHIFT+ENTER then copied accross and down as needed.