Help with Multi Row tool to add down

[HW1]

Hi,

I want the multi row formula to add numbers when there is non null cell.

How can I do it?

I have the values currently as:

RecordID	Address
1
2
3	1
4
5
6
7
8
9
10	1
11
12
13
14
15
16
17
18

I want them to be 

RecordID	Address
1
2
3	1
4	2
5	3
6	4
7
8
9
10	1
11	2
12	3
13	4
14
15
16
17
18

I am using the multi row tool to be :

If !isNull([Address])
THEN [Row+1:Address] +1
ELSE [Address]
ENDIF

But its not cutting it..

what should the multirow formula be?

Please find attached workflow for reference

[HW1 · Accepted solution]

Figured out a solution in case anybody needs it.

[AngelosPachis]

Hi @HW1 ,

Can you try :

If !isNull([Address])
THEN [Address] 
ELSE  [Row-1:Address] +1
ENDIF

and make sure that you configure the multi-row formula tool to treat not available values as 0

I think this should do it. Is there any reason the address goes all the way until 4?

Thanks,

Angelos

[HW1]

@AngelosPachis  Yep. I am parsing PDF invoices which are hundreds and the addresses are split into different rows with underlying identifiers.

I want to concatenate the values so that the address is output in a single row. I am trying to figure that out.. If you can help, that can be great!

The progress so far:

RecordID	Value	Address
1	Flick Anticimex Pty Ltd TAX INVOICE
2	Page 1/2
3	😎 Anti Ci mex’ Flick Anticimex Pty Ltd ABN 85000059665
4	Your Bill-To Number Date of Issue Invoice Number Bill Enquiries
6	Email
7	. Total Payable
8	Sandra & John Wright Pty Ltd y
9	Accounts Payable Atn: Gaby 16.68
10	PO Box 118
11	ABN 13168617178 Due Date
12	Seven Hills, NSW 1730 07/04/21
13	Reference Date No. Unit Price Total Price
14	For the following services provided P/O No No. Completed Units (Ex GST) (Ex GST)
15	Monthly In Advance Invoice from Date of
16	Issue
17	Waste Options- Lavinia Horne	1
18	Unit 5, 32 Export Dr Whakatu Gold Coast	2
19	Fire Protec, Molendinar	3
21	Sanitary Service
23	Location Subtotal: 7.58
24	Monthly In Advance Invoice from Date of
25	Issue
26	Waste Options - Jaana Brown	1
27	98 Brisbane Terrace Sip ‘n’ Dip Australia,	2
28	Goodna	3
30	Sanitary Service
32	Location Subtotal: 7.58
33	Flick Anticimex Pty Ltd TAX INVOICE
34	Page 2/2
35	😎 Anti Ci mex’ Flick Anticimex Pty Ltd ABN 85000059665
36	Your Bill-To Number Date of Issue Invoice Number Bill Enquiries
38	Email
39	. Total Payable
40	Sandra & John Wright Pty Ltd y
41	Accounts Payable Atn: Gaby 16.68
42	PO Box 118
43	ABN 13168617178 Due Date
44	Seven Hills, NSW 1730 07/04/21
45	Reference Date No. Unit Price Total Price
46	For the following services provided P/O No No. Completed Units (Ex GST) (Ex GST)
47	Total Ex-GST: 15.16
48	Total GST Amount: 1.52
49	Total Payable: 16.68
50	GENERAL INQUIRIES: National.Enquiries@flick.com.au.
51	Credit Claims Must be advised in writing within 14 days of the invoice date
52	Your Bill-To Number Invoice Number Bill Enquiries Due Date Total Amount Payable
54	a Remittance Details for EFT Payments :
56	Ref: 1000002095 Please quote invoice number for prompt allocation
57	Remittances can be emailed to:
58	Telephone & Internet Banking — BPay® accounts.receivable@flick.com.au
59	Contact your bank or financial institution to make this payment
60	from your cheque, savings, debit, credit card or transaction
61	account. More info: www.bpay.com.au OVvisa [J Mastercard Tamex
62	Flick Anticimex Pty Ltd Amount Expiry Date cvc
63	Homebush West, NSW 2140
64	Card Holder's Name
65	Customer Signature

Expected:

RecordID	Value	Address	Address_1
1	Flick Anticimex Pty Ltd TAX INVOICE
2	Page 1/2
3	😎 Anti Ci mex’ Flick Anticimex Pty Ltd ABN 85000059665
4	Your Bill-To Number Date of Issue Invoice Number Bill Enquiries
6	Email
7	. Total Payable
8	Sandra & John Wright Pty Ltd y
9	Accounts Payable Atn: Gaby 16.68
10	PO Box 118
11	ABN 13168617178 Due Date
12	Seven Hills, NSW 1730 07/04/21
13	Reference Date No. Unit Price Total Price
14	For the following services provided P/O No No. Completed Units (Ex GST) (Ex GST)
15	Monthly In Advance Invoice from Date of
16	Issue
17	Waste Options- Lavinia Horne	1	Waste Options- Lavinia Horne Unit 5, 32 Export Dr Whakatu Gold Coast Fire Protec, Molendinar
18	Unit 5, 32 Export Dr Whakatu Gold Coast	2
19	Fire Protec, Molendinar	3
21	Sanitary Service
23	Location Subtotal: 7.58
24	Monthly In Advance Invoice from Date of
25	Issue
26	Waste Options - Jaana Brown	1	Waste Options - Jaana Brown 98 Brisbane Terrace Sip ‘n’ Dip Australia, Goodna
27	98 Brisbane Terrace Sip ‘n’ Dip Australia,	2
28	Goodna	3
30	Sanitary Service
32	Location Subtotal: 7.58
33	Flick Anticimex Pty Ltd TAX INVOICE
34	Page 2/2
35	😎 Anti Ci mex’ Flick Anticimex Pty Ltd ABN 85000059665
36	Your Bill-To Number Date of Issue Invoice Number Bill Enquiries
38	Email
39	. Total Payable
40	Sandra & John Wright Pty Ltd y
41	Accounts Payable Atn: Gaby 16.68
42	PO Box 118
43	ABN 13168617178 Due Date
44	Seven Hills, NSW 1730 07/04/21
45	Reference Date No. Unit Price Total Price
46	For the following services provided P/O No No. Completed Units (Ex GST) (Ex GST)
47	Total Ex-GST: 15.16
48	Total GST Amount: 1.52
49	Total Payable: 16.68
50	GENERAL INQUIRIES: National.Enquiries@flick.com.au.
51	Credit Claims Must be advised in writing within 14 days of the invoice date
52	Your Bill-To Number Invoice Number Bill Enquiries Due Date Total Amount Payable
54	a Remittance Details for EFT Payments :
56	Ref: 1000002095 Please quote invoice number for prompt allocation
57	Remittances can be emailed to:
58	Telephone & Internet Banking — BPay® accounts.receivable@flick.com.au
59	Contact your bank or financial institution to make this payment
60	from your cheque, savings, debit, credit card or transaction
61	account. More info: www.bpay.com.au OVvisa [J Mastercard Tamex
62	Flick Anticimex Pty Ltd Amount Expiry Date cvc
63	Homebush West, NSW 2140
64	Card Holder's Name
65	Customer Signature

I guess the multi row formula tool can help. How can I do it?

[AngelosPachis]

@HW1 In this instance you need a summarize tool to concatenate rows that have 1,2,3 in the address. To do that, you must first use a multi-row formula tool to group rows you want to concatenate together, so you can use that new field to group on later on.

Hope that helps,

Angelos