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