Trying to recreate invoices with multiple types of data

[aforte]

Here goes,
I'm not a power user and my excel skill set is relatively low, but I've always been able to trudge my way through in Excel and create something that works. Right now, though, I'm at a loss. I've been tasked with recreating a series of invoices. We normally send the data out and an outside company creates the invoices. Now, however, we're attempting to bring that process in house as it will save money. The problem is that the invoices have 5 different types of data on them for 5 different lines of business. I've actually been able to recreate the invoice to some degree, but I'm at a road block in separating out the different lines of business. Every entity is referenced by an account number, but each account number has a different amount of rows of data in each section. This is my problem. Is there a way to adjust the output size by each account so that I don't have numerous empty rows and can that be dynamic? Eventually I will set up a macro to save all of these as individual .pdfs.

To illustrate what I'm talking about, each of these accounts would be created to a separate .pdf based on the account code. I've currently created the template and it is pulling in the proper data, however, it is formatted in a manner that I've left enough space for the largest amount of rows (in this example) the Job column. As you can see that varies by Account code. Account 1234 has the max amount of jobs for the business line one (5) but only 2 jobs for business line two. While Account 1235 has only 2 jobs in business line one but the max (4) in business line two. For Account 1234 I need to get rid of two rows in the area where business line two is displayed. For Account 1235 I need to get rid of two rows in business line one. Any thoughts on how to accomplish this?

Current:
(First Account)
Account code: 1234
Business line one

Job	User	Amount	Total
1	John Doe	1	2
2	Jane Doe	2	4
3	Bob Doe	3	6
4	Jerry Doe	1	2
5	Dave Doe	4	8

Business line two

Job	Date	Time	Amt	Total
1	1/1/2020	0900	2	4
2	1/2/2020	1000	4	8

(Second Account)
Account Code:
1235
Business line two

Job	User	Amount	Total
1	A Guy	2	4
2	A. Nother Guy	3	6

Business line two

Job	Date	Time	Amt	Total
1	1/2/2020	0900	1	2
2	1/3/2020	1000	2	4
3	1/4/2020	1100	3	6
4	1/5/2020	1200	4	8

Desired

(First Account)
Account code: 1234
Business line one

Job	User	Amount	Total
1	John Doe	1	2
2	Jane Doe	2	4
3	Bob Doe	3	6
4	Jerry Doe	1	2
5	Dave Doe	4	8

Business line two

Job	Date	Time	Amt	Total
1	1/1/2020	0900	2	4
2	1/2/2020	1000	4	8

(Second Account)
Account Code:
1235
Business line two

Job	User	Amount	Total
1	A Guy	2	4
2	A. Nother Guy	3	6

Business line two

Job	Date	Time	Amt	Total
1	1/2/2020	0900	1	2
2	1/3/2020	1000	2	4
3	1/4/2020	1100	3	6
4	1/5/2020	1200	4	8

Any help on this would be greatly appreciated.

 

[Toadstool]

Hi Aforte,

I'm not sure of the exact format and I'm guessing you're using formulae to pull in the data and putting a null output where no input data exists.

It appears column D should always have an Amt or Total so you could change your formulae for that column to output a single space. Now run a macro which checks column D and any with a single space gets EntireRow.Hidden = True set. The published PDF will not show those rows.

 

[aforte]

Hi Aforte,

I'm not sure of the exact format and I'm guessing you're using formulae to pull in the data and putting a null output where no input data exists.

It appears column D should always have an Amt or Total so you could change your formulae for that column to output a single space. Now run a macro which checks column D and any with a single space gets EntireRow.Hidden = True set. The published PDF will not show those rows.

Thanks Toadstool,

That makes sense.

I appreciate the help.