I need help in creating VBA to match two different sheets. the only link between them is item description, but names are not 100% identical. I tried to do a loop that finds the description in each cell in Sheet1 and match in Sheet2 and then find the items cost of each item.
check the sample below.
<tbody>
</tbody>
<tbody>
</tbody>
check the sample below.
| Item Description | Item Cost | DEPT_MISC_LINE | |||
| PARACETAMOL|| 250 mg suppository | 0.1795 | Paracetamol 500mg Tablet T2 TAB PO Q6H DAY5 #24 | |||
| EPINEPHRINE|| 1 mg/mL (1:1,000) 1 mL injection | 1.83866 | Paracetamol 500mg Tablet T2 TAB PO Q6H DAY5 #24 | |||
| INSULIN LISPRO|| 100 units/mL injection | 83.71223 | Alginic Acid Compound Suspension T10 SUSP PO Q6H DAY7 prn hburn #200 | |||
| LIDOCAINE|| 2% gel | 5.91142 | Alginic Acid Compound Suspension T10 SUSP PO Q6H DAY7 prn hburn #200 | |||
| METOCLOPRAMIDE|| 5 mg/mL (2 mL) injection | 0.97879 | Alginic Acid Compound Suspension 20 ml po qid prn hburn day 14 #1000 | |||
| OXYTOCIN|| 5 IU/mL (1 mL) injection | 1.7989 | Paracetamol 500mg Tablet T2 TAB PO Q6H DAY5 prn pain #24 | |||
| TEICOPLANIN|| 200 mg vial (injection) | 133.2249 | Ibuprofen 400mg Tablet T1 TAB PO TIDPC DAY5 prn pinf #15 | |||
| XYLOMETAZOLINE|| 0.1% nasal spray (adult) | 4.43829 | Amoxicillin-Clavulanate 875mg-125mg Tablet 1 po pc bid q12h day 7 #14 | |||
| XYLOMETAZOLINE|| 0.05% nasal drops (pediatric) | 3.68699 | Paracetamol 500mg Tablet T2 TAB PO Q6H DAY5 #24 | |||
| KETOROLAC|| 30 mg/mL (1 mL) injection | 4.80156 | Desloratidine 5mg Tablet T1 TAB PO D DAY10 eve #10 | |||
| HETASTARCH|| 6% solution (500 mL) for infusion | 28.63 | Paracetamol 500mg Tablet T2 TAB PO Q6H DAY5 #24 | |||
| PARACETAMOL|| 500 mg tablet | 0.02887 | Ascorbic Acid (effervescent) 1000mg Tablet DD1 TAB-EFF PO D DAY14 #20 | |||
| BUPIVACAINE|| 0.5% "HEAVY" preservative-free (spinal) injection | 13.22805 | Atorvastatin 10mg Tablet T1 TAB PO HS DAY90 #90 | |||
| DICLOFENAC SODIUM|| 100 mg suppository | 1.39871 | Ergocalciferol 50,000 units Capsule T2 CAP PO Q7D DAY90 #12 | |||
| DICLOFENAC SODIUM|| 100 mg suppository | 1.39871 | Calcium Carbonate-Vitamin D 600mg-200units Tablet T1 TAB PO BID DAY90 #180 | |||
| ERGOMETRINE MALEATE SYNTHETIC OXYTOCIN|| 0.5 mg + 5I.U/mL (1 mL) injection | 14.95007 | Vildagliptin-Metformin 50mg-1000mg Tablet T1 TAB PO BID DAY90 #180 | |||
| LIDOCAINE|| 2% (50 mL) injection | 4.04046 | Pseudoephedrine-Triprolidine 60mg-2.5mg Tab T1 TAB PO Q8H DAY3 MCD #10 | |||
| LIDOCAINE|| 10% endotracheal spray | 25.37894 | Paracetamol 500mg Tablet T2 TAB PO Q6H DAY3 PRN PF #24 | |||
| OXYTOCIN|| 5 IU/mL (1 mL) injection | 1.7989 | Xylometazoline 0.1% (10mL) Nasal Spray SP1 SPRAY EN Q8H DAY3 NASCONG #1 | |||
| OXYTOCIN|| 5 IU/mL (1 mL) injection | 1.7989 | Paracetamol 500mg Tablet T2 TAB PO Q6H DAY5 #0 | |||
| PHYTOMENADIONE|| 10 mg/mL (0.2 mL) injection | 2.7549 | Paracetamol 500mg Tablet T2 TAB PO Q6H DAY5 #0 | |||
| ROCURONIUM|| 10 mg/mL (5mL) injection | 19.87276 | Paracetamol 24mg/mL Suspension 8 ML PO Q4H PRN PF #200 | |||
| LIDOCAINE|| 1% (20 mL) injection | 1.95 | Neomycin/PolymixinB/Hydrocortisone (5mL) Ear Drops I4 DROPS AD TID DAY7 #1 | |||
| LIDOCAINE|| 1% (20 mL) injection | 1.95 | Docusate (Ear Wax Removal) 0.5% (10mL) Ear Drops I8 DROPS AU HS DAY2 #1 | |||
| PARACETAMOL||10 mg/mL (100 mL) vial injection | 11.75051 | Ibuprofen 400mg Tablet T1 TAB PO BID DAY7 PRN PINF #15 | |||
| BALANCED SALT SOLUTION|| eye drops | 12.49882 | Paracetamol 500mg Tablet T2 TAB PO Q6H DAY5 #24 | |||
| BUDESONIDE|| 0.5 mg/mL (2 mL) nebule (for inhalation) | 6.53829 | Ascorbic Acid (effervescent) 1000mg Tablet DD1 TAB-EFF PO D DAY14 #20 | |||
| DOCUSATE (EAR WAX REMOVAL)|| 0.5% ear drops | 2.51422 | Pseudoephedrine-Triprolidine 60mg-2.5mg Tab T1 TAB PO Q8H DAY4 MCD #12 | |||
| ERYTHROMYCIN|| 0.5% eye ointment | 7.53643 | Xylometazoline 0.1% (10mL) Nasal Spray SP1 SPRAY EN Q8H DAY3 #1 | |||
| FLUORESCEIN|| 2% eye drops (minims) | 3.74992 | Amoxicillin-Clavulanate 80mg-11.4mg/mL Susp 2.5 ML PO PC BID Q12H DAY 7 FRIDGE AND SHAKEWELL #70 | |||
| FUSIDIC ACID / BETAMETHASONE|| 20 mg / 1 mg (per gram) cream | 9.76567 | Paracetamol 24mg/mL Suspension 7 ML PO TID DAY 5 PRN PF #100 | |||
| FUSIDIC ACID|| 2% cream | 2.44014 | Cetirizine 1mg/mL Solution T2 SOL PO D DAY5 EVE #75 | |||
| VACCINE , HEPATITIS B VACCINE RECOMBINANT , (INACTIVATED) ( HEP B ADULT.) || 20 mcg (adult) injection ( single dose ) | 0 | Paracetamol 24mg/mL Suspension 6 ML PO TID DAY 5 PRN PF #100 | |||
| Cetirizine 1mg/mL Solution T2 SOL PO D DAY5 #75 | |||||
| Amoxicillin-Clavulanate 80mg-11.4mg/mL Susp 2.5 ML PO BID DAY 7 FRIDGE AND SHAKEWELL #70 | |||||
| Electrolyte for Rehydration (Neolyte) Sachet ORS2 140ML PO TID DAY3 #1260 | |||||
| Electrolyte for Rehydration (Neolyte) Sachet ORS2 140ML PO TID DAY3 #1260 |
<tbody>
</tbody>
| Item Description | Item Cost | DEPT_MISC_LINE | |||
| PARACETAMOL|| 250 mg supposito | 0.1795 | Paracetamol 500mg Tablet T2 TAB PO Q6H DAY5 #24 | |||
| EPINEPHRINE|| 1 mg/mL (1:1,000) 1 mL injection | 1.83866 | Paracetamol 500mg Tablet T2 TAB PO Q6H DAY5 #24 | |||
| INSULIN LISPRO|| 100 units/mL injection | 83.71223 | Alginic Acid Compound Suspension T10 SUSP PO Q6H DAY7 prn hburn #200 | |||
| LIDOCAINE|| 2% gel | 5.91142 | Alginic Acid Compound Suspension T10 SUSP PO Q6H DAY7 prn hburn #200 | |||
| METOCLOPRAMIDE|| 5 mg/mL (2 mL) injection | 0.97879 | Alginic Acid Compound Suspension 20 ml po qid prn hburn day 14 #1000 | |||
| OXYTOCIN|| 5 IU/mL (1 mL) injection | 1.7989 | Paracetamol 500mg Tablet T2 TAB PO Q6H DAY5 prn pain #24 | |||
| TEICOPLANIN|| 200 mg vial (injection) | 133.2249 | Ibuprofen 400mg Tablet T1 TAB PO TIDPC DAY5 prn pinf #15 | |||
| XYLOMETAZOLINE|| 0.1% nasal spray (adult) | 4.43829 | Amoxicillin-Clavulanate 875mg-125mg Tablet 1 po pc bid q12h day 7 #14 | |||
| XYLOMETAZOLINE|| 0.05% nasal drops (pediatric) | 3.68699 | Paracetamol 500mg Tablet T2 TAB PO Q6H DAY5 #24 | |||
| KETOROLAC|| 30 mg/mL (1 mL) injection | 4.80156 | Desloratidine 5mg Tablet T1 TAB PO D DAY10 eve #10 | |||
| HETASTARCH|| 6% solution (500 mL) for infusion | 28.63 | Paracetamol 500mg Tablet T2 TAB PO Q6H DAY5 #24 | |||
| PARACETAMOL|| 500 mg tablet | 0.02887 | Ascorbic Acid (effervescent) 1000mg Tablet DD1 TAB-EFF PO D DAY14 #20 | |||
| BUPIVACAINE|| 0.5% "HEAVY" preservative-free (spinal) injection | 13.22805 | Atorvastatin 10mg Tablet T1 TAB PO HS DAY90 #90 | |||
| DICLOFENAC SODIUM|| 100 mg suppository | 1.39871 | Ergocalciferol 50,000 units Capsule T2 CAP PO Q7D DAY90 #12 | |||
| DICLOFENAC SODIUM|| 100 mg suppository | 1.39871 | Calcium Carbonate-Vitamin D 600mg-200units Tablet T1 TAB PO BID DAY90 #180 | |||
| ERGOMETRINE MALEATE SYNTHETIC OXYTOCIN|| 0.5 mg + 5I.U/mL (1 mL) injection | 14.95007 | Vildagliptin-Metformin 50mg-1000mg Tablet T1 TAB PO BID DAY90 #180 | |||
| LIDOCAINE|| 2% (50 mL) injection | 4.04046 | Pseudoephedrine-Triprolidine 60mg-2.5mg Tab T1 TAB PO Q8H DAY3 MCD #10 | |||
| LIDOCAINE|| 10% endotracheal spray | 25.37894 | Paracetamol 500mg Tablet T2 TAB PO Q6H DAY3 PRN PF #24 | |||
| OXYTOCIN|| 5 IU/mL (1 mL) injection | 1.7989 | Xylometazoline 0.1% (10mL) Nasal Spray SP1 SPRAY EN Q8H DAY3 NASCONG #1 | |||
| OXYTOCIN|| 5 IU/mL (1 mL) injection | 1.7989 | Paracetamol 500mg Tablet T2 TAB PO Q6H DAY5 #0 | |||
| PHYTOMENADIONE|| 10 mg/mL (0.2 mL) injection | 2.7549 | Paracetamol 500mg Tablet T2 TAB PO Q6H DAY5 #0 | |||
| ROCURONIUM|| 10 mg/mL (5mL) injection | 19.87276 | Paracetamol 24mg/mL Suspension 8 ML PO Q4H PRN PF #200 | |||
| LIDOCAINE|| 1% (20 mL) injection | 1.95 | Neomycin/PolymixinB/Hydrocortisone (5mL) Ear Drops I4 DROPS AD TID DAY7 #1 | |||
| LIDOCAINE|| 1% (20 mL) injection | 1.95 | Docusate (Ear Wax Removal) 0.5% (10mL) Ear Drops I8 DROPS AU HS DAY2 #1 | |||
| PARACETAMOL||10 mg/mL (100 mL) vial injection | 11.75051 | Ibuprofen 400mg Tablet T1 TAB PO BID DAY7 PRN PINF #15 | |||
| BALANCED SALT SOLUTION|| eye drops | 12.49882 | Paracetamol 500mg Tablet T2 TAB PO Q6H DAY5 #24 | |||
| BUDESONIDE|| 0.5 mg/mL (2 mL) nebule (for inhalation) | 6.53829 | Ascorbic Acid (effervescent) 1000mg Tablet DD1 TAB-EFF PO D DAY14 #20 | |||
| DOCUSATE (EAR WAX REMOVAL)|| 0.5% ear drops | 2.51422 | Pseudoephedrine-Triprolidine 60mg-2.5mg Tab T1 TAB PO Q8H DAY4 MCD #12 | |||
| ERYTHROMYCIN|| 0.5% eye ointment | 7.53643 | Xylometazoline 0.1% (10mL) Nasal Spray SP1 SPRAY EN Q8H DAY3 #1 | |||
| FLUORESCEIN|| 2% eye drops (minims) | 3.74992 | Amoxicillin-Clavulanate 80mg-11.4mg/mL Susp 2.5 ML PO PC BID Q12H DAY 7 FRIDGE AND SHAKEWELL #70 | |||
| FUSIDIC ACID / BETAMETHASONE|| 20 mg / 1 mg (per gram) cream | 9.76567 | Paracetamol 24mg/mL Suspension 7 ML PO TID DAY 5 PRN PF #100 | |||
| FUSIDIC ACID|| 2% cream | 2.44014 | Cetirizine 1mg/mL Solution T2 SOL PO D DAY5 EVE #75 | |||
| VACCINE , HEPATITIS B VACCINE RECOMBINANT , (INACTIVATED) ( HEP B ADULT.) || 20 mcg (adult) injection ( single dose ) | Paracetamol 24mg/mL Suspension 6 ML PO TID DAY 5 PRN PF #100 | ||||
| Cetirizine 1mg/mL Solution T2 SOL PO D DAY5 #75 | |||||
| Amoxicillin-Clavulanate 80mg-11.4mg/mL Susp 2.5 ML PO BID DAY 7 FRIDGE AND SHAKEWELL #70 | |||||
| Electrolyte for Rehydration (Neolyte) Sachet ORS2 140ML PO TID DAY3 #1260 | |||||
| Electrolyte for Rehydration (Neolyte) Sachet ORS2 140ML PO TID DAY3 #1260 |
<tbody>
</tbody>
