Intelligent autofill based on cell values.

dzhogov

New Member
Joined
Apr 1, 2019
Messages
11
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!
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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
Joined
Dec 3, 2007
Messages
1,092
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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
@ Jan Karel

Extremely honored to cross your path ...

A great Fan of Yours ....
:)

Uitstekende dag ...!!!
 

jkpieterse

Well-known Member
Joined
Dec 3, 2007
Messages
1,092

ADVERTISEMENT

@James006
The pleasure is mine I'm sure!
 
Last edited:

dzhogov

New Member
Joined
Apr 1, 2019
Messages
11
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
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Apr 1, 2019
Messages
11
Indeed, the solution was way simpler than I had anticipated. Thank you to everyone, especially jkpieterse.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

jkpieterse

Well-known Member
Joined
Dec 3, 2007
Messages
1,092
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,947
Messages
5,621,773
Members
415,856
Latest member
jimb2k

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top