Converting a range of cells into one single straight down column on another sheet
Hey guys! I could use a formula/function to convert this range of cells from Guest_Info sheet F4:V1000 to Guest_name sheet (A4). Also if theres anything to add in the formula to not allow duplicate names on this list that would be amazing! Here is an example with the attached sheet! Thanks
Unpivot your data with Power Query.
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").
Please Login or Register to view this content.
It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
- Follow this link for an introduction to Power Query functionality.
- Follow this link for a video which demonstrates how to use Power Query code provided.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
FormulaFormula:
Please Login or Register to view this content.
I used the Power Query code and changed the name (Table1) to (GuestList) and I'm getting this. "DataFormat.Error: Invalid cell value '#VALUE!'."
Were you using the same file or a different one? Was the layout of your actual file exactly the same as your sample file?
I see what you're saying. So this is an actual example of some of the partial data. There is technically a couple hundred rows of names I have to put in the list. Also will this work when new names are added? I just want the registered guests 1-15 while having none of the other columns. How do I do that?
@Chandler8
This should do what you want. Post back if you need additional help.
Please Login or Register to view this content.
alansidman
Thank you so much for your help! Much appreciated. Power Query seems like it has some serious potential with what I am trying to achieve. Would it also be able to grab (when a certain name is listed what their check in/check out dates are, their sponsor names, the guest last names, their total number of days stayed, their eligibility, etc. all from the "Guest_Info" sheet?) Because the gist here is that a guest can only use the amenities for 14 days or within their first two stays regardless of if they use them or not. Would something like this be possible on power query? Thanks in advance!! The same from the data sheet above. (All the columns listed).
Yes. It is possible. If you like some assistance on that, then open a new thread and post your sample data and what you would like your results to look like and then we can give it a go.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks