My task in a trade database agency to maintain business data, the format of business listing is membership no in column A, and business details in Column B. I wants to split or transpose data in a such way that data in cell B3 should be transfer to Cell C3, B4 should be transfer to Cell D4 and B5 should be transfer to Cell E5 which can be visible in Transposed Data. The Membership Nos are also listed along side of business details. A sample sheet is attached.
Re: Transpose data from single column to multiple columns
maybe something like this with Power Query
Membership No Business Detail.1 Business Detail.2 Business Detail.3 Business Detail.4 Business Detail.5 Business Detail.6 M-1 Syed Hamid Ilyas Welfare Trust, 051-895632 M-2 Muhammad Ishfaq memon coporations 0333-52642563 051-2536123 M-3 Adeel Shahzad AH Agri Tech 0300-5151124 Roadhan Aurf Orang Aslam Khan 0336-4592122 M-4 Rana Osman Khan Heaven Growers (Pvt) Mr.Gul Nawaz 051-5281791 051-4525369
// Table1 let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Grp = Table.Group(Source, {"Membership No"}, {{"All", each _, type table}}), List = Table.AddColumn(Grp, "Business Detail", each [All][Business Details]), Ext = Table.TransformColumns(List, {"Business Detail", each Text.Combine(List.Transform(_, Text.From), "="), type text}), Split = Table.SplitColumn(Ext, "Business Detail", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Business Detail.1", "Business Detail.2", "Business Detail.3", "Business Detail.4", "Business Detail.5", "Business Detail.6"}) in Split
Last edited by sandy666; 05-02-2023 at 11:14 AM. Reason: correction
can you please guide me how to apply power query
Are you the same person as asad.ermo? The question you ask is almost identical to one asked by that user a day or two ago.
What is the logic for Aslam Khan's data starting on a new line, when he and Adeel Shahzad are both code M-3?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Data provided in sample sheet is for reference purpose only, actual data is different but the format is same.
No, I am different from asad.ermo,
Adeel Shahzad and Aslam Khan's are partners of the same company their phone numbers are also shown.
Kindly do NOT ignore my question.
please refer to previous answer
to whom are you talking to?
use username in your answers
Please answer the questions asked in post #3.
No further help can be offered until you do.
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
OK. I will accept that.
However, I do not understand your logic. Why is this formula solution INCORRECT???
=IFERROR(IF(COUNTIF($A$3:$A3,$A3)=1,INDEX($B:$B,AGGREGATE(15,6,ROW($B$3:$B$20)/($A$3:$A$20=$A3),COLUMNS($C3:C3))),""),"")
Re: Transpose data from single column to multiple columns
alternative to my previous post it could be this
Membership No Number Business Details.1 Business Details.2 Business Details.3 M-1 051-895632 Syed Hamid Ilyas Welfare Trust, M-2 0333-52642563 Muhammad Ishfaq memon coporations M-2 051-2536123 memon coporations M-3 0300-5151124 Adeel Shahzad AH Agri Tech M-3 0336-4592122 Roadhan Aurf Orang Aslam Khan M-4 051-5281791 Rana Osman Khan Heaven Growers (Pvt) Mr.Gul Nawaz M-4 051-4525369 Mr.Gul Nawaz
Last edited by sandy666; 05-02-2023 at 01:14 PM.
Power Querylet Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content], Group = Table.Group(Source, {"Membership No"}, {{"Business Details", each {_[#"Membership No"]{0}} & _[#"Business Details"]}}), Custom1 = Table.Transpose(Table.FromColumns(Group[#"Business Details"])), #"Renamed Columns" = Table.RenameColumns(Custom1,{{"Column1", "Membership No"}, {"Column2", "1"}, {"Column3", "2"}, {"Column4", "3"}, {"Column5", "4"}, {"Column6", "5"}, {"Column7", "6"}, {"Column8", "7"}}) in #"Renamed Columns"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks