Hi,
@mathetes @Hans Vogelaar
excel, need to find some query from a cell and copy them to column in new sheet. Also need to align rows firstly Parent and Secondly Child.
Parent is Configurable rows having value config in column D, and if it is simple then it's a child the product.
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
Sample Sheet: Sheet file
created a forum post for the same can be checked Check Now
Thanks in advance. :thankyou:
