Oct 08 2020 09:12 AM
Basically, the problem is that in the excel report that comes from the form, every person's order goes into one cell:
Meanwhile, this is how we need that data:
At the moment, we're putting the column with all the orders into our already filled out template with all the product columns as in the 2nd photo, then using this formula to copy that data into the next column: =SUBSTITUTE(A2,CHAR(10),"^")
And then we do a text to columns, delimited, with tab and ^
But that only gets us here:
And I still have to spend hours inserting cells to move the data to the right column and then find/replacing text with nothing so we can just have the amount ordered data in each cell.
So my question is does anyone know of a formula or macros or something that will make this easier for our team?
Thanks in advance!
Adrienne
Oct 08 2020 01:56 PM
That's job for Power Query if you consider it as an option.
Oct 14 2020 01:01 PM
Oct 14 2020 02:28 PM
That's better to explain on sample file, otherwise it'll be abstract repeating of books.