Converting columns into rows with different values
I have a csv file with 16 columns.
The first 10 are contact information, the last 6 are different phone numbers sometimes for the same contact.
My objective is to create duplicates of the first 10 columns with the new 11th column representing one phone number from the last 6 columns.
Attaching a sample csv
Thank you
Are 6 telephone numbers ALWAYS present?
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Not always 6 but there will be at least one number in one of the columns
Power Query
Please Login or Register to view this content.
Last edited by Czeslaw; 07-04-2022 at 11:15 AM.
Not sure I follow! I've put before and after of what I'm trying to accomplish in the sample csv file
Changed from csv to xlsx
Sorry but this is not what I'm trying to accomplish
I am looking to keep all values from A to J as is in their own columns (no merging)
Value in column K should have only one phone number
Please check the updated sample2 with “before”: what the original sheet was and “after” an example of what the result should look like.
Thank you
Administrative Note:
Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
PowerQuery
M Code:
Please Login or Register to view this content.
Thank you! This is exactly what I want
I'm not a coder. Can you please advise how do I load or implement this code to my actual sheet?
Follow steps 1 and 2 in the linked article to open Power Query: http://raw.youramys.com/wiki-https-t...c/ba-p/3359795
Once opened (see step 3 in article) select the Advanced Editor.
Replace the existing code by pasting the code from AliGW's post (#9) into Advanced Editor window.
Select OK.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
I don't have power query as I'm using office for mac. Is there a formula to use directly in excel or google sheets instead of having the need to use power query?
Here is a formula-based proposal.
Populate cells Q4:Q6 using: =SUM(COUNTIFS(K4:P4,"<>"),Q3)
Populate the First Name through Mail Zip columns using: =IFERROR(INDEX(A$4:A$6,AGGREGATE(15,6,(ROW(A$4:A$6)-ROW(A$3))/($Q$4:$Q$6>=ROWS(N$15:N15)),1)),"")
Populate the Phone column using: =IFERROR(AGGREGATE(15,6,K$4:P$6/(K$4:P$6<>"")/(A$4:A$6=N15)/(B$4:B$6=O15),COUNTIFS(A$15:A15,A15)),"")
Let us know if you have any questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks