Pulling multiple items from a list of invoices to a separate sheet

Hi all
I have a list of invoices on a sheet, each with a unique number and associated to an account number.
Is there a way to pull all invoice data for one account number onto a separate sheet?
Initially thought of vlookup, but this would only pull the first one and I have no clue (yet!) how to use VBA.
Any help would be appreciated.
Kind regards
Chris

Hi,

Welcome to the forum.

This can be done with the help of a Pivot Table or some array formula.

Can you post a sample file with enough data in excel format without any confidential info please? Click on "Go Advanced" and "Manage Attachments" to upload a file.

thanks for your reply
i have attached (i hope!) a sheet to this post
the data on the left is test data from the invoice list and the bit on the right is my statement template.
hope this helps
chris

Hi,

Please try the following array formula (need to be confirmed by pressing CTRL+SHIFT+ENTER)

In H17:
=IFERROR(INDEX(A$1:A$14,SMALL(IF($C$1:$C$14=$P$6,ROW(A$1:A$14)-MIN(ROW(A$1:A$14))+1),ROWS($H$1:$H1))),"")

In J17:
=IFERROR(INDEX(B$1:B$14,SMALL(IF($C$1:$C$14=$P$6,ROW(B$1:B$14)-MIN(ROW(B$1:B$14))+1),ROWS($H$1:H1))),"")

In N17:
=IFERROR(INDEX(D$1:D$14,SMALL(IF($C$1:$C$14=$P$6,ROW(D$1:D$14)-MIN(ROW(D$1:D$14))+1),ROWS($H$1:J1))),"")

See the attached file.

That's brilliant!
Thankyou so much!
Now for the tricky question, what makes it work?
pointless having it working if you don't know how, plus i'm keen to learn

You are welcome

If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.

For help on array formula, please go through the following URLs:

https://support.office.com/en-us/art...2-ecfd5caa57c7

https://exceljet.net/excel-functions...small-function