Forum Discussion
Use a macro to autofill a column until the end of the data in another row
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).
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.
Charla74 Thank you for the reply.
I'm trying to create a macro that will automate adding the category data in cell A1, A42, A60 and so on to each of the products listed. I've included an example of what the spreadsheet should look like complete. Any thoughts on how to approach this problem?
Thank you!
~Ryan
RyanRR1983 I suspect that your next step in this process is to perform some sort of analysis on Cost, Qty and Price per Product Number across different Categories. If so, there is no need to keep the category headers and empty rows between the groups of product numbers.
If my suspicion is correct, a relatively simple Get&Transform Data (a.k.a.PowerQuery or PQ) operation will fill down the Category to each relevant product record and create one long list that can be analysed, grouped, sorted, filtered however you want it, e.g. in a Pivot Table. Sheet2 in the attached workbook contains the end result of the PQ. I dare to say that PQ is easier to learn and maintain than VBA, in case you are new to both.
Riny_van_EekelenThank you for the help! You are correct - I'm getting the data and cleaning it up so it's ready for analysis.
To be honest, I'm not familiar with the Get&Transform Data (a.k.a.PowerQuery or PQ) operation. How did you use it to create Sheet 2 in the attached? Any help you can provide would be great. Thank you so much!
Best,
Ryan
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).