Get data from defined excel cells and append it in another excel file.

Question

I have multiple excel forms with same formats. I want to get data from some cells and write it in another excel file.

This is the excel form with questions in column A and response in column D (I have more than 100 files like this and want to process them in single run). I need to get the responses from particular cells and write it in another resultant template. Note:- I did not need all the responses but only few (may be Name, Company & Sex). Also cell reference will not change.

Result Template will look like

Anyone having the solution please share it with me. I need this code urgently.

Thanks in advance!!

Deepak Shaw

DawnDuong · Answer

hi @deepakshaw1991

Seems like you only have general schema in mind, so I thought it is more useful to give some generic principles when you need to handle such use cases

- When you dataset has many blank rows and columns, you can then use the data cleanse tool immediately after the INPUT tool to remove empty rows and columns. (this is helpful for your specific case when there are only 2 columns in each data)

- I assume you will need to read input from multiple tabs > use BATCH MACRO to do that. Make sure you output tab name or use some method to get the reference ID of where each dataset comes from. Make sure you have a SELECT tool before the MACRO OUTPUT so that the schema is constant across iterations to avoid errors.

- After you have stacked the data with the batch macro, you can use CROSS TAB tool to get the sum of the measurements that are present in your inputs.

- Then you can TRANSPOSE all column so that you have each row showing the sum for 1 measurement only.- Note: that you can dynamic rename here to get rid of the "SUM" in the header before transposing or use FORMULA tool to get rid of the "SUM" in the row value after transposing.

- Then it's a simple JOIN to get only the value you want

- Finally write the desired outputs to the desired cell references.

Regards,
Dawn.

jatinbangadynpro · Answer

First: We can surely pick specific cell references..Also we can manage additionally columns in select tool, can you please provide me dummy/sample data matching exactly your requirements I can modify and share it again. Meanwhile you can update the Alteryx software at your end as well

Second- how many sheets are there - I can modify this to cover sheets containing a specific identifier.

deepakshaw1991 · Answer

Thanks for sharing the workflow. I am unable to run it since I have lower version of Alteryx. However I get the understanding from the image attached.

There are some problems.

First:-  Question in column A may have slight changes across the forms. So when using the Cross Tab tool it can create additional columns. Can we just use the cell references of the response (Say:- D4, D8, D13) and get the value and write it in the result template.

Second:-  Sheet name is different across different files. However we can use contains formula since some portion will be same always. I don't have any idea to implement this.

Please let me know if we have any possibilities to implement this.

Also, I would like to thank you for such a quick response.

Thanks & Regards,

Deepak Shaw