Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.

Pull top level data in to table from vertical format

7 - Meteor



I have the attached workflow that uses a macro to look through our job directories find spreadsheets that contain a sheet called Master Budget and then pull the information from it. The problem i'm having is that the gyms have now changed the template to the attached Dummy Budget 1 format.


In cell B1 is the Job number that i need to pull in order to run joins off after we have the information. How can i pull that number out with the Prefix TP. they have made it a custom format so that it doesnt recognize the TP no: at the start. see below.


I would like to pull the information in to a data table that would also take figure from the Grand total column beginning in column F12. If the table could be outputted as below.


The directory will have to run through about 500+ of these sheets but they should be in the exact same format as these.


Job NumberProject NameMaterialsExternal Manufactured ItemsOVERSEAS PROCUREMENTPaintProduction PlanningFactorySiteLogistics
TP5555 36401.200000040921.22527.00




17 - Castor
17 - Castor

Hi @chelseariver90 


They did more than just change the format of the TP No.  The entire file is different.  Attached is the original file (Budget Original) and the current one.  The key to the whole process is first multi-row formula that determines the row type, H1, H2, etc.  Here the formula that's used





if isempty([Row-1:Row Type]) then
elseif [F5] = "GRAND TOTAL INC. WASTE" then
elseif [F2]="MARK-UP (%)" then
elseif [F5] = "GRAND TOTAL INC. MARK-UP" then
elseif [F1] = "1" then
	[Row-1:Row Type]





As you can see, It's looking for specific strings in specific columns.  These have changed.  For instance, the first else looks for  "GRAND TOTAL INC. WASTE". This header is gone.  As is the  "Mark-UP (%)" label. "Grand Total Inc. Mark-up" is now "Grand Total"


Before you had 4 header sections before the data started.  Now you have only 3. 




Modify the multi-row formula to find these 3 sections using the corresponding start header text. 


For the formatting problem, I've added a Multi-row  in the Job... container that adds TP NO: if the row after it contains "Quote No:".