Help with index/collect (or maybe something else?)

Murz

Hi,

I am struggling with index collect. I'm wondering if what I'm trying to do may not be possible with Smartsheet but I'm not giving up until I ask you wonderful people!

I have a large Smartsheet with many columns and I'm maxed out at 381 columns, my license is only to 400 columns so while I could probably figure this out with numerous helper columns, I'm somewhat limited right now with that option.

I have groups of data that I need to be placed in specific cells so I can generate a document with the information in the right place. It is for transactions and the transactions are allocated to an "Object Code" and then to an "ORG Code" which are tied to a Facility. Facilities have 1, 2 or 3 ORG Codes assigned to them, and if the Facilities have a split ORG code, it will break the transaction amount into a percentage. For example, if Facility 1 is broken into 2 ORG codes, ORG Code 1 will be 25% and ORG Code 2 will be 75%. In addition, these ORG code amounts need to be broken down into Object codes. Whew.

So one step I have (there are many but I think what I find here will help with all the steps) is to take the object codes that are entered for the facilities and list them under the approriate ORG code.

I have the raw Object code data entered into a single column for each facility, and depending on the facility split, the object code is duplicated or triplicated into "split" cells. Named "Object Code 1a Split 2" or "Object Code 1a Split 3" for example.

Then I need th Object Codes to be collected into the correct Object Code field so I can place them in my generated document. There are also many variations of how the data can be entered. I have given three transaction examples in my data chart here:

	Object Code 1a	Object Code 1b	Object Code 1c	Object Code 1a Split 2	Object Code 1b Split 2	Object Code 1c Split 2	Object Code 1a Split 3	Object Code 1b Split 3	Object Code 1c Split 3	Object Code 2a	Object Code 2b	Object Code 2c	Object Code 2a Split 2	Object Code 2b Split 2	Object Code 2c Split 2	Object Code 2a Split 3	Object code 2b Split 3	Object Code 2c Split 3	Object Code 3a	Object Code 3b	Object Code 3c	Object Code 3a Split 2	Object Code 3b Split 2	Object Code 3c Split 2	Object Code 3a Split 3	Object Code 3b Split 3	Object Code 3c Split 3
Transaction 1	500523	500524	500525	500523	500524	500525	500523	500524	500525	500541			500541						500595	500601		500595	500601		500595	500601
Transaction 2	500555									500599	500517		500599	500517
Transaction 3	500114	500147		500114	500147					500778									500637	500755		500637	500755

If a facility is split, each "split" is a separate ORG code so in Transaction 1, Object Code 1a - Object Code 1c Split 3 are the same facilitlly but Object Code 1a - 1c is a different ORG code than Object Code 1a Split 2- 1c Split 2 and Object Code 1a Split 3 - 1c Split 3. I hope that makes sense.

But in my next step, I need to group all the Object Codes into consecutive Object Code cells based on the ORG code.

	ORG 1			ORG 2			ORG 3			ORG 4			ORG 5			ORG 6			ORG 7			ORG 8
	DOC: Object Code 1a	DOC: Object Code 1b	DOC: Object Code 1c	DOC: Object Code 2a	DOC: Object Code 2b	DOC: Object Code 2c	DOC: Object Code 3a	DOC: Object Code 3b	DOC: Object Code 3c	DOC: Object Code 4a	DOC: Object Code 4b	DOC: Object Code 4c	DOC: Object Code 5a	DOC: Object Code 5b	DOC: Object Code 5c	DOC: Object Code 6a	DOC: Object Code 6b	DOC: Object Code 6c	DOC: Object Code 7a	DOC: Object Code 7b	DOC: Object Code 7c	DOC: Object Code 8a	DOC: Object Code 8b	DOC: Object Code 8c
Transaction 1	500523	500524	500525	500523	500524	500525	500523	500524	500525	500541			500541			500595	500601		500595	500601		500595	500601
Transaction 2	500555			500599	500517		500599	500517
Transaction 3	500114	500147		500114	500147		500778			500637	500755		500637	500755

So ORG 1 has Object Code 1a - 1c and Transaction 1 has 500523, 500524, 500525 in those cells. ORG 2 and ORG 3 are the same because they are splits of the first facility. However, iti gets more complex with Transaction 2 where ORG 1 has 1 Object Code 500555 which goes into DOC: Object Code 1a but the next Object Code is 500599 (Object Code 2a) and that needs to go into DOC: Object Code 2a. And so on.

So my problem. I have all my cells ordered correctly so they would "collect" properly in the DOC: Object Code 1a, DOC: Object Code 2a etc but I'm having a lot of trouble getting them to populate correctly.

Sometimes if I use

=INDEX(COLLECT([Object Code 1a]@row:[Object Code 3c Split 3]@row, [Object Code 1a]@row:[Object Code 3c Split 3]@row, OR(@cell<>"",@cell="")),1) it will pull the values correctly and skip the blanks but then sometimes I need to do =index(collect([Object Code 1a]@[Object Code 3c Split 3]@row,[Object Code 1a]@row:[Object Code 3c Split 3]@row,@cell<>""),1) and it works. Or doesn't.

So I'm stumped. It skips blanks when it shoudn't sometimes but then it does. I'm wondering if I need to do something completely different to make this work, as this is the sequence for the rest of my Smartsheet - I have "items purchased" and "amount spent" sections that follow the same format. Thank you in advance!

I also uploaded my test data excel if that helps.

Data.xlsx

jmyzk_cloudsmart_jp

Hi @Murz

I’m trying to understand your data structure — thanks for the detailed explanation, it's a complex setup but really well thought out.

From what I can gather, each group of three columns ending in a, b, and c (like Object Code 1a, 1b, 1c) maps to a single ORG code. The pattern seems to work like this:

• Object Code 1a/1b/1c → ORG 1 (base set for the first facility)
• Object Code 1a/1b/1c Split 2 → ORG 2 (same facility, second ORG split)
• Object Code 1a/1b/1c Split 3 → ORG 3 (third ORG for the same facility)
• Object Code 2a/2b/2c → ORG 4 (next facility)
• Object Code 2a/2b/2c Split 2 → ORG 5
• Object Code 2a/2b/2c Split 3 → ORG 6
• And so on...

So, each "split" variation basically introduces a new ORG entry, even though it's part of the same facility group.
Is my understanding correct?

If this is the case, your second table in the Excel file does have some entries that match this rule — but not all of them.
Am I right about the structure, or do you have a different grouping logic in mind? Just want to make sure I’m interpreting it correctly before offering suggestions.