Forum Discussion
Formula challenge: re-shape and cleanup
You're tasked with re-shaping and cleaning up the fictional Faber College alumni list to be used for a mail merge. Going from this: To this: To spice things up, the First and Last n...
Patrick2788 So I do not have Beta functions so here is an option without them. I don't know how well your solution performs on the full data set but mine crashes. As soon as you get into the couple thousands it start to take a lot of time and at some point before 10K it just crashes. I added a LAMBDA function (AlumniX) purely to easily select how many rows to test it on. It also tries adjusts that length to make it full records (as long as it isn't tripped up by one of those gap rows). Here is the formula:
=LET(alum,FILTER(AlumniX(200),INDEX(AlumniX(200),,1)<>""),
n,ROWS(alum),
list,MAKEARRAY(n,4,
LAMBDA(r,c,CHOOSE(c,
LET(name,INDEX(alum,MROUND(r+1,3)-2,MOD(r-1,3)+1),LEFT(name,SEARCH(" ",name))),
LET(name,INDEX(alum,MROUND(r+1,3)-2,MOD(r-1,3)+1),MID(name,SEARCH(" ",name),LEN(name))),
INDEX(alum,MROUND(r+1,3)-1,MOD(r-1,3)+1),
INDEX(alum,MROUND(r+1,3),MOD(r-1,3)+1)))),
FILTER(list, INDEX(list,,3)<>"Bellman"))
I did not have any trouble with your workbook. I'm running this on a new computer with 32 GB RAM, i7 processor. I will try it on my old computer.
- Did you try replacing AlumniX(200) with AlumniX(10000) or just Alumni? It works great at a couple hundred of lines but got slow at a couple thousand and I'm pretty sure it 'broke' around 10000 and forget about 100000+. That said, my computer has been slogging lately and is well over due to be replaced.
I tried it with 'Alumni' and Excel refused to calculate it - returned a 0. There was no memory or CPU spike, it appears to be a limitation of MAKEARRAY. I tried running it in Excel web app for grins, wouldn't calculate it.