Forum Discussion

RyanRR1983's avatar
RyanRR1983
Copper Contributor
Sep 24, 2020
Solved

Use a macro to autofill a column until the end of the data in another row

Hello,   I'm consolidating category data ahead of the holidays and it's taking a lot of time to do it manually. Basically, I need some help with the following problem:   The Macro should select...
Macros and VBA
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Sep 28, 2020

    RyanRR1983 The attached link takes you to, what I considered, a great help in learning PQ. As suggested in this blog, PQ has a bit of a learning curve, but once you get over that it will add a tremendous "toolbox" to Excel.

     

    If you follow the first steps, (especially "Importing data from tables") you'll learn how to navigate to the query in the file I uploaded and look at the applied steps. In summary, the query loads the original data, creates a column with the category headers only and gets rid of empty rows. Then, it loads the transformed table back to Excel (that's what you find in Sheet2).

     

     

Charla74's avatar
Charla74
Iron Contributor
Sep 25, 2020

Hi RyanRR1983 

 

If I understand correctly, you want to duplicate values from column A to column E, right?  If so, you can use the following formula from cell E7 down:

 

=IF(ISNUMBER($A7),$A7,"")

 

If you wanted a calculation rather than duplication, the following would be an example of that:

 

=IF(ISNUMBER($A7),$A7*$C7,"")

 

Hope this helps.

Resources