Home

Return invoice line item numbers on list of multiple invoices

%3CLINGO-SUB%20id%3D%22lingo-sub-723277%22%20slang%3D%22en-US%22%3EReturn%20invoice%20line%20item%20numbers%20on%20list%20of%20multiple%20invoices%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-723277%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3ECurrently%20importing%20data%20into%20our%20new%20software%20program.%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20is%20a%20formula%20that%20can%20search%20a%20column%20and%20return%20the%20line%20items%20of%20that%20particular%20invoice%20number%3F%3C%2FP%3E%3CP%3EPlease%20see%20attached%20photo%20for%20further%20info%3A%20so%20I'm%20wanting%20Column%20A%20to%20read%20column%20B%20and%20return%20the%20line%20items%20for%20each%20invoice%20instead%20of%20me%20having%20to%20input%20manually.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20932px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F120816i998F9C1EAD4AADCB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Excel.JPG%22%20title%3D%22Excel.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-723277%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-724629%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20invoice%20line%20item%20numbers%20on%20list%20of%20multiple%20invoices%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-724629%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F110671%22%20target%3D%22_blank%22%3E%40Natasha%20Townsend%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnter%20this%20formula%20in%20A2%2C%20and%20copy%20down%20rows%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DCOUNTIF(B%242%3AB2%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EB2)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-724635%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20invoice%20line%20item%20numbers%20on%20list%20of%20multiple%20invoices%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-724635%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F110671%22%20target%3D%22_blank%22%3E%40Natasha%20Townsend%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIF(%24B%242%3A%24B2%2CB2)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-724728%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20invoice%20line%20item%20numbers%20on%20list%20of%20multiple%20invoices%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-724728%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F110671%22%20target%3D%22_blank%22%3E%40Natasha%20Townsend%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%201%20%2B%20IFERROR(%20(prior%20%5BLineItem%5D)%20%2F%26nbsp%3B%20(%5B%40InvoiceNo%5D%20%3D%20(prior%20%5BInvoiceNo%5D)%20)%2C%200%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3Ewhere%20%3CSTRONG%3E'prior'%3C%2FSTRONG%3E%20is%20a%20Name%20given%20to%20the%20row%20of%20the%20table%20directly%20before%20the%20current%20row.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EDefining%20prior%20to%20refer%20to%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3CFONT%20face%3D%22Verdana%2CArial%2CHelvetica%2Csans-serif%22%3E%3DOFFSET(%20Table1%5B%40%5D%2C%20-1%2C%200%20)%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%3CFONT%20face%3D%22Verdana%2CArial%2CHelvetica%2Csans-serif%22%3Eworks%20but%20makes%20the%20formula%20volatile.%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3E%3CFONT%20face%3D%22Verdana%2CArial%2CHelvetica%2Csans-serif%22%3EBecause%20the%20formula%20is%20likely%20to%20be%20unfamiliar%2C%20I%20have%20attached%20a%20file%20to%20demonstrate%20that%20it%20does%20address%20the%20stated%20problem.%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Natasha Townsend
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

@Natasha Townsend 

Enter this formula in A2, and copy down rows: 

=COUNTIF(B$2:B2,

B2)

@Natasha Townsend 

 

Try this formula:

 

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

 

@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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies