Turning a cell into a column

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

Workflow

Tips and Tricks

Common Use Cases

Transformation

Accepted answers

All comments

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

Turning a cell into a column.yxmd

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!

Example data set.PNG

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:

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

New Workflow14.yxmd

Fake data set.xlsx

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335