Cross Tab portion of data only

[KdnD]

Hello,

In the example below, I'm trying to cross tab portion of the data (GL account and Amount) only and the desired output would be: vendor no., vendor name, invoice no., invoice date, due date, routing code, (1st) gl account for the same vendor, (1st) amount for the same vendor, (2nd) gl account for the same vendor, (2nd) amount for the same vendor, etc. on 1 row. When there is a change in vendor number, a new row would be created for the new vendor interation. I feel like an iterative macro is needed but not sure where to start - any advice is greatly appreciated!

[Luke_C]

Hi @KdnD 

If I understand correctly you want a column for each GL account? If so using the crosstab tool should get this done without a macro.

1. Group by all the fields you listed (Vendor, Invoice, Date, etc)
2. 'Change Column Headers', select the GL Account field
3. 'Values for new columns', select the amount field
4. Choose Sum as the aggregation method.

Typically, if you post the actual data rather than a screenshot you might get a more complete answer.

[KdnD]

Hi @Luke_C 

Here's an example of the dataset and the desired output.

[Luke_C · Accepted solution]

@KdnD This is an interesting way to present the data. Right off the bat, Alteryx will not let you have multiple fields with the same name. So you might have to settle for for GL Account 1, GL Account 2, and so on.

Try the Make Columns tool. I'm curious to see other's solutions as well.

[KdnD]

@Luke_C Our AP upload format is special (for lack of better words) 😉

The make column tool did the trick and got me to the format I need the data to be in. Thanks for your help!

[Luke_C]

Great, glad that worked! If you're all set please be sure to accept the solution so people can find it easier in the future