Automatically populate a column based on values in another column (VBA)

H

hanmas

New Member
Joined
Feb 19, 2012
Messages
24
Hello again,
I need to automatically populate a column with labels based on values in another column, e.g. automatically insert "Apple" in the Label column if the product name contains "aa", "Banana" in the Label column if the product name contains "bb", etc as below:

HTML: 
Date	        Product	       Label
2012/02/07	TextaaText	Apple
2012/02/07	TextbbText	Banana
2012/02/07	bbTextText	Banana
2012/02/07	TextccText	Cherry
2012/02/07	TextTextaa	Apple
2012/02/07	TextTextcc	Cherry

How would I go about doing this in vba? (It needs to be automated as there are 30,000+ rows in the table and 30+ criteria for the labels)
Thanks again for your help :)
Graham
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Hi Graham,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>

An approach would be to have a table of all the codes against their values, like below<o:p></o:p>

<TABLE style="WIDTH: 129pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=172><COLGROUP><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 896" width=28><COL style="WIDTH: 54pt" span=2 width=72><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: gray; WIDTH: 21pt; HEIGHT: 11.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_3057219 class=xl66 height=15 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: white 0px; BACKGROUND-COLOR: gray; WIDTH: 54pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=72>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: white 0px; BACKGROUND-COLOR: gray; WIDTH: 54pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=72>B</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: gray; HEIGHT: 11.25pt; BORDER-TOP: white 0px; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=15 align=right>1 </TD><TD style="BORDER-BOTTOM: black 0px; BORDER-LEFT: black 0px; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0px; BORDER-RIGHT: black 0px" class=xl65>aa</TD><TD style="BORDER-BOTTOM: black 0px; BORDER-LEFT: black 0px; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0px; BORDER-RIGHT: black 0px" class=xl65>Apple</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: gray; HEIGHT: 11.25pt; BORDER-TOP: white 0px; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=15 align=right>2 </TD><TD style="BORDER-BOTTOM: black 0px; BORDER-LEFT: black 0px; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0px; BORDER-RIGHT: black 0px" class=xl65>bb</TD><TD style="BORDER-BOTTOM: black 0px; BORDER-LEFT: black 0px; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0px; BORDER-RIGHT: black 0px" class=xl65>Banana</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: gray; HEIGHT: 11.25pt; BORDER-TOP: white 0px; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=15 align=right>3 </TD><TD style="BORDER-BOTTOM: black 0px; BORDER-LEFT: black 0px; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0px; BORDER-RIGHT: black 0px" class=xl65>cc</TD><TD style="BORDER-BOTTOM: black 0px; BORDER-LEFT: black 0px; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0px; BORDER-RIGHT: black 0px" class=xl65>Cherry</TD></TR></TBODY></TABLE>
Then enter the below formula in the first row of Label column and copy downward.<o:p></o:p>

{=VLOOKUP(MID(D8,MAX(IFERROR(SEARCH($A$1:$A$3,D8),0)),2),$A$1:$B$3,2)}<o:p></o:p>

Please note that {} should not be typed in, press Ctrl+Shift+Enter instead after entering the formula.

Note: D8=The value under column Product.
 
Upvote 0
Here is one way. I assume that the column B values couldn't contain, say, "aa" and "cc".

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Add_Labels()<br>    <SPAN style="color:#00007F">Dim</SPAN> Bits<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> Lbls <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "aa,Apple,bb,Banana,cc,Cherry" <SPAN style="color:#007F00">'<- Add more as required</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Bits = Split(Lbls, ",")<br>    <SPAN style="color:#00007F">With</SPAN> Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)<br>        <SPAN style="color:#00007F">For</SPAN> i = 0 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(Bits) <SPAN style="color:#00007F">Step</SPAN> 2<br>            .AutoFilter Field:=1, Criteria1:="*" & Bits(i) & "*"<br>            .Offset(, 1).SpecialCells(xlVisible).Value = Bits(i + 1)<br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        .AutoFilter<br>        .Offset(, 1).Cells(1).Value = "Labels"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Last edited:
Upvote 0
Great, thank you very much Mohammed! This works perfectly as a manual solution...now I just need to find a way to automate it in an efficient way. Any tips greatly appreciated...
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Worksheet Event adding and removing Data Validation based on multiple Conditions
Replies
0
Views
301
Nathan Asius
N
M
  • Question
VBA hides/unhides columns based upon pivot table and value
Replies
0
Views
180
mapatton
M
I
  • Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
420
Insurance Dave
I
N
  • Solved
Formula to change font color in part of cell only
Replies
2
Views
436
Nathan Asius
N
L
  • Solved
Help with formula Number serial based on criteria monthly
Replies
4
Views
185
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,216,489
Messages
6,130,959
Members
449,608
Latest member
jacobmudombe

Share this page

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
Back
Top