Forum Discussion

River_998's avatar
River_998
Copper Contributor
Sep 02, 2023
Solved

Automatically move Excel rows up or down for certain columns so that all data of rows align well

Hello. I downloaded sets of food data (one Excel file for each type of food) from https://food-nutrition.canada.ca/cnf-fce/newSearch, copied their columns, and pasted them into one Excel spreadsheet. In my sample pictures below, there is rice, chicken, and milk, as well as the component (e.g. nutrient) amounts in each food item. At the top of the sheet, each component is in its own row. As you scroll down the sheet, the other components do not align well. 

 

Is there a macro or some other method that would automatically move Excel rows up or down so that all of the data of rows align well? For example, I want all of the caffeine data (name, unit, and amount) to be in the same row for each type of food. If each component is in its own row, then it would be easier for me to calculations on the right side of the sheet (e.g. use column K to add column C, F, and J amounts together to determine total intake of caffeine).

 

Note: 

  • I have Microsoft Excel 2021 on my laptop (Windows 10).
  • Examples of food components include "22:6n-3 (DHA)" (a fatty acid) and "Fatty acids, saturated, total".
  • Each type of food has one or more columns of component amounts (e.g. I have component amounts in 100 grams and 258 grams of milk in my picture below). If necessary, I could try to have only one component amount column for each type of food (e.g. keep 100 g milk and delete 258 g milk).
  • I know how to move rows manually, but it's a slow process.
  • If helpful, I could email my sample Excel file to you.

 

Thank you!

 

Top of Excel sheet

 

 

 

Middle of Excel sheet

  • River_998 This is really something for Power Query (PQ). I downloaded the three nutrition reports you showed in the screenshot. Saved them in one folder and connected PQ to that folder. Did some basic cleaning up and created a pivot table that automatically lines up all nutrients for you. The end result (just a portion of it) looks like this:

    It that what had in mind?

     

3 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    River_998 This is really something for Power Query (PQ). I downloaded the three nutrition reports you showed in the screenshot. Saved them in one folder and connected PQ to that folder. Did some basic cleaning up and created a pivot table that automatically lines up all nutrients for you. The end result (just a portion of it) looks like this:

    It that what had in mind?

     

    • River_998's avatar
      River_998
      Copper Contributor

      Riny_van_Eekelen - Thank you. I think that your method would be helpful. What specific steps did you do in this method?

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor

        River_998 It requires Power Query. Not difficult but it might take an effort to learn it if you have never used it. 

Resources