Forum Discussion
Formula to transpose one column into two
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.
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.