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

[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

[rohitashsharma]

Thanks James for your quick response

However on your query related to the Rows per Invoice may vary since sometimes they may 2/3 or sometimes more than that, yet the logic will be same always.

Please suggest if the procedure will always be same or there should be some changes to the workflow.

Regards,

Rohitash

[rohitashsharma]

Hi James,

The workflow doesn't work on the large data set where we have multiple duplicate invoices, would request you please help.

Thanks,

Rohitash

[JamesCharnley]

Hi @rohitashsharma I've attached an updated version of the workflow that is dynamically adding the tag with a multi-row. It's not the cleanest but it's late! I'm not 100% sure it's the desired output but it seems consistent based on the initial example.

[rohitashsharma]

Hi James, 

I appreciate your support, however it still doesn't solve the problem, please find the explanation below:

For a particular invoice number there may be multiple row and for example on the first row the Part Desc., Quantity and Rate may be on Part Desc 1, Quantity1, Rate 1 and for the other row of same invoice it may exist on Part Desc3, Quantity3 and Rate3, so we'll have to move the data existing on on Part Desc3, Quantity 3 and rate3 to Part Desc2, Quantity2 and Rate2, And So on ...

Hope I am able to explain now.

Thanks,

Rohitash

[JamesCharnley]

Hi @rohitashsharma ,

I get you now! Took me a little while to work out how to bypass the auto sort on the cross tab. This is truthfully a pretty ugly workflow but I think it fixes the issue.

[rohitashsharma]

Hi James,

There is still something which doesn't allow us to have the desired result eg:

Invoice # 500104353 wherein we have information on Parts Desc#5, Quantity5 and Std Rate5 which should have been moved to the columns Part Desc1, Quantity1 and Rate1:(So if we have data on Part7 and rest columns from Part1 to Part6 are blank then the information on part7 should be sifted to Part1 along with Quantity and rate and so on, also once the information is shifted to the left the right columns should be blank)

From:

Thanks,

Rohitash

[JamesCharnley]

Ah ok @rohitashsharma. I'd minimised the data in a select at the start to help me work with it but hadn't re-ticked them. I just re-ticked those columns in the first select and they should be getting pulled through now.

[rohitashsharma]

Hi James,

The workflow creates multiple columns, for a single column eg: parts Desc1 -18, Quantity 1-18 and Std Rate 1-18, how can I Identify the correct one, I mean every month there will be a fresh input and not sure which column I should consider:

Thanks,

Rohitash