Fill up/down issues due to inconsistent patterns

[npatrick]

Hi Everyone!

After parsing data from a PDF, I have columns that need to be filled up or down to align everything on one row. I used a combination of tools (transpose, cross tab, multi-row formula) to try and accomplish this but I'm running into issues because one of the user's values does not follow the same pattern as the others (see below tables). Because of this, the approval limit for the non-standard format user, Jane Doe in this example, is blank and the value is concatenated with the prior user's approval limit after aggregating with the cross-tab tool. I’m not sure where I’m going wrong and would appreciate any feedback. Please see attached workflow example for more details.

Standard Format

User ID	Email	Name	Payment Type Access	Approval Limit
[Null]	john.doe@gmail.com	John Doe	[Null]	[Null]
[Null]	Blank	Blank	Yes	$1
0145	Blank	Blank	[Null]	[Null]

Non-Standard Format

User ID	Email	Name	Payment Type Access	Approval Limit
[Null]	Blank	Blank	[Null]	[Null]
[Null]	jane.doe@gmail.com	Jane Doe	Yes	$1
0143	Blank	Blank	[Null]	[Null]

[MeganBowers]

 @npatrick  Your logic of transposing and then cross-tabbing makes sense, you just need to have a column identifying which records belong to which group (i.e. which person).

Does this solution attached work on the larger dataset? It assumes every group of records is separated by an all-null row like in your example. If the pattern of the all-null row is not in your dataset then you can use a multi-row formula to increase the Record field when a name is present.