How to transform manually created table into Alteryx friendly input

Hi All,

I would like to transform a manually created table in Excel (Backup.xlsx) into a simple machine readable table (Output needed.xlsx) that can be used by Alteryx for further processing. Both files are attached below. Any help would be highly appreciated. Thanks a lot in advance!

Output needed.xlsx

Backup file test.xlsx

Workflow

Tips and Tricks

Transformation

Preparation

Accepted answers

aatalai

@Nickolay is that from row 3 or row 7? I would import it from there and then use select record to remove unwanted rows/sample tool and set skip the first n rows. Let me know if that helps?

All comments

aatalai

The simplest way is have 2 data inputs from the same file and sheet.

1) import the information from row 9 onwards. and perform sumarises and selects to format

2) imoort the same sheet from e2 to b5 to get the date and fx rates, then append them to the section 1

let me know if that helps

Nickolay

Hi Aatalai,

Thank you for your reply! The date and fx rate date should be easy to add as they are the same for all the rows.

How can we transpose the Project, Name headers to the columns?

aatalai

@Nickolay is that from row 3 or row 7? I would import it from there and then use select record to remove unwanted rows/sample tool and set skip the first n rows. Let me know if that helps?

Nickolay

Yes, it helped. I managed to get what I wanted. Thanks a lot!

Screenshot 2024-01-31 122651.png

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335