| 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:
- 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.
- 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.
