I have an excel sheet that contains registrations list of our event. Depending upon the country you are registering from the length of the form gets adjusted. Bcs of this reason when I created a list of final registration, data seems to be jumbled up a bit.
For eg: C stands for Column and R stands for Rows... Under Column 1 (First Name), in Rows 1-3 I have First Names. But from 4 - 6, it’s Email, Reg_subjectline and Apps. There are at least 20 Columns and in each Columns data is arranged in this manner but they are not equally sequenced in rows. My requirement is to get only emails in one single column C1, first names in C2, phone in C3, for all registrants... any formula that u can recommend wud be really helpful....
Hi Jithesh,
Welcome to our forum!
Presumably when someone enters their name they don't prefix it with 'First Name' and 'Last Name' etc. so how would Excel be able to identify which cell contains which field?
I would go back to the form and fix it so that the data is collated correctly. Have you tried Microsoft Forms? It doesn't suffer from this problem.
Mynda
@Mynda the form is capturing this data correctly and I get notification on outlook for each registrations. Im trying to extract the registrations from Outlook notifications to excel. Thats when I am loosing the structure of the data.
Ok, now I have more questions and one still unanswered:
1. Where is the form putting the data? Surely it's not only emailing it to you. Isn't it putting it into a table somewhere?
2. You haven't explained how you're extracting the data from Outlook and why this results in you losing the structure of the data.
3. You didn't answer how Excel would be able to identify which cell contains which field.
Mynda
1. Where is the form putting the data? Surely it's not only emailing it to you. Isn't it putting it into a table somewhere?
Ans: It's only available in email and not available anywhere else
2. You haven't explained how you're extracting the data from Outlook and why this results in you losing the structure of the data.
Ans: when i receive it, data is not structured in table.
3. You didn't answer how Excel would be able to identify which cell contains which field.
Ans: If you have opened my file, you would know each of the cell value has its respective column title at the beginning of the text.
There's no need to be rude, Jithesh. I did open your file...how do you think I knew that the names were prefixed with First Name, Last Name etc. in my first reply?
You haven't answered part of my second question. How are you extracting the data from Outlook?
Here's what doesn't make sense; if people are completing a form and the results are emailed to you, why aren't they emailed in the same order each time? If you're copying and pasting the data from the email to Excel, then how is it getting jumbled in a different order? I can't see how or why a form would randomly send you data in a different order.
Keep in mind that you're familiar with your process. I am not, so I'm trying to fill in the blanks based on what you tell me and my own experience of using forms. Also have faith that if there were an easy solution I would have provided it. I'm trying to find the best and easiest solution, and that requires me to understand the whole process.
Mynda
Sincere apologies if I sounded rude which i never meant to be. Happy to onboard with the extract process from Outlook to Excel.
There are at least 10 different forms and each of them are different in terms of number of fields. Some has 7 and some having 8 and some are 9. When i receive them in outlook as notifications they are jumbled up. I'm using outlook export option to extract all registrations from a outlook folder to excel csv file. When I look at data on CSV file, the entire form fields are showing up in one single cell. so i used power query to split different line items into various rows and columns. That's how the final data set became like this. Hope this clarifies.
Hi Jithesh,
Thank you for clarifying. You can use Conditional Columns to rearrange the data. It's a bit laborious to set up. See attached.
Mynda
Thanks data looks exactly how i wanted it. This is great, but can you please share steps to set it up?
Hi Jitesh,
You can see the steps in the Query Editor Applied Steps pane.
Mynda
Hi Mynda - if you have some free time, i would like to have a quick 30 mins virtual meeting to understand the steps.
I don't do live support, sorry. I used conditional columns. Perhaps it would be helpful for you to learn Power Query. I cover this technique, among many others, in my Power Query course.
Okay Thanks Mynda.
Mynda - can you also help me with a formula for trimming whitespaces in multiple columns in one go using power query?
In the Power Query editor Transform tab > Format you can use Trim to do this.
