Hi VBA Developers,
Hope you are fine,
Please note I have been trying to create a database but in this case, I only need a column with the data, based on some parameters but during the process I am confused since the code I think should have some loops.
Probably my code is a mess, but definitely create database manually or using formulas could be painful. If someone highly skilled can help me with the code I will appreciate a lot, I just need to create the database 1 time.
Basically, there is a specific structure with a Pattern (Expected results Spoiler) but this example
"Expected Results Yellow"
Expected Column is the only required as results.
Appreciate your help on this.
Regards
Andres A
Hope you are fine,
Please note I have been trying to create a database but in this case, I only need a column with the data, based on some parameters but during the process I am confused since the code I think should have some loops.
Probably my code is a mess, but definitely create database manually or using formulas could be painful. If someone highly skilled can help me with the code I will appreciate a lot, I just need to create the database 1 time.
Basically, there is a specific structure with a Pattern (Expected results Spoiler) but this example
VBA Code:
Sub CreateHier()
With Application
'.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
Dim ws, Ts As Worksheet
Dim i, W, L, lastrow, LastR1 As Long
Dim X, Y, Z, O As String
Dim Cell As Range
Set ws = Sheets("Data")
Set Ts = Sheets("Template")
lastrow = ws.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lastrow
ws.Select
Range("A1").Select
ActiveCell.Offset(1, 0).Activate
X = ActiveCell.Offset(0, 1).Value & "_"
Y = ActiveCell.Offset(0, 2).Value & "_"
Z = ActiveCell.Offset(0, 3).Value
LastR1 = Ts.Range("C" & .Rows.Count).End(xlUp).Row + 1
Ts.Cells(LastR1, 3).Value = X & Y & Z
For W = 2 To lastrow
ActiveCell.Offset(1, 0).Activate
X = ActiveCell.Offset(0, 1).Value & "_"
Y = ActiveCell.Offset(0, 2).Value & "_"
O = ActiveCell.Offset(0, 4).Value
LastR1 = Ts.Range("A" & .Rows.Count).End(xlUp).Row + 1
Ts.Cells(LastR1, 3).Value = X & Y & O
'For Each Cell In Range("I2:I10")
For P = 2 To lastrow
ActiveCell.Select
X = ActiveCell.Offset(0, 1).Value & "_"
Y = ActiveCell.Offset(0, 2).Value & "_"
Y = ActiveCell.Offset(0, 6).Value
Ts.Cells(LastR1, 3).Value = X & Y
Next P
'Next Cell
Next W
Next i
'.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
End Sub| Country | Division | Country Cd | Total | PNL | Product Code | Product Name | Country | Company Code | ||
| Belize | GHH | BZ | TTL | PNL | OOV | Oncology Vaccine | BZ | 2200 | ||
| Costa Rica | GHH | CR | TTL | PNL | FCV | Pharmacology Vaccine | BZ | 2220 | ||
| El Salvador | GHH | SV | TTL | PNL | NEU | Neuroscience | BZ | 2240 | ||
| Guatemala | GHH | GT | TTL | PNL | PHT | Pulmonary Hypertension | BZ | 2260 | ||
| Honduras | GHH | HN | TTL | PNL | IMH | Immunology | US | 1000 | ||
| Mexico | GHH | MX | TTL | PNL | CRD | Cardiovascular | US | 1010 | ||
| Nicaragua | GHH | NI | TTL | PNL | MTB | Metabolism | US | 1020 | ||
| Panama | GHH | PA | TTL | PNL | TPA | Therapeutic | US | 1030 | ||
| Argentina | GHH | AR | TTL | PNL | NDG | Neurodegenerative Cure | CA | 1100 | ||
| Bolivia | GHH | BV | TTL | PNL | CA | 1110 | ||||
| Brazil | GHH | BR | TTL | PNL | CA | 1120 | ||||
| Chile | GHH | CH | TTL | PNL | CA | 1130 | ||||
| Colombia | GHH | CO | TTL | PNL | CR | 2000 | ||||
| Ecuador | GHH | EC | TTL | PNL | CR | 2010 | ||||
| French Guiana | GHH | FG | TTL | PNL | CR | 2020 | ||||
| Guyana | GHH | GY | TTL | PNL | CR | 2030 | ||||
| Paraguay | GHH | PG | TTL | PNL | SV | 2300 | ||||
| Peru | GHH | PE | TTL | PNL | SV | 2310 | ||||
| Suriname | GHH | SR | TTL | PNL | SV | 2320 | ||||
| Uruguay | GHH | UU | TTL | PNL | SV | 2330 | ||||
| Venezuela | GHH | VZ | TTL | PNL | GT | 2400 | ||||
| United States | GHH | US | TTL | PNL | GT | 2410 | ||||
| Canada | GHH | CA | TTL | PNL | GT | 2420 | ||||
| GT | 2430 | |||||||||
| HN | 2500 | |||||||||
| HN | 2510 | |||||||||
| HN | 2520 | |||||||||
| HN | 2530 | |||||||||
| MX | 2600 | |||||||||
| MX | 2610 | |||||||||
| MX | 2620 | |||||||||
| MX | 2630 | |||||||||
| AR | 3000 | |||||||||
| AR | 3010 | |||||||||
| AR | 3020 | |||||||||
| AR | 3030 | |||||||||
| BV | 3100 | |||||||||
| BV | 3110 | |||||||||
| BV | 3120 | |||||||||
| BV | 3130 | |||||||||
| BR | 3500 | |||||||||
| BR | 3510 | |||||||||
| BR | 3520 | |||||||||
| BR | 3530 | |||||||||
| CH | 3600 | |||||||||
| CH | 3610 | |||||||||
| CH | 3620 | |||||||||
| CH | 3630 | |||||||||
| CO | 3700 | |||||||||
| CO | 3710 | |||||||||
| CO | 3720 | |||||||||
| CO | 3730 | |||||||||
| EC | 3800 | |||||||||
| EC | 3810 | |||||||||
| EC | 3820 | |||||||||
| EC | 3830 | |||||||||
| FG | 3200 | |||||||||
| FG | 3210 | |||||||||
| FG | 3220 | |||||||||
| FG | 3230 | |||||||||
| GY | 3300 | |||||||||
| GY | 3310 | |||||||||
| GY | 3320 | |||||||||
| GY | 3330 | |||||||||
| PG | 3900 | |||||||||
| PG | 3910 | |||||||||
| PG | 3920 | |||||||||
| PG | 3930 | |||||||||
| PE | 4000 | |||||||||
| PE | 4010 | |||||||||
| PE | 4020 | |||||||||
| PE | 4030 | |||||||||
| SR | 4100 | |||||||||
| SR | 4110 | |||||||||
| SR | 4120 | |||||||||
| SR | 4130 | |||||||||
| UU | 4200 | |||||||||
| UU | 4210 | |||||||||
| UU | 4220 | |||||||||
| UU | 4230 | |||||||||
| VZ | 4300 | |||||||||
| VZ | 4310 | |||||||||
| VZ | 4320 | |||||||||
| VZ | 4330 |
"Expected Results Yellow"
| Expected Results | ||||
| GHH_BZ_TTL | ||||
| GHH_BZ_OOC_PNL | Numbers Code is a mix Between company code based on country code (Data) and those code below as 1001 , 1002 etc | |||
| GHH_BZ_OOC | Dynamic | Default | ||
| 22001001 | 2200 | 1001 | ||
| 22201001 | 2220 | 1001 | ||
| 22401001 | 2240 | 1001 | ||
| 22601001 | 2260 | 1001 | ||
| GHH_BZ_OOV_PNL | ||||
| GHH_BZ_OOV | ||||
| 22001002 | 2200 | 1002 | ||
| 22201002 | 2220 | 1002 | ||
| 22401002 | 2240 | 1002 | ||
| 22601002 | 2260 | 1002 | ||
| GHH_BZ_FCV_PNL | ||||
| GHH_BZ_FCV | ||||
| 22001003 | 2200 | 1003 | ||
| 22201003 | 2220 | 1003 | ||
| 22401003 | 2240 | 1003 | ||
| 22601003 | 2260 | 1003 | ||
| GHH_BZ_FCV_NEU | ||||
| GHH_BZ_NEU | ||||
| 22001004 | 2200 | 1004 | ||
| 22201004 | 2220 | 1004 | ||
| 22401004 | 2240 | 1004 | ||
| 22601004 | 2260 | 1004 | ||
| GHH_BZ_FCV_PHT | ||||
| GHH_BZ_PHT | ||||
| 22001005 | 2200 | 1005 | ||
| 22201005 | 2220 | 1005 | ||
| 22401005 | 2240 | 1005 | ||
| 22601005 | 2260 | 1005 | ||
| GHH_BZ_IMH_PHT | ||||
| GHH_BZ_IMH | ||||
| 22001006 | 2200 | 1006 | ||
| 22201006 | 2220 | 1006 | ||
| 22401006 | 2240 | 1006 | ||
| 22601006 | 2260 | 1006 | ||
| GHH_BZ_CRD_PHT | ||||
| GHH_BZ_CRD | ||||
| 22001007 | 2200 | 1007 | ||
| 22201007 | 2220 | 1007 | ||
| 22401007 | 2240 | 1007 | ||
| 22601007 | 2260 | 1007 | ||
| GHH_BZ_MTB_PNL | ||||
| GHH_BZ_MTB | ||||
| 22001008 | 2200 | 1008 | ||
| 22201008 | 2220 | 1008 | ||
| 22401008 | 2240 | 1008 | ||
| 22601008 | 2260 | 1008 | ||
| GHH_BZ_TPA_PNL | ||||
| GHH_BZ_TPA | ||||
| 22001009 | 2200 | 1009 | ||
| 22201009 | 2220 | 1009 | ||
| 22401009 | 2240 | 1009 | ||
| 22601009 | 2260 | 1009 | ||
| GHH_BZ_NDG_PNL | ||||
| GHH_BZ_NDG | ||||
| 22001010 | 2200 | 1010 | ||
| 22201010 | 2220 | 1010 | ||
| 22401010 | 2240 | 1010 | ||
| 22601010 | 2260 | 1010 |
Expected Column is the only required as results.
Appreciate your help on this.
Regards
Andres A
Last edited by a moderator:
