100,000 reference limit for big data sheet. How to save references?

Rytis Karalius

Hi all,

I have a big data sheet - insurance claims.

When the client fills out the insurance claim, it writes the insurance number in forms, which then is used to search for data to be pulled from 11 other sheets according to this number.

I have approximately 35~ columns that need to be filled.

What I did is index every column with IFERROR formula:

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX({1 - Sum}, MATCH([Certificate Reference]@row, {1 - Series + Number}, 0)), INDEX({2 - Sum}, MATCH([Certificate Reference]@row, {2 - Series + Number}, 0))), INDEX({3 - Sum}, MATCH([Certificate Reference]@row, {3 - Serija + Number}, 0))), INDEX({4 - Sum}, MATCH([Certificate Reference]@row, {4 - Serija + Number}, 0))), INDEX({5 - Sum}, MATCH([Certificate Reference]@row, {5 - Serija + Number}, 0))), INDEX({6 - Sum}, MATCH([Certificate Reference]@row, {6 - Serija + Number}, 0))), INDEX({7 - Sum}, MATCH([Certificate Reference]@row, {7 - Serija + Number}, 0))), INDEX({8 - Sum}, MATCH([Certificate Reference]@row, {8 - Serija + Number}, 0))), INDEX({9 - Sum}, MATCH([Certificate Reference]@row, {9 - Serija + Number}, 0))), INDEX({10 - Sum}, MATCH([Certificate Reference]@row, {10 - Serija + Number}, 0))), INDEX({11 - Sum}, MATCH([Certificate Reference]@row, {11 - Serija + Number}, 0)))

So this is 35 columns, with similar formulas, that pulls data from ~60,000 rows, and of course i am getting stuck with 100k references.

Is there a way to somehow save this project, do helper sheets in between for example, or somehow write a formula that pulls all corresponding cells according to first indexed value? Note - i can't just copy paste full row, because structure in final and indexed sheets is different.

Andrée Starå

Hi @Rytis Karalius

I hope you're well and safe!

Sometimes in my client solutions, when needed, I join values together in a so-called helper column in the Source sheet(s) and then break it up in the Destination sheet. The result is that the range decreases significantly.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Rytis Karalius

Hi, thank you for response.

If i understand your solution correctly, you are offering to add multiple cell values in the sheets indexed in combination like a "CSV" file with delimiters and then parsing them out in the final sheet?

That might work, however, this will take ages to do with 11 sheets (soon 13) and 35 columns combined from each sheet.

I am also interested in formula which parse the values, especially when there are text values inside that has a "company name" inside, which might range from 1 word to 5 and so on.

Wondering if we could come up with more simple solution.