Convert two rows having data into 2 column data

Question

Hi all, I have a transaction dataset in which amount is in line 1 and tax is in line 2. I want to bring this into 1 line with side by side column having amount and tax. The key is the invoice num (or reference in my case)

Input

Invoice numtax codeUSDamount123P1100123P11000

Expected output

invoice numtax codeAmountTax123P11000100

MadhavTR · Answer

Hi Kaustubh,

Thanks for your reply last time. I have now got 2 extra usecases on top it

1.Can i add and summarize based on certain conditions (here gst code will always be present)

2. Also take data from a certain column where the gstcode is missing (this will be true always)

for example

Input

Invoice NumDescriptiongstcodeUSDAmountTAXID123TaxP110 123TaxP110 123AmountP1100 123AmountP1100 123   ABCD

Output

Invoice Numtax codeAmountTaxTax ID123P120020ABCD

Thanks in advance

RJ_1988 · Answer

1) Add one identifier Column called as 'Type' (it can be dynamic based on the pattern).

2) Use a cross Tab tool - Group Data by 'Invoice Number' and 'Tax Code' , change Column headers 'Type' and Values for new columns as 'USDAmount', method for aggregating values 'Sum', you will get the desired result.

OTrieger · Answer

@MadhavTR 
In this way the most easiest way will be to use the Summarize Tool.
Group the data on Invoice Number and Tax code and Concatenate USAmount.
Then use Parse Text to Columns Tool and you will get the data in that manner.
What left is to rename the 2 fields, you can use Select tool to do that