creating pivot sheet in excel file

Question

I'm preparing the files using Order Co. I'm doing that using formula and output tool.

I would like to ask you if is it possible to have for each file two sheets: one with raw data (I have it) and one with pivot?
Can I create these files without  'File' column?

I've attached the sample of my current output.

8590.xlsx

davidskaife · Accepted Answer

Hi @Neffri

I responded on the previous thread with a solution, but here it is as well:

A Block Until Done tool ensures that the first workstream is completely finished before attempting the second (so you don't get errors about the excel file already in use), the second stream summerises as per your output, generates the filepath based on Order Co as it was done in the first stream, using a Select tool to remove the Order Co field, and then adds a new tab called Pivot 180+ to the existing excel file.

Edit: to make sure the Filepath doesn't appear on the output uncheck the 'Keep Field in Output' option at the bottom of the Output tool

I'd also make note of the Output Options for both workstreams as well

PanPP · Accepted Answer

Hi @Neffri

You can bring in all the files in one go if that is what you are looking for.

1) Select one of the excel files to bring in, select the sheet and then change the end of the folder path to the following (C:\Folder\Files\*.xlsx)

- The * will bring in all excel files.

2) If you don't want any columns (File column) you can use a select tool to deselect that column.

If you are using the output tool and adding the file column, uncheck keep field in output at the bottom of the Output tool.

3) You can use the block until done tool to output to 2 sheets within the same file, making sure the first sheet is written first followed by the second sheet.

Hope this helps.