Turning a cell into a column

[shelbybradley]

I am trying to convert the data sheet into a cleaner, simpler format. 

How do you do a "join multiple records" within the same sheet?. Currently, the data is organized horizontally with a replicating column format (A, B, C, A, B, C....). How can I combine all of these columns into the same column (A, B, C).

[atcodedog05]

Hi @shelbybradley 

Can you provide some sample input and expected output It will help us get a better understanding of the usecase.

We will be happy to help : )

[Luke_C]

Hi @shelbybradley 

Can you share some sample data? It sounds like you may want to transpose all of the data and crosstab back.

[NikyN]

Hello @shelbybradley,

as the column names are the same, they will be most likely renamed automatically (e.g. A, B, C, A1, B1, C1 etc.).

I agree with @Luke_C, that you are most likely looking for transpose all of the data and crosstab back. 

I am attaching dummy records. 

With Transpose Tool, you are able to have all column names/and values in rows. Here is very important to have Dynamic and Unknown Columns ticket to ensure all columns are passing thru the tool.

I recommend Cleansing Tool, to remove auto renames.

Crosstab Tool will give you desired format of the data, based on needs you can use Group By/and Method of Aggregating based on you needs.

Let me know if this solution was helpful!

Best luck!

Niky 

[shelbybradley]

Here is a larger explanation of the challenge I am facing with the data set....

Attached is the data set in its initial form. I would like to simplify and condense the data set into just a few columns with the most critical information (Sales Q, Sales per item, COGS, Inventory)

As you can see, Rows 1-3 contain the contextual information for the data columns. I need the information in the first three rows (specifically, City, Store Location, and Product) but I need them to be their own columns. But, I am having converting them into columns because they sit right on top of rest of the data.

Thus , I would like to have the final output have the columns:

- City

- Estimated ship date (Column C)

- Store Location

- Product

- Sales Q.

- $ per item

- COGS

- Inventory

Please let me know if you can help. 

Thanks!

[Luke_C]

Is this the same issue as the below? Or some different solution you need?

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Restructuring-Data-Set/td-p/903016

[shelbybradley]

This is the same, just worded more clearly.

[Luke_C]

To close this in case someone comes across in the future, here's an example of this: 

1. Isolate the city name, will be appended at the end.
2. Isolate the 3 header rows, transpose, and turn into columns with IDs based on order
3. Isolate the data, since columns repeat they get appended with an ID (COGS2,COGS3, etc)
4. We can transpose and isolate those numbers that were appended to join up with the header IDs we create
5. Join data together and format how you need.