Intelligent autofill based on cell values.

dzhogov

New Member
Hello,

I have built a sales report dashboard which is to be updated monthly with around 2000 rows of input. The data I input has the following categories/columns:

Date | Sales rep | Product | Quantity | Value | Product category | Brand.

Sadly, the company's software does not export the product category and brand automatically, so I need to input them manually. There are around 140 products, 12 categories and 3 brands. Each month, I add the new rows of data, sort the data by Product name so that it's visually obvious which product is in which category and brand, and then I autofill by dragging down the correct category and brand. As the data sheet grows, this takes more and more time.

I am looking for a suggestion of how I should go about creating an intelligent autofill feature for these columns, with each category and brand corresponding to a group of products.

Thanks!
 

James006

Well-known Member
Hello,

As long as you are maintaining an up-to-date validation worksheet with your products/categories/brands database ...

a simple macro will execute your ' intelligent autofill ' ...

Hope this will help
 

jkpieterse

Well-known Member
Can you perhaps post a small sample of your data; please show a sample of what it looks like just before you do the autofill and a sample of the same data after your autofill work. Just a couple of rows of data suffices.

If I understand correctly, what may help in the autofilling process (this assumes row 1 in your table does not contain empty cells):

- Select all data after doing your additions, but before autofilling
- Press F5, click Special
- Select Blank cells
- press the = sign
- press the up arrow once.
- hold the control key and press enter
- Now select all data again
- Copy
- Paste special, values.
 

dzhogov

New Member
Thank you for your quick reply, and apologies for not giving a sample of the data in the first place.

Let's say I have to enter the data for March 2019 below the data for February 2019, which has already been filled in last month as follows:

Month:Product:Sales Rep:Quantity:Value:Category:Brand:
feb.19Fillet stakeJohn5.000100.00BonelessDimmy's
feb.19Fillet stakeJohn20.000400.00BonelessDimmy's
feb.19WingsSammy14.00028.00W/ boneKFC
feb.19WingsPeter10.00020.00W/ boneKFC
feb.19Cheese bitesPeter50.00050.00DairyTasties

<tbody>
</tbody>

Once I add the data from March and sort it by product name, I get something like this:

Month:Product:Sales Rep:Quantity:Value:Category:Brand:
feb.19Fillet stakeJohn5.000100.00BonelessDimmy's
feb.19Fillet stakeJohn20.000400.00BonelessDimmy's
mar.19Fillet stakeJohn17.000340.00
mar.19Fillet stakeSammy5.000100.00
feb.19WingsSammy14.00028.00W/ boneKFC
mar.19WingsSammy1.0002.00
feb.19WingsPeter10.00020.00W/ boneKFC
feb.19Cheese bitesPeter50.00050.00DairyTasties

<tbody>
</tbody>


What I need is to quickly fill the Category and Brand values based on the value in the Product column. Should I look into a macro that checks the name of each product and assigns it onto a category and brand?

Regards!
 

Fluff

MrExcel MVP, Moderator
If you want to fill the blank cells, with the data from the row above, simply follow the instructions from jkpieterse in post#3.
 

dzhogov

New Member
Indeed, the solution was way simpler than I had anticipated. Thank you to everyone, especially jkpieterse.
 

Fluff

MrExcel MVP, Moderator
Glad we could help & thanks for the feedback
 

jkpieterse

Well-known Member
Glad this has solved your issue. Please note though if you add an entirely new product category and/or brand you'll have to manually enter the appropriate information in those columns, otherwise it'll end up with the same details as whatever happens to be entered in the row above that new item.
 

Some videos you may like

This Week's Hot Topics

Top