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

1. ## 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

2. ## Re: Pulling multiple items from a list of invoices to a separate sheet

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.

3. ## Re: Pulling multiple items from a list of invoices to a separate sheet

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

4. ## Re: Pulling multiple items from a list of invoices to a separate sheet

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.

5. ## Re: Pulling multiple items from a list of invoices to a separate sheet

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

6. ## Re: Pulling multiple items from a list of invoices to a separate sheet

You are welcome

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1