July 20, 2020
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....
July 16, 2010
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
July 16, 2010
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
July 20, 2020
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.
July 16, 2010
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
July 20, 2020
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.
July 16, 2010
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.
1 Guest(s)