Automatic grouping

[cikiwik]

Hello there,

Need Help from all of Excel Guru here to simplify my work in excel.

I have a raw data like this

Name	Address	city	age	sex	region
Ditu	aaaaaa	Jakarta	12	m	Others
Asia	bbbbbb	Medan	14	f	Others
Goson	cccccc	Bogor	16	m	Others
Istama	ddddd	Bandung	18	f	Others
Jaya	eeeee	Bekasi	20	m	Others

<tbody>
</tbody>

I want to utilized the "region" Column to grouping the city data by adding information in region so the result will be like this

Name	Address	city	age	sex	region
Ditu	aaaaaa	Jakarta	12	m	JaBek
Asia	bbbbbb	Medan	14	f	SumUt
Goson	cccccc	Bogor	16	m	JaBar
Istama	dddddd	Bandung	18	f	JaBar
Jaya	eeeeee	Bekasi	20	m	JaBek

<tbody>
</tbody>

Is there possible to use VBA to automatically replace "Others" in the "region" Column with value such as example above?
( Jakarta & Bekasi will be "JaBek" in "region" Column, Bogor & Bandung will be "JaBar" in "region " Column, and Medan will be "SumUt" in "region" Column)

If possible how to make the VBA code? cause I am a dummies in VBA and Excel

Thanks in advance for your Help
Regards,
CKW

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG02Jun45
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
    .Add "Jakarta", "JaBek"
    .Add "Bekasi", "JaBek"
    .Add "Bogor", "JaBar"
    .Add "Bandung", "JaBar"
    .Add "Medan", "SumUt"


[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Dn.Offset(, 3).Value = .Item(Dn.Value)
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[hiker95]

cikiwik,

Here is another macro solution for you to consider.

You can change the raw data worksheet name in the macro.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub UpdateRegion()
' hiker95, 06/02/2015, ME858692
Dim c As Range
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  For Each c In .Range("C2", .Range("C" & Rows.Count).End(xlUp))
    If c = "Jakarta" Or c = "Bekasi" Then
      c.Offset(, 3) = "JaBek"
    ElseIf c = "Bogor" Or c = "Bandung" Then
      c.Offset(, 3) = "JaBar"
    ElseIf c = "Medan" Then
      c.Offset(, 3) = "SumUt"
    End If
  Next c
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the UpdateRegion macro.