Looking for a way to transpose data that is currently in the below format;
A
1
2
3
B
1
2
4
5
C
1
3
4
5
Into
A 1 2 3 X X
B 1 2 X 4 5
C 1 X 3 4 5
X=Blank cell
Please see attached an excerpt of the data.
Looking for any solution, formula or macro (my macro skills are basic).
Thank you in advanced, please let me know if more information is required.
Last edited by excelnoobthree; 11-20-2018 at 09:27 PM.
If you keep your school name all on one line, then the following code will work for you. For example, line 21 and 22 should be on the same line
Please Login or Register to view this content.
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
Try this for results on sheet2.
Regards MickPlease Login or Register to view this content.
Thank you both for your posts, I have noticed an error with my original post, the spacing didn't format correctly, the end result i am wanting is
Into
A 1 2 3 X X
B 1 2 X 4 5
C 1 X 3 4 5
X=Blank cell
Alan - The full data set is 20,000+ lines, wouldn't be able to go through and ensure there are no errors such as in lines 21&22, is there a way around this?
Mick - Your code worked really well, however in the case of cells 26-31 where the data has both an email and web sheet 2 only displays the web in E, is there a way to display email in F and leave E blank if there is no Web:?
Cheers,
EN3
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks