Seeking alternative to my current numerous array formulas

[luckeyjune]

Hello,
I'm back again with another question. Does anyone have a clever way to replace my current method of obtaining horizontal lookup results? My dataset is extremely large, so having this many array formulas makes processing extremely time-consuming. What I have is a dataset with Account #, Completed Date, Reason Code. Any given account # might have numerous jobs, which I want returned as Job 1, Job 2, etc.
In helper columns to the right, I use array formulas to return horizontally the dates of the jobs each account # had, and then those Resolution Codes.
The array formula I'm using is {=IFERROR(INDEX($B:$B,SMALL(IF($A2=$A:$A,ROW($A:$A)),COLUMN(A1))),"")}
If there is some other way to accomplish this, I'd be happy to know!
See an example image of something similar to my dataset below.
Thanks so much to anyone who takes a look at this.

 

[jasonb75]

If you're trying to speed up slow calculation then try using a realistic number of rows within the array instead of entire columns.

If you only have 1000 rows of data then using the entire column as a range for the formula is the equivalent to doing something useful for 1 minute 22 seconds, then wasting the rest of a 24 hour period on a pointless exercise.

 

[offthelip]

I think you can do it with straight "if" statements: Your data appears to be sorted on Account number if this is the case then I think the solution is quite easy.
I created a "helper" column in column C (you will hasve to move this somewhere else)
In C2 I Have

=IF(A2=A1,C1+1,1)

in D2:

=IF($C2=1,$B2,D1)

in e2 :

=IF($C3=2,$B3,IF($C2=1,"",E1))

in f2:

=IF($C4=3,$B4,IF($C2=1,"",F1))

Note the pattern: then this can be copied for as many jobs as you need
This will take no time to calculate

 

[luckeyjune]

If you're trying to speed up slow calculation then try using a realistic number of rows within the array instead of entire columns.

If you only have 1000 rows of data then using the entire column as a range for the formula is the equivalent to doing something useful for 1 minute 22 seconds, then wasting the rest of a 24 hour period on a pointless exercise.

Sure, with 70,000 rows of data it doesn't make that much of a difference, and with running that many different arrays. I just used a simple array example for the small dataset I used to show an idea of what I'm doing presently. Thank though.

 

[jasonb75]

Sure, with 70,000 rows of data it doesn't make that much of a difference

You would be surprised, that's still ~93% of the processing effort being wasted, or 90 minutes work, 22 hours 30 minutes wasted.

 

[luckeyjune]

You would be surprised, that's still ~93% of the processing effort being wasted, or 90 minutes work, 22 hours 30 minutes wasted.

Oh I'm sure you're right! Arrays take a long time and yes referencing an entire column will slow it down considerably. Mainly I just wanted something simpler than array formulas.

 

[luckeyjune]

I think you can do it with straight "if" statements: Your data appears to be sorted on Account number if this is the case then I think the solution is quite easy.
I created a "helper" column in column C (you will hasve to move this somewhere else)
In C2 I Have

=IF(A2=A1,C1+1,1)

in D2:

=IF($C2=1,$B2,D1)

in e2 :

=IF($C3=2,$B3,IF($C2=1,"",E1))

in f2:

=IF($C4=3,$B4,IF($C2=1,"",F1))

Note the pattern: then this can be copied for as many jobs as you need
This will take no time to calculate

Well this is extremely simple, clever, and it works. Thank you so much. Clearly I was overthinking.

 

[offthelip]

Glad to be of help!!