Posts by xl4betrm

Sorry, this does not use Scripting Dictionary...so my text should read as this:
and I modified it to accomplish most of what I have indicated above. However, my modified VBA generates the results based on only one criteria (EID). I was not able to figure out how to code for the other two criteria (Code, Option).

I need to compare two data ranges by three criteria (EID, Code, Option) within one Excel sheet and then in the same sheet, I need to generate results, but with re-aligned data for each range based on the three criteria.

The spreadsheet contains 29 columns and the layout is as follows:

1. Each data range has fourteen headers (EID, Name, Code, Option, Number, PDate, Product, Cost, Cost2, Cost3, Type, Benefit, DDate, EDate).

2. There is data under columns “A” to “N”, column “O” has blanks, then more data under columns “P” to “AC.

I found some excellent VBA written by Jindon here:
https://www.ozgrid.com/forum/f…th-multiple-columns/page2

and I modified it to accomplish most of what I have indicated above. However, my modified VBA generates the results based on only one criteria (EID). I see Jindon used Scripting Dictionary, but I was not able to figure out how to code for the other two criteria (Code, Option).

I have attached a sample file with the modified VBA. It has four sheets:

A. Data – this is the source raw data
B. Results With 1 Criteria – this contains results after running the modified VBA
C. Desired Results With 3 Criteria – this how the re-aligned data needs to generate after running updated VBA
D. Merged Results With 3 Criteria – if possible, have desired results with 3 criteria merged into this sheet

If someone can help me on how to apply the three criteria it will be greatly appreciated!

Re: Using Array Copy and Insert Row Based On Cell Value

skywriter, I’m grateful for your code and it does what I need to do. But I came across a slight issue after testing it a bit more and I can’t seem to figure out how to fix it. Can you please have a look at it?

The issue seems to happen when the data source has two adjacent records where the plan name is “TRS Medical 2015” but for different EIDs. After the VBA runs, for the first EID record having plan name “TRS Medical 2015” it will generate three extra rows in addition to the copied row highlighted in orange. And it deletes the other EID record having plan name is “TRS Medical 2015” instead of creating its copy row highlighted in orange.

I have attached the tested Excel file with the two sheets as before. But in the sheet named Review Original Data I placed one row with plan name “TRS Medical 2015” right above another one having plan name as “TRS Medical 2015”. The Review sheet has the results showing described issue.

Really appreciate it!

Re: Using Array Copy and Insert Row Based On Cell Value

skywriter, your code is perfect for what I need. Thank you very much!

I have an Excel sheet (Review) which has individual rows of data by a unique "EID" number. I need to copy and insert row(s) if the cell value under plan column (D) is “TRS Medical 2015”. After the row(s) that meet criteria are copied/inserted into the Review sheet there are a few other data modifications, e.g. replace employer cost with "0" zero, change deduction code based on payer product and highlight the copied/inserted row(s).

The amount of data which needs to be evaluated may have 10K or more individual rows by unique EID number. I used an array to process the data because, as indicated in some forums, it’s more efficient and faster to use an array instead of looping through cells in the Excel sheet. I found VBA for array and modified it to do what is indicated above.

When the data is processed for a small number of records, the VBA does what it’s supposed to. However, when I tried processing 6,000 records, it runs a long time. I don’t know how long it actually runs with the 6000 records because I had to break its execution after 20 minutes.

So I’m hoping someone can look at my sample Excel file and its VBA to see if why it takes too long or if there is a better way to do this.

Attached is my sample Excel file. It has two sheets - one is named “Review Original Data” which is the data before executing the VBA. And the other one is “Review” which has results after executing the VBA.

Thank you!

Re: Generate Multiple Rows Of Data From One Row of Data That Spans Across Many Column

shknbk2, your replacement code is fantastic. It does exactly what is required. Thank you very much!

I have an Excel sheet (InData) which has individual rows of data by unique "ID NUMBER". Each ID Number may have multiple "deductions" and "benefits" contained in the one row but which span across many columns. I need to convert the single row of data into multiple rows by ID Number and write the results into a new sheet (OutData).

The Excel data source (InData) may have 10K or more individual rows by unique ID Number. And each ID Number has data going across columns, starting from column “D” and ending in column “BC”. The data has categories (column headings) for deductions or benefits as follows:

Starting in column “D” - Deduction Desc, Deduction Amount, Deduction Start Date, Deduction Stop Date. These four deduction headings are repeated nine times across the columns and end in column “AM”.
Starting in column “AN” - Benefit Desc, Benefit Amount, Benefit Start Date, Benefit Stop Date. These four benefit headings are repeated four times across the columns and end in column “BC”.

Attached is my sample Excel file which contains VBA I'm currently using but needs modification. It has sheets for “InData”, “OutData” (generated by current VBA and not the desired output) and “OutData Needs To Be Like This” (which is desired output).

Thank you!

Re: Compare More Than Two Worksheets and Reconcile Differences

Jindon, please disregard my previous message about the Reconciliation sheet.
Your VBA solution accomplishes what I need.

Many thanks!! …for your quick response and expert help.

Re: Compare More Than Two Worksheets and Reconcile Differences

Yes! Jindon you are amazing. It works perfectly to generate the “Differences” sheet. Would you be able to do similar for the “Reconciliation” sheet? So it will show data under each Company column and in column H to show their differences in Charge amount (if any).
Thank you!

I need to compare more than two worksheets and reconcile differences for Charge amounts.

Each sheet corresponds to a unique Company, e.g. Company1, Company2, Company3, etc.
The data columns are the same in all sheets - Name, Product, ID Number and Charge.

It needs to produce a “Reconciliation” sheet with the comparison results from each of the multiple sheets.
Also, it needs to create a “Differences” sheet with only those having Charge amount differences.

I found some excellent VBA code written by Jindon here: http://www.ozgrid.com/forum/showthread.php?t=174992 and have modified it in my attempt to produce the above.
I have attached my example which uses 4 Company worksheets. You will see it generates the Reconciliation and the Differences sheets, but results only show for Company1 and Company4. It does not list results of Company2 and Company3. Finally, there’s an issue with the results. “Allen GHI 111111” with a Charge amount of $250 is in the first three Company sheets, but not in Company4. However, in the results it shows $250 under Company4 when it should be blank.

If you can help me with this it will greatly be appreciated!

Compare More Than Two Worksheets and Reconcile Differences

I need to compare more than two worksheets and reconcile differences for Charge amounts.
Each sheet corresponds to a unique Company, e.g. Company1, Company2, Company3, etc.
The data columns are the same in all sheets - Name, Product, ID Number and Charge.

It needs to produce a “Reconciliation” sheet with the comparison results from each of the multiple sheets.
Also, it needs to create a “Differences” sheet with only those having Charge amount differences.

I found some excellent VBA code written by Jindon here: http://www.ozgrid.com/forum/showthread.php?t=174992 and have modified it in my attempt to produce the above. I have attached my example which uses 4 Company worksheets. You will see it generates the Reconciliation and the Differences sheets, but results only show for Company1 and Company4. It does not list results of Company2 and Company3. Finally, there’s an issue with the results. “Allen GHI 111111” with a Charge amount of $250 is in the first three Company sheets, but not in Company4. However, in the results it shows $250 under Company4 when it should be blank.
If you can help me with this it will greatly be appreciated!