Fill down multiple columns with the values based on groupings from another column

Attached is a pretend products export from Shopify. For certain columns, Shopify only writes the values to the first product variant record and leaves subsequent records blank (See worksheet Original). They're assumed to be the same as the first record. However, this makes it hard to work with the data since you can't filter.

ProductIDs do not get exported but instead Shopify relies on the Handle column as the unique identifier. How can I fill columns down with the value in the first record for each product, using the Handle as the means of identifying when to stop filling down that value? (See worksheet Desired) What I mean is, if you look at the Original worksheet, if we ignore the Handle, then "Sweatshirt" will fill down into the Ladies T-shirt Standard Product Type as well and that's not good. (See worksheet Undesired)

To be clear, I don't know how to do any part of this. So I'd like a script to 1) By column, fill a value down for all records with the same Handle 2) Ideally do this for multiple specified columns in the same script.

There are 32000 rows and 50 columns though I'll only need to do this for about 12 columns.

Thanks!

P.S. I have a subscription to Kutools but a) it doesn't reference a different column as a grouping mechanism and b) doesn't do multiple columns in one shot. (Took quite a few minutes just to test one column.)

Thank you! I never had the need and therefore never took the time to try Power Query.

I have a couple of questions after visiting the links you provided as well as those in your signature.

1) When I open Advanced Editor, there are a couple of transformations in there which were created(?) when I connected to the data source. Those are showing up as separate steps in the Applied Steps pane. However, when I pasted in the third line you provided (including placing a comma at the end of the preceding line), it doesn't do any FILLS, and it no longer displays the separate steps in the Applied Steps pane, instead displaying "products_export_1" (which is the filename of the original csv). What am I doing wrong? And in fact, when I use Transform-->Any Column-->Fill-->Down it also doesn't do anything.

2) I don't see where in your code you use the "Handle" column's value to stop/restart the fill. Again, the concern being that a value is filled down through its own products past a blank "header" row, straight through into another product.

Thanks!

I figured out the answer to #1. That I had to have null values not empty strings in order for fill to work. Still wondering about #2.

If any of the other suggestions does not strike your fancy, you can try this.

Please Login or Register  to view this content.

VBA

Please Login or Register  to view this content.