Intelligent autofill based on cell values.

[dzhogov]

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]

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]

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.

 

[James006]

@ Jan Karel

Extremely honored to cross your path ...

A great Fan of Yours ....

Uitstekende dag ...!!!

 

[jkpieterse]

@James006
The pleasure is mine I'm sure!

 

[dzhogov]

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:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Month:[/TD]
[TD]Product:[/TD]
[TD]Sales Rep:[/TD]
[TD]Quantity:[/TD]
[TD]Value:[/TD]
[TD]Category:[/TD]
[TD]Brand:[/TD]
[/TR]
[TR]
[TD]feb.19[/TD]
[TD]Fillet stake[/TD]
[TD]John[/TD]
[TD]5.000[/TD]
[TD]100.00[/TD]
[TD]Boneless[/TD]
[TD]Dimmy's[/TD]
[/TR]
[TR]
[TD]feb.19[/TD]
[TD]Fillet stake[/TD]
[TD]John[/TD]
[TD]20.000[/TD]
[TD]400.00[/TD]
[TD]Boneless[/TD]
[TD]Dimmy's[/TD]
[/TR]
[TR]
[TD]feb.19[/TD]
[TD]Wings[/TD]
[TD]Sammy[/TD]
[TD]14.000[/TD]
[TD]28.00[/TD]
[TD]W/ bone[/TD]
[TD]KFC[/TD]
[/TR]
[TR]
[TD]feb.19[/TD]
[TD]Wings[/TD]
[TD]Peter[/TD]
[TD]10.000[/TD]
[TD]20.00[/TD]
[TD]W/ bone[/TD]
[TD]KFC[/TD]
[/TR]
[TR]
[TD]feb.19[/TD]
[TD]Cheese bites[/TD]
[TD]Peter[/TD]
[TD]50.000[/TD]
[TD]50.00[/TD]
[TD]Dairy[/TD]
[TD]Tasties[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Month:[/TD]
[TD]Product:[/TD]
[TD]Sales Rep:[/TD]
[TD]Quantity:[/TD]
[TD]Value:[/TD]
[TD]Category:[/TD]
[TD]Brand:[/TD]
[/TR]
[TR]
[TD]feb.19[/TD]
[TD]Fillet stake[/TD]
[TD]John[/TD]
[TD]5.000[/TD]
[TD]100.00[/TD]
[TD]Boneless[/TD]
[TD]Dimmy's[/TD]
[/TR]
[TR]
[TD]feb.19[/TD]
[TD]Fillet stake[/TD]
[TD]John[/TD]
[TD]20.000[/TD]
[TD]400.00[/TD]
[TD]Boneless[/TD]
[TD]Dimmy's[/TD]
[/TR]
[TR]
[TD]mar.19[/TD]
[TD]Fillet stake[/TD]
[TD]John[/TD]
[TD]17.000[/TD]
[TD]340.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]mar.19[/TD]
[TD]Fillet stake[/TD]
[TD]Sammy[/TD]
[TD]5.000[/TD]
[TD]100.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]feb.19[/TD]
[TD]Wings[/TD]
[TD]Sammy[/TD]
[TD]14.000[/TD]
[TD]28.00[/TD]
[TD]W/ bone[/TD]
[TD]KFC[/TD]
[/TR]
[TR]
[TD]mar.19[/TD]
[TD]Wings[/TD]
[TD]Sammy[/TD]
[TD]1.000[/TD]
[TD]2.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]feb.19[/TD]
[TD]Wings[/TD]
[TD]Peter[/TD]
[TD]10.000[/TD]
[TD]20.00[/TD]
[TD]W/ bone[/TD]
[TD]KFC[/TD]
[/TR]
[TR]
[TD]feb.19[/TD]
[TD]Cheese bites[/TD]
[TD]Peter[/TD]
[TD]50.000[/TD]
[TD]50.00[/TD]
[TD]Dairy[/TD]
[TD]Tasties
[/TD]
[/TR]
</tbody>[/TABLE]


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]

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]

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

 

[Fluff]

Glad we could help & thanks for the feedback

 

[jkpieterse]

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.