Lines from Invoice

I’m sure there’s a simple solution staring me in the face that I’m over looking.

I have a spreadsheet with payments that has to get reformatted for another spreadsheet (spreadsheet 2).

Spreadsheet 2 has an Invoice tab and a Line tab. The Invoice tab contains only one entry per Invoice Number and a sum of the totals. The Line tab has a column for the Invoice ID from the Invoice Tab, and also a Line Number column to indicate if the invoice is made up of multiple items. I’m struggling to come up with a way to do the Line Number column.

I’ve tried getting a count of the number of times the Invoice ID is present, but I’m struggling to get an iteration of the Line Number to write to a cell in a For Each loop from it.

Example of Desired Output:

1 Like

Hi, it would be nice to see how you have your source excel sheet…

Meaning the original spreadsheet 1?

yes, we need to see how you have it before helping you achieve the desired output…

Post manipulating the comments of a report I’m creating the following to a tab:

Invoice ID Line Number Business Unit Source InvoiceNumber InvoiceAmount Invoice Date Supplier Name Supplier Number Supplier Site
Group1 GT 1976000910 73,283.55 3/20/2018 AETN FR 2419042 REM99_PURCH_PAY
Group1 GT 1976000909 12,528.35 3/20/2018 AETN FR 2419042 REM99_PURCH_PAY
Group1 GT 1976000909 27,465.39 3/20/2018 AETN FR 2419042 REM99_PURCH_PAY

The Invoice Tab is populated from that info as follows:

*Invoice ID *Business Unit *Source *Invoice Number *Invoice Amount *Invoice Date **Supplier Name **Supplier Number *Supplier Site
1 Group1 GT 1976000910 73283.55 3/20/2018 AETN FR 2419042 REM99_PURCH_PAY
2 Group1 GT 1976000909 39993.74 3/20/2018 AETN FR 2419042 REM99_PURCH_PAY

The Invoice ID is coming from turning the first set of data into a LINQ grouping by invoice number and then is being turned into a list and taking the index number of the Invoice Number from the list.

The desired output of this example would be:

*Invoice ID Line Number *Line Type *Amount
1 1 ITEM 73,283.55
2 1 ITEM 12,528.35
2 2 ITEM 27,465.39

sorry but this just got a little more confusing… what is your source data table? Where invoice id and line number should come from?

Invoice ID is being created, it doesn’t come from the source data.

ListA = (From p In dtWireFinal.Select()
Group p By ID=p.Item(“InvoiceNumber”).ToString Into GroupA=Group
Select Convert.ToString(GroupA.Sum(Function(x) Convert.ToDouble(x.Item(“InvoiceAmount”).ToString)))).ToList()

For Each item in ListA:
intCurrIndex = ListA.IndexOf(item)+1

Write Cell
intCurrIndex.ToString in Column A

Basically I need to create a line item for each row in the first table from 1 to the count of the number of times the Invoice Number exists - iterating by 1. I can write the Invoice ID to the first table in the prior post and that can be leveraged if needed.

Yes, I totally overthought it. Fixed with simple Excel If statement.

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.