# Thread: How to create detail invoice from data rows

1. ## How to create detail invoice from data rows

Hi

I need help to create detail invoice from data rows below :
What formula to get data (A1:C5) in cell A9,A10,A11....

Thanks

A B C
1 Invoice No Item No Item Desc
2 PSI0001 STBX0001 Item Desc 0001
3 PSI0001 STBX0002 Item Desc 0002
4 PSI0002 STBX0003 Item Desc 0003
5 PSI0002 STBX0004 Item Desc 0004
6 .
7
8 Invoice No
9 PSI0001
10 STBX0001
11 STBX0002
12 PSI0002
13 STBX0003
14 STBX0004
15 .

2. Originally Posted by heryx
What formula to get data (A1:C5) in cell A9,A10,A11....

3. Originally Posted by navic
Hi Navic,

Example files below, thank you.
Detil Invoice Line.xlsx

4. Try this ARRAY formula (for all column range)
Code:
`=IFERROR(INDEX(\$B\$2:\$B\$18;SMALL(IF(\$E\$1=A2:A18;ROW(B2:B18)-1;"");ROW()-1));"")`
see attach example

5. Originally Posted by navic
Try this ARRAY formula (for all column range)
Code:
`=IFERROR(INDEX(\$B\$2:\$B\$18;SMALL(IF(\$E\$1=A2:A18;ROW(B2:B18)-1;"");ROW()-1));"")`
see attach example
Hi Navic,

Thanks for ARRAY formula, actually i need to extract the unique field "Invoice Number" then the "Item No" in the same rows.

See attach file

heryx-navic.xlsx

Thanks again

6. Originally Posted by heryx
......... in the same rows.
I guess you think the one column/multiple rows?
Try to look for a solution using VBA or you wait till you hear from someone else.

7. heryx, you can try Pivot Table (see attach)