Return invoice line item numbers on list of multiple invoices

Highlighted
Occasional Contributor

Hi All,

Currently importing data into our new software program. 

Is there is a formula that can search a column and return the line items of that particular invoice number?

Please see attached photo for further info: so I'm wanting Column A to read column B and return the line items for each invoice instead of me having to input manually.

Excel.JPG

3 Replies
Highlighted

@Natasha Townsend 

Enter this formula in A2, and copy down rows: 

=COUNTIF(B$2:B2,

B2)

Highlighted

@Natasha Townsend 

 

Try this formula:

 

=COUNTIF($B$2:$B2,B2)

 

Highlighted

@Natasha Townsend 

= 1 + IFERROR( (prior [LineItem]) /  ([@InvoiceNo] = (prior [InvoiceNo]) ), 0 )

where 'prior' is a Name given to the row of the table directly before the current row.

Defining prior to refer to

=OFFSET( Table1[@], -1, 0 )

works but makes the formula volatile.

 

Because the formula is likely to be unfamiliar, I have attached a file to demonstrate that it does address the stated problem.