Crosstab?

[G_SAND]

Hi All

I think I need to use a Crosstab for this but I can't seem to work it out

Current Layout

Material	Use
A	1
A	2
B	1
B	2
B	3

Required Layout

Material	Use	Use	Use
A	1	2
B	1	2	3

I'm sure it is easy. Any help would be great

Thanks

[NicoleJohnson]

See attached, I think this will get you what you are looking for (though slightly different format than how you've displayed above). I'm assuming that what you want is the different uses across the top, and than an indicator of whether or not that material is needed for that use case. So in my example results, it's showing a count of 1 for every use, rather than the 1 2 3 that you are showing (since 1 2 3 would be your column headers instead).

1. Summarize by Material & Use, then count Use

2. Crosstab with Group by Material, Use as column headers, and Count as values.

Let me know if this gets you what you need!

Cheers,

NJ

[G_SAND]

Hi Nicole

Thanks for the reply but in fact I do need the numbers stated in the table (1,2,3 etc) across from the material.

[NicoleJohnson]

No problem! The key with the Cross-Tab tool is that you have to have a different field for header & values... but nothing says they can't be the same values. So if you add a formula tool before a Cross-Tab, you should be able to get the layout you need. Note that you cannot have multiple columns with the same name, so in the solution attached I'm showing it as 1 2 3 instead of Use Use Use. But you could alter the formula to be something like "Use 1", "Use 2", "Use 3", etc.

Does that get you closer?

NJ

[G_SAND]

 Hi Nicole

We are getting closer but I may have been too simplistic in my example. Here's a better indication on the data:

Material	Use
A	0.1
A	0.6
B	1
B	0.8
B	1.2

Hopeful output:

Material	Use1	Use2	Use3
A	0.1	0.6
B	1	0.8	1.2

Following your last example, I suppose I need to put some kind of indicator in a new column so I can use the cross tab function:

Material	Use	Indicator
A	0.1	1
A	0.6	2
B	1	1
B	0.8	2
B	1.2	3

Would that work? And if so, how can I add that extra column? In the data set itself there are materials with up to 10 entries. 

Thanks

[NicoleJohnson · Accepted solution]

Yes, that's exactly how you could achieve that. Easiest is probably the Tile tool. You can mark your unique field as Material - this will give you a Tile_Num and Tile_SequenceNum field - the Tile_SequenceNum field is the one you'll want to use that shows you the identifier per Material. Use that field to create your "Use #" headers, followed by the Cross-Tab.

See attached! :)

NJ

[G_SAND]

Hi Nicole

We got there in the end!!

Thanks for your help.

[becki]

thank you for posting the solution!  i could use it on my own question!