Forum Discussion
Allocating data from one cell to other rows and columns
Hi everyone!
I created a purchase order request from Jotform, but when I download the Excel file, all the information from the products come in one cell.
first excel file
I need the information to be allocated on separated columns and rows (example in the image below).
I've tried using the replace tool + text to columns but it doesn't work for me, it only register one paragraph from the text I receive. It is important to notice that the different products are separated by paragraphs (by pressing Enter).
This is the ideal layout for me:
what would be perfect
(if we could "merge¢er" the empty cells would be nice, but not needed)
If I'm able to achieve this, I can use "Text to Columns" to look like this:
after text to columns
And this is the end result I want.
It needs to be done automatically for me (Macro and/or VBA), because this file keeps updating after 15 days.
Any ideas on how to do this?
Thanks in advance,
Caio
3 Replies
ccgbalarin The attached file contains a table created with Power Query. It resembles your ideal layout, noting that merging cells is something you should avoid. Cosmetically nice looking but a pain when you want to do any analysis with the data afterwards.
Thanks Riny! It's exactly what I was looking for.
Can I do a Macro with the steps used in Power Query?
ccgbalarin Lear PowerQuery. It's much easier and more user friendly than VBA. The link below would be a good start. Somewhat of a learning curve, but once you get a feel for it, you wouldn't want to use anything else. And don't give up!
https://exceloffthegrid.com/power-query-introduction/
Read through the material and you'll learn about splitting columns into new rows. In your case the special delimiter is a "line-feed". And you'll be able to follow the few steps I applied to transform the raw data.
