[William Lisowski]

I remain away from Stata, but not from Excel.

It appears that the problem you need to solve is to create the probabilities in an Excel table, since you want to export the Stata results back to Excel.

In the attached Excel workbook I take one of the variables in the CSV from post #15 and demonstrate Excel technique for accomplishing what I understand you to want. Perhaps this will point you to an Excel-based solution to your immediate needs.

[Ian Bell]

The reason I came back to Stata with the solution is the calcs in Excel covering all 160,000 rows and doing just one factor at a time caused Excel to crash. This happened several times and also had my CPU running at 100%. Despite my computer being of a high spec with an i9 16 core processor 64GB Ram and a 1TB SSD.
I have just tried using your two formulas which I combined making it one and calc all 77 columns down 20,000 rows and it again crashed when it had nearly completed and that took almost an hour. Time wise it would take a full day to do one sheet 160k rows by 77 cols and I have a total of 20 sheets which makes it an almost impossible task even with a super fast computer. By the way your formulas were slightly quicker. Stata would be a far better proposition I would have thought. I'd appreciate your further thoughts when time permits and you are back with your beloved Stata.

[William Lisowski]

In post #1 you said that the largest number of rows for any ID is 36.

The code I provided compares the Index in each row to the Index in all other rows in the table.

If your data is sorted so that all the observations with the same value of Index are adjacent, then you only need to compare the Index in each row to those from 35 rows above to 35 rows below. That will reduced the computation significantly. You will also need to add 35 blank rows above the table so that you don't have to modify the code in the first 35 rows of data.

So that means changing the first and third argument to sumif from absolute references of 160,000 rows to relative references.

The attached Excel workbook has those changes made.

[Ian Bell]

Can you please rename the worksheet and send again as every time I download it, it opens the earlier version.
Thanks

[William Lisowski]

As requested.

[Ian Bell]

Thank You William for all your help, I will work on it. Hopefully I can get stuck into those Stata training manuals and be easier to deal with when next I post.
Many Thanks
Ian