Table with duplicate column names

[Ayaanarjun-5885]

Hi,

I have a table of data with products as columns and flavors as rows with certain values. and I need output without duplicate columns and summation of all the values, when I give input file my duplicate columns are named as Ice cream1, Ice cream2 so on.. whereas they are same columns. Si I need  consolidated values with out duplicate rows and columns.. 

Please find the below table

	Ice Cream	Jelly	Wafers	Ice Cream	Wafers	Ice Cream	Wafers	Ice Cream	Juices	Ice Cream	Juices
Raspberry	3	2	17	1	9	17	16	7	17	13	7
Mango	9	17	5	11	19	1	5	7	14	10	11
Double Vanilla	5	0	7	5	18	18	5	6	0	4	15
Mango	14	5	13	2	2	1	9	18	4	18	20
Raspberry	13	9	18	13	17	3	6	20	8	1	7
Strawberry	12	18	2	9	5	18	0	7	7	16	13
Raspberry	16	20	6	11	3	3	0	18	18	6	9
Vanilla	10	15	2	5	17	2	4	3	8	2	1
Raspberry	0	13	9	19	4	0	17	5	6	18	5
Raspberry	19	19	13	1	14	1	18	20	4	16	4
Mango	1	5	5	0	15	12	6	2	19	15	20
Vanilla	18	10	3	8	1	5	12	19	18	12	5
Mango	2	11	1	20	16	11	7	3	15	14	7
Double Vanilla	18	15	17	18	14	2	18	12	8	0	13
Raspberry	17	9	20	0	20	7	13	18	15	20	12
Choclate	17	19	13	18	17	19	13	13	0	12	20
Raspberry	6	5	16	6	14	2	5	9	4	15	8
Raspberry	5	2	16	20	10	4	4	14	16	13	5
Choclate	12	5	11	16	14	17	9	4	0	20	6
Vanilla	0	15	14	12	10	0	18	12	11	10	20
Mango	14	7	6	2	14	17	14	4	4	4	1
Vanilla	8	11	15	19	20	9	7	5	12	8	13
Choclate	20	13	7	4	8	8	1	6	4	15	14
Raspberry	2	7	15	4	0	10	19	19	11	13	10
Caramel	17	14	20	7	11	20	8	5	20	10	8
Strawberry	8	3	12	8	1	3	7	8	19	11	5
Caramel	9	10	18	19	15	10	3	6	16	5	9
Vanilla	4	8	2	6	2	2	2	5	8	6	8
Double Vanilla	10	8	11	20	10	13	14	15	11	17	18
Strawberry	17	17	7	4	16	20	0	10	6	1	9
Vanilla	16	18	1	18	19	20	0	15	20	20	20
Vanilla	13	7	17	6	20	16	19	7	17	5	2
Vanilla	14	19	4	3	7	9	13	4	12	19	6

Desired Output 

	Ice cream	jelly	wafers	juices
Raspberry	x	x	x	x
Mango	x	x	x	x
Double Vanilla	x	x	x	x
Strawberry	x	x	x	x
Caramel	x	x	x	x
Choclate	x	x	x	x

[IraWatt · Accepted solution]

Hey @Ayaanarjun-5885,

Here is one approach:

Transpose the columns for each category into one rename them the same then crosstab it back summing the same named columns together.

This solution uses the transpose and cross tab tools which can be hard to understand to new users. I would recommend checking out the community Getting-Started-Learning-Path for some context on how they work: https://community.alteryx.com/t5/Learning-Paths/Getting-Started-Learning-Path/ta-p/475117#done

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[Felipe_Ribeir0 · Accepted solution]

Hi @Ayaanarjun-5885 

One way of doing this: