# Not sure what formula to use to sort

Occasional Visitor

# Not sure what formula to use to sort

I have spent a few hours trying to figure out what should be a simple task, and I was wondering if someone could assist me with which formula would be useful for what I am attempting to accomplish.

I have pulled a SQL database that unfortunately contains a column which includes three separate data points in the single column.

It looks something like this:

{"apples":4489,"oranges":1000,"bananas":0}

{"oranges":0,"bananas":0,"apples":1330}

{"bananas":2725,"oranges":101,"apples":100}

{"oranges":0,"apples":2386,"bananas":1578}

I have separated them into individual columns, but they're still not entirely easy to understand, I am looking to sort them by type into a specific column.

 Apples:4489 Oranges:1000 Bananas:0 Oranges:0 Bananas:0 Apples:1330 Bananas:2725 Oranges:101 Apples:100 Oranges:0 Apples:2386 Bananas:1578

How would I create a formula that sorts it into useable data such as this?

 Apples Bananas Oranges Apples:4489 Oranges:1000 Bananas:0 4489 0 1000 Oranges:0 Bananas:0 Apples:1330 1330 0 0 Bananas:2725 Oranges:101 Apples:100 100 2725 101 Oranges:0 Apples:2386 Bananas:1578 2386 1578 0
2 Replies

# Re: Not sure what formula to use to sort

Maybe with Power Query. I'd prefer a data layout like in the green table.

# Re: Not sure what formula to use to sort

If you must have a formula and have access to 365, this formula might work. It will re-shape the data vertically. It's best to go vertical.

``=LET(data,E1:G4,arr,SORT(TOCOL(data)),HSTACK(TEXTBEFORE(arr,":"),TEXTAFTER(arr,":")))``