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
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies