Forum Discussion

ccgbalarin's avatar
ccgbalarin
Copper Contributor
Nov 18, 2021

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. I need the information to be alloc...
excel
Macros and VBA
office 365
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Nov 18, 2021

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.

 

ccgbalarin's avatar
ccgbalarin
Copper Contributor
Nov 18, 2021

Riny_van_Eekelen 

Thanks Riny! It's exactly what I was looking for.

 

Can I do a Macro with the steps used in Power Query?

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Nov 18, 2021

    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.