Hi, I have a big dataset (A) of patients containing millions of rows and 50 columns. I need to do Multiple Hash lookups of the table using Macros. For example, I have patient info below by Patient Id. Pt_Id Loc HQ Inv Fill_Loc Fill_Qty 1058 STL ORD 500 MEM 100 1099 PIT MEM 50 STL 110 1058 MCI MEM 50 ORD 120 1058 ORD MEM 50 ORI 110 1059 CDL IAD 100 MCI 0 2051 HOW HIT 100 IAD 4 2045 IND IAD 5 MCI 5 2051 MRD IAD 1 IND 50 2051 ORL IAD 2 LTY 80 2051 STK IAD 5 ORF 40 1. I need to do Hash lookup on Pt_Id and Fill_Loc. For example, Pt_Id, Fill_Loc Pt_Id Fill_Loc Fill_Qty 1058 MEM 100 2. Using the result from 1, I need to do look-up on Pt_id and Fill_loc from 1 will become HQ..... Pt_Id HQ 1058 MEM 3. From 1, Patient No. 1058 had a Fill_Qty of 100. From 2, I need to lookup Pt_Id, HQ and get Inv. If Fill_Qty was 100 then I need to show Inv of 100. Pt_Id Loc Inv 1058 MCI 50 1058 ORD 50 4. The total Inv should not be more them Fill_Qty. If Inv. was more then Fill_Qty then I need to check each record and reduce the last record so that the total will be equal to Fill_Qty. Below is another example where Inv was more than Fill_Qty and I'll adjust the last inv. record to be equal to Fill_Qty. 1. Lookup Pt_Id Fill_Loc Fill_Qty 2051 IAD 4 2. Pt_Id HQ Inv 2051 IAD 1 2051 IAD 2 2051 IAD 5 3. Result - Total of Inv. = Fill_Qty Pt_Id HQ Inv 2051 IAD 1 2051 IAD 2 2051 IAD 1 The last row was adjusted from 5 to 1 so that the total of Inv matches Fill_Qty.
... View more