Adjusting data from Bottom to Top and right to left

rohitashsharma

Hi Team,

We have a huge amount of data on a spread sheet and where we have duplicate invoices and also there are multiple columns for Part Description, Quantity and Rate, now the case is lets say there are three rows for same invoice and each row has unique Part Description, Quantity and Rate but not in all the cells, I need to arrange the data from above said field to left and from bottom row to the top row example:

Original data

Desired Output:

I have also attached the data file with two tabs on it Original Data and the requirement.
Note: Can't avoid rest of the fields as well, so all fields are required.

Thanks,

Rohitash

REPORTS 4 - Copy.xlsx

JamesCharnley

Hi @rohitashsharma,

Knowing how to make this completely dynamic is difficult without being sure of a few things in the input, such as how many rows there might be per order. 

My attached screenshot won't work for the whole thing, but the logic is that the tag after transposing allows you to crosstab everything back onto the same row. In this case I can just use a static tag but in reality you'd want to use a multi-row formula to indicate an invoice number being different to the previous.

As for making all of the field names the same in the output, that might be possible to 'hack' using a multi field formula with invisible spaces, but I'm not sure how to make that dynamically myself. Maybe someone else can chime in on the bits I've missed.

Hope that helps at all.

James

ARInvoiceTPCT.yxzp