Need Some Serious Help in Excel Work

[vishalpatwa]

Hi,

I have been working on Medication data sheet, and found so many variations and also a lot un-aligned fields. Could someone help me to create a exact the sheet. I think, it's a very big challenge. I have sent this sheet to so many excel professionals but they have told that can only be done manually, But the has more than 12000+ rows and around 80+ column, which makes it too much hard to do the manual job. I have attached a sample sheet with manually solved. I am mentioning some brief of the task which needs to done in the formula or in the code for a better understanding.

The Sheet has two types of Products Configurable & Simple Which can be checked from the column D. if Product type is simple then it is a simple product and has only child's value. If the Product type is configurable then it is a Parent Medicine which has some child according to Strength and Quantity.
The Sheet has two types of medication Brand & Generic, & every Medication has it's own child version according to the strength and unit. For Example If a medicine XYZ is a Brand or Generic then it has some variations of the strength (1mg/2mg/3mg or more or random which is provided by Manufacturer or depends upon the units of the medicine ) & every strength has some variable of Quantity(30/60/90 or more or a random value depends upon the units).

Units: 

• %
• Dose
• gm
• IU
• mcg
• mg
• mg(10meq)
• ml
• w/v
• v/w

If the product is Brand then for the shorted sheet's product name will be picked from the AS column having value categoryname=Name(SaltName) then product name will be Name & the alternate name for the shorted sheet will also be picked from the AS column categoryname=Name(SaltName) then the alternate name will be SaltName.
If the product is Generic then for the shorted sheet's product name will be picked from the AS column having value categoryname=Name(SaltName) then product name will be SaltName & the alternate name for the shorted sheet will also be picked from the AS column categoryname=Name(SaltName) then the alternate name will be Name.

Identify Generic and Brand from the AS column medicine_type=BRAND or medicine_type=GENERIC
strength will be picked from the AS column from strength={value mentioned}strength unit will be picked from the AS column from unit={mentioned value's unit}
Quantity will be picked from column AS of rows having simple product type (As mentioned for the column D).Quantity Unit will be picked from column AS of rows having simple Product type (As mentioned for the column D) from
capsule={value mentioned} or tablet={value mentioned}.

Drug Type will be picked from the AS column medicine_type=BRAND or medicine_type=GENERIC

price will be picked from the column N

For more please refer to the attached sample sheet.

Problem & Solution Sheet Medi.xlsx

if needed more information please reply I'll provide.
Thanks in advance.  

[HansV]

Welcome to Eileen's Lounge!

That is a tall order! If you get assistance, it might take time.

[HansV]

Cell AS29 has

categoryname=Vasotec,...

This does not conform to your description of categoryname=Name(SaltName): there is no name between parentheses.

[vishalpatwa]

Cell AS29 has

categoryname=Vasotec,...

This does not conform to your description of categoryname=Name(SaltName): there is no name between parentheses.

Hi Hans, Thank you for taking your time,

Yes, it is not defining my description, I feel sorry to forgot mentioning, if it cannot be extracted from their then we need extract it from either SKU column A or column CF.

Also it stands for Quantity and Quantity Unit, these fields can be extracted from SKU column CF.

Also please notify, is it possible to make it by the formula or macros? it will be push me up for doing some research in macros and formula to do the tasks.

Again Thank you so much.

[HansV]

See the attached workbook (now a macro-enabled workbook).
If you press Alt+F8, you should see a macro named TransformData.
You can run it by clicking Run, and you can view the code by clicking Edit.
The result is in the worksheet named Output.

Problem & Solution Sheet Medi.xlsm

[vishalpatwa]

See the attached workbook (now a macro-enabled workbook).
If you press Alt+F8, you should see a macro named TransformData.
You can run it by clicking Run, and you can view the code by clicking Edit.
The result is in the worksheet named Output.


Problem & Solution Sheet Medi.xlsm

Hi HansV, Thanks a lot it works for me for capsule and tablet only but the sheet on which I'm working now has more quantity unit. is it possible for all the quantity units which is listed below.

• capsule
• pills
• cream
• doses
• gm
• gms
• inhaler
• injection
• ml
• mls
• nasal ointment
• nasal spray
• ointment
• rotacapsule
• sachets
• tablet
• tube
• vials

And have one more query also, can we just take top two quantities* in the output sheet?

Top two quantity means, if medicine has 3 or more values in quantity like 30,60,90,180, and 270 then we will put 180 and 270 value in the output sheet all other value will be neglected, same as if the sheet had 100,200, and 300 then output sheet should keep only 200 and 300 values. And if the sheet has only one value then output sheet should have only one value. for an exam you can check the provided attachment, also attaching it again for ease.

Problem & Solution Sheet Medi.xlsx

I really feel sorry for disturbing you. And Thank you for creating such a great forum. I feel thankful that I have found this forum.

I'm an SEO & PPC Expert, please inform me if I can provide some help in my expertise area.

Thank You again.

[HansV]

Handling those extra quantity units is doable, but getting the top 2 is a bit much.
Here is the modified macro:

Code: Select all

Sub TransformData()
    Dim ws As Worksheet
    Dim wt As Worksheet
    Dim s As Long
    Dim m As Long
    Dim t As Long
    Dim arrs
    Dim arrt
    Dim arrp() As String
    Dim txt As String
    Dim typ As String
    Dim cat As String
    Dim p As Long
    Dim nm As String
    Dim altnm As String
    Dim tmp
    Dim strength As String
    Dim strengthunit As String
    Dim qty As String
    Dim qtyunit As String
    Dim v
    Application.ScreenUpdating = False
    Set ws = Worksheets("Need to Short the data")
    Set wt = Worksheets("Output")
    wt.Range("A2:H" & Rows.Count).Clear
    m = ws.Range("A" & Rows.Count).End(xlUp).Row
    arrs = ws.Range("A1:AS" & m).Value
    arrt = wt.Range("A1:H" & m).Value
    s = m
    t = m + 1
    Do
        If arrs(s, 1) Like "config_*" Then
            txt = arrs(s, 45)
            arrp = Split(txt, ",")
            tmp = Filter(arrp, "medicine_type=")
            typ = Split(tmp(0), "=")(1)
            cat = Split(arrp(0), "=")(1)
            p = InStr(cat, "(")
            If p > 0 Then
                If typ = "GENERIC" Then
                    nm = Mid(cat, p + 1, Len(cat) - p - 1)
                    altnm = Left(cat, p - 1)
                Else
                    nm = Left(cat, p - 1)
                    altnm = Mid(cat, p + 1, Len(cat) - p - 1)
                End If
            Else
                If typ = "GENERIC" Then
                    nm = Split(arrs(s, 1), "_")(1)
                    altnm = cat
                Else
                    nm = cat
                    altnm = Split(arrs(s, 1), "_")(1)
                End If
            End If
            tmp = Filter(arrp, "strength=")
            strength = Split(tmp(0), "=")(1)
            tmp = Filter(arrp, "unit=")
            strengthunit = Replace(Split(tmp(0), "=")(1), strength, "")
            Do While Not arrs(s - 1, 1) Like "config_*"
                s = s - 1
                t = t - 1
                arrt(t, 1) = nm
                arrt(t, 2) = altnm
                arrt(t, 3) = strength
                arrt(t, 4) = strengthunit
                txt = arrs(s, 45)
                arrp = Split(txt, ",")
                For Each v In Array("capsule", "pills", "cream", "doses", "gms", "gm", _
                        "inhaler", "injection", "mls", "ml", "nasal ointment", "nasal spray", _
                        "ointment", "rotacapsule", "sachets", "tablet", "tube", "vials")
                    tmp = Filter(arrp, v & "=", , vbTextCompare)
                    If UBound(tmp) > -1 Then
                        tmp = Split(tmp(0), "=")
                        qty = tmp(1)
                        qtyunit = tmp(0)
                        arrt(t, 5) = qty
                        arrt(t, 6) = qtyunit
                        Exit For
                    End If
                Next v
                arrt(t, 7) = typ
                arrt(t, 8) = arrs(s, 14)
                If s = 2 Then Exit Do
            Loop
            s = s - 1
        End If
    Loop Until s = 1
    wt.Range("A1:H" & m).Value = arrt
    If t > 2 Then
        wt.Range("A2:A" & t - 1).EntireRow.Delete
    End If
    wt.Range("N2:N" & m - t + 2).NumberFormat = "0.00"
    Application.ScreenUpdating = True
End Sub

[vishalpatwa]

Handling those extra quantity units is doable, but getting the top 2 is a bit much.
Here is the modified macro:

Hi HansV, Thank you for replying me again. I checking this code on my sheet. Also As you mentioned "getting the top 2 is a bit much", can you please clarify that is it possible to do this task? or not. then if it is not possible to this kind of task, surely I'll not look for the answer for this.

And Thank You very much, you have saved my at least 2 months of work.

[HansV]

It would be possible, but it would take more time than I want to/can spend on this.

[vishalpatwa]

It would be possible, but it would take more time than I want to/can spend on this.

Thank You HansV. I really appreciate your help, it saved me for doing the manual tasks.