Can you dynamically pass in a record number to Select Records?

[jakeuk]

Items	Hawaii	Ohio
Green:
Apples	100	200
Red:
Apples	200	700

I've been thinking of different ways to tackle this, and I have something that works now... But I know that it could be better. I've been trying to gather something of this nature. 

What I Need:

State	Green Apples	Red Apples
Ohio	200	700
Hawaii	100	200

There is an inherent issue with the Transpose tool in that it does not allow one to have multiple columns named the same thing i.e. in the example this would be "Apples" rather it will make me use some sort of operator such as Average or Sum to combine the Apples when they are in fact different. The way in which I've gotten around this thus far is by using the Record ID tool. In the data set that I have now, the format stays consistent so I always know where let's say "Red" occurs. From there it's pretty easy to just use a Filter tool to select the records that are either above Red (or equal to and below Red) to add something so that I can differentiate between the two Apples. 

I've been worried though. Let's say that Green adds an item such as Marbles. The number that I've manually entered into the Filter tool will be messed up obviously. My current thought process now is:

1. Find where "Red" is using the Find Replace tool and append something such as the word "Here" to mark where the data starts to be intrinsically different.
2. Now, how do I mark everything below "Here" - is there a way to pass in dynamically, to let's say the Select Records tool, the Record ID for where "Here" occurs

Hopefully this illustration is somewhat clear. 

[Luke_C · Accepted solution]

Hi @jakeuk 

Would something like this work?

1. Multi-row formula to rename the items
2. Filter out empty rows
3. Transpose & Crosstab to desired presentation
4. Dynamic rename to fix column headers

[jakeuk]

This was close to what I needed, although it lead me exactly to what I needed. Thank you very much, I was unfamiliar with the Multi Row tool so it took me a second to figure out.

The only adjustment I made to your formula is as follows:

if contains([Row-1:Items], "Red") 
then [Items] + "Red"
else [Items]
endif

If we were to say, add "Bikes" to Items beneath "Red:", your code would spit out  

I also know exactly what I'm searching for (Red in this case) so that helps. The colon was a smart idea though 🙂