Forum Discussion
Formula to transpose one column into two
I’m not sure what the best formula is to transpose where for example I have one column for two categories combined . For example food category and food needs to be split into two, a vlookup mapping won’t work as there are duplicate categories . I want it to be able to look like column D&E? Any suggestions ?
4 Replies
Mellisa1260 With PowerQuery (PQ) this would be a piece of cake, using "Fill Up", once you have established which of the words in column A represent Foods and which are the Categories. PowerQuery is only available for the Windows version of Excel. I'm on a Mac right now and, thus, can't demonstrate it. But if you don't mind to add some helper columns, you can fairly easily achieve the outcome you described without PQ.
First, I'm matching the "words" in column A with a list of known Categories (column B), based on a lookup table. Then, I count how many rows in column B, from the first row to the current row, contain a value not equal to zero (column C) and add 1. That gives the index for the Category name in the lookup table. You may hide columns B and C and filter out the blanks from column D to be left with a table of Foods and the Category they belong to.
- Riny_van_Eekelen thanks that kinda makes sense but I think where there is thousands of rows I don’t think that with work. Maybe the power query you mentioned might be my answer . Just not sure how to do it
Mellisa1260 See attached workbook that now contains a PQ solution. It will not be a problem to apply this on thousands of rows.
https://exceloffthegrid.com/power-query-introduction/
The above link could be a good starting point if you are new to PowerQuery.
- A and E columns are the same, unfortunately I do not understand what exactly you want to accomplish.
Something more detailed would help, even for those who have to do a translation in advance.
Thank you for your understanding and patience
NikolinoDE
